By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,982 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,982 IT Pros & Developers. It's quick & easy.

vb codes for sql update

P: 4
i'm not sure whether this shud b in vb or sql but i'm havin a problem with updating the data in access using the sql commands in vb. It seems that it will not update but no error messages were shown. So i'm simply lost in verifying the error. Here is a sample for my code:

strDate = Format(txtInvoiceDate.Text, "dd/mm/yyyy")

If saveType = 2 Then ' New Record

strSQL = "INSERT INTO tb_GTotalFL (InvoiceDate,AccNo,Discount,Tax) " _
& "VALUES ('" & txtInvoiceDate.Text & "'," _
& "'" & txtAccNo.Text & "'," _
& "" & txtDiscount.Text & "," _
& "'" & txtTax.Text & "')"
Else
'Edit Record
strSQL = "UPDATE tb_GTotalFL SET Discount=" & txtDiscount.Text & ", " _
& " Tax=" & txtTax.Text & "" _
& " WHERE AccNo='" & txtAccNo.Text & "' AND InvoiceDate= #" & strDate & "#"
End If
' Open a connection using OLEDB.
cnn.ConnectionString = VDataFile
cnn.Open
Set rs = cnn.Execute(strSQL)
'close connection
Set rs = nothing
Set cnn = nothing
cnn.close

...no errors..but wont update record edited..can any1 advice..? help is very much appreciated
Apr 22 '08 #1
Share this Question
Share on Google+
3 Replies


daniel aristidou
100+
P: 491
i'm not sure whether this shud b in vb or sql but i'm havin a problem with updating the data in access using the sql commands in vb. It seems that it will not update but no error messages were shown. So i'm simply lost in verifying the error. Here is a sample for my code:

strDate = Format(txtInvoiceDate.Text, "dd/mm/yyyy")

If saveType = 2 Then ' New Record

strSQL = "INSERT INTO tb_GTotalFL (InvoiceDate,AccNo,Discount,Tax) " _
& "VALUES ('" & txtInvoiceDate.Text & "'," _
& "'" & txtAccNo.Text & "'," _
& "" & txtDiscount.Text & "," _
& "'" & txtTax.Text & "')"
Else
'Edit Record
strSQL = "UPDATE tb_GTotalFL SET Discount=" & txtDiscount.Text & ", " _
& " Tax=" & txtTax.Text & "" _
& " WHERE AccNo='" & txtAccNo.Text & "' AND InvoiceDate= #" & strDate & "#"
End If
' Open a connection using OLEDB.
cnn.ConnectionString = VDataFile
cnn.Open
Set rs = cnn.Execute(strSQL)
'close connection
Set rs = nothing
Set cnn = nothing
cnn.close

...no errors..but wont update record edited..can any1 advice..? help is very much appreciated
are you using vb6??
Im not to familiar with vb6 if you are using a later version use snippets
Apr 22 '08 #2

debasisdas
Expert 5K+
P: 8,127
try like this
Expand|Select|Wrap|Line Numbers
  1. cnn.BeginTrans
  2. cnn.execute "Your insert or update SQL query here."
  3. cnn.CommitTrans
Apr 22 '08 #3

P: 4
thx 4 the feedback..finally solved the problem..it seems that my date format is whats causing the problem. So i changed the format to (mm/dd/yyyy) n am able to update the records..thx again!
Apr 22 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.