Insert record between two DateTimePickers using Do While in VB.Net

elvisidrizi1
 
on Jul 12, 2020 11:49 PM
1020 Views

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()

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 13, 2020 07:20 AM
on Jul 13, 2020 07:27 AM
elvisidrizi1 says:
 Do While (startdate <= enddate)

You need to loop and set the adoRS.EOF inside it.

Dim startdate As DateTime = DateTimePickerFixCosts1.Value
Dim enddate As DateTime = startdate.AddMonths(txtNumberofMonths.Text)
Do While (startdate <= enddate)
    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)
    If (adoRS.EOF = True) Then
        ' insert
        adoRS.AddNew()
        adoRS.Fields("Date").Value = startdat 
        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 = startdat
            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()
        End If
    End If
    startdate = startdate.AddMonths(1)
Loop
adoRS.Close()
db.Close()