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
13
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
