Insert Update record in database using ADODB Connection in VB.Net

elvisidrizi1
 
on Jul 15, 2020 11:54 PM
Sample_379410.zip
2747 Views

Hi there,

I have a code which I insert information inside of SQL Table, it used to work fine with the update and overwriting the information before I decided to input data based on the number of the months that I choose, Now the inserting works perfectly fine but when I want to update the same information that I inserted it doesn't update of it just one of it, the one that I choose.

Here is the code :

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=#####\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)

adoRS.Close()
db.Close()
Loop 

what is that I am doing wrong here, I just cant seem to figure it out.

Thankfully

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 18, 2020 05:25 AM
on Jul 20, 2020 02:03 AM

Refer the modified code. You need to pass the date to where condition date. If you want to check for particular Id then add ID condition to the select query. 

Dim startdate As DateTime = DateTimePickerFixCosts1.Value
Dim enddate As DateTime = startdate.AddMonths(Convert.ToInt32(txtNumberofMonths.Text))
Try
    Do While (startdate < enddate)
        Dim db As ADODB.Connection = New ADODB.Connection
        db.ConnectionTimeout = 10
        'Dim connstr As String = "Provider = SQLOLEDB;Data Source=192.168.0.10\SQL2014;Initial Catalog=Test;uid=sa;pwd=pass@123;"
        Dim connstr = "Provider = sqloledb;Data Source=#####\SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
        db.ConnectionString = connstr
        db.Open()
        Dim sqlstr As String = "Select * from Test_Table where Date = '" + startdate + "'"
        Dim adoRS As ADODB.Recordset = 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 = startdate
            adoRS.Fields("Department").Value = TxtFixCosts.Text
            adoRS.Fields("Description").Value = TxtItemDescFixCosts
            adoRS.Fields("Payment").Value = TxtPaymentFixCosts.Text
            adoRS.Fields("Price").Value = txtPriceFixCosts.Text
            adoRS.Fields("Status").Value = "Open"
            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("Status").Value = "Closed"
                MsgBox("Your data has been overwrited")
                adoRS.Update()
            End If
        End If

        adoRS.Close()
        db.Close()
        startdate = startdate.AddMonths(1)
    Loop
Catch ex As Exception
    MsgBox(ex.Message)
End Try