Hi Everyone,
I hope everyone has a good day. I have a code which I add and update values in sql with vb.net,
What I want to do is set values each month between two datetimepickers.
For example to insert the a mortgage of the house for 5 years, from datetimepicker1 from 01.01.2015 to datetimepicker2 01.01.2021.
I googled a lot but there is no examples like this.
Your Help is much appreciated.
Here is the code:
Dim startdate As DateTime = DateTimePickerFixCosts1.Value
Dim enddate As DateTime = startdate.AddMonths(txtNumberofMonths.Text)
db = New ADODB.Connection
db.ConnectionTimeout = 10
connstr = "Provider = sqloledb;Data Source=ELVIS\SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
db.ConnectionString = connstr
db.Open()
sqlstr = "Select * from Test_Table where ID = '" & Val(TxtIDFixCosts.Text) & "'"
adoRS = New ADODB.Recordset
adoRS.Open(sqlstr, db, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
Do While (startdate <= enddate)
If (adoRS.EOF = True) Then
' insert
adoRS.AddNew()
adoRS.Fields("Date").Value = DateTimePickerFixCosts1.Value
adoRS.Fields("Department").Value = TxtFixCosts.Text
adoRS.Fields("Description").Value = TxtItemDescFixCosts
adoRS.Fields("Payment").Value = TxtPaymentFixCosts.Text
adoRS.Fields("Price").Value = txtPriceFixCosts.Text
MsgBox("Your Record has been inserted")
adoRS.Update()
Else
If (MsgBox("This item in this date is already inserted " & vbCrLf & "Would you like to overwrite it?", MsgBoxStyle.YesNo, "This Item exist") = vbYes) Then
' overwrite
adoRS.Fields("Date").Value = DateTimePickerFixCosts1.Value
adoRS.Fields("Department").Value = TxtFixCosts.Text
adoRS.Fields("Description").Value = TxtItemDescFixCosts.Text
adoRS.Fields("Payment").Value = TxtPaymentFixCosts.Text
adoRS.Fields("Price").Value = txtPriceFixCosts.Text
MsgBox("Your data has been overwrited")
adoRS.Update()
startdate = startdate.AddMonths(1)
End If
End If
Loop
adoRS.Close()
db.Close()
