Updating Access with UPDATE Statement with VB.NET 2K3

[MagicCity33]

Hello all,
I'm trying to update data in an Access database but according to VB.NET I have a syntax error in my UPDATE SQL Statement. I have looked over this code over and over and again but I just don't see any problems with what I have. I can update one field at a time but I would like to do is update an entire row based on the data entered into the textboxes on the form. Has anyone run into this problem before? What can I do to resolve this problem.

Here is my code:

Public AppPath = System.Windows.Forms.Application.StartupPath
Public Active_DB As String = "C:\Test\Agenda00.mdb"
Public ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Active_db & " ;User Id=admin;Password=;"
Public DBconn As OleDbConnection = New OleDbConnection(ConnStr)
Public DS As DataSet
Public objfrmMain As frmMain

DS = New DataSet ' This isn't used until I get ready to look back into the database to make sure my ' changes where made.

Try

Dim UpdateCMD As OleDbCommand = New OleDbCommand

UpdateCMD.CommandText = "UPDATE Agenda SET " _
& "AGDate = #" & objfrmMain.txtgb2Date.Text& "#, " _
& "Topic = '" & objfrmMain.txtgb2Topic.Text & "', " _
& "Time = #" & objfrmMain.txtgb2Time.Text & "#, " _
& "Additional = '" & objfrmMain.txtgb2Comment.Text & "'" _
& " WHERE ID = #" & objfrmMain.txtgb2old.Text & "#"

UpdateCMD.Connection = DBconn
UpdateCMD.ExecuteNonQuery()
DBconn.Close()

MsgBox("Update Completed!")

Catch ex As Exception
MessageBox.Show(ex.Message)
Exit Function
End Try

Thanks

 

[kulrom]

why those #'s ?/
also you suppose to change the statement to somthing like:

UpdateCMD.CommandText = "UPDATE Agenda SET " & _
"AGDate = '" & objfrmMain.txtgb2Date.Text& "', " & _
"Topic = '" & objfrmMain.txtgb2Topic.Text & "', " & _
"Time = '" & objfrmMain.txtgb2Time.Text & "', " & _
"Additional = '" & objfrmMain.txtgb2Comment.Text & "' " & _
"WHERE ID = '" & objfrmMain.txtgb2old.Text & "'"

HTH
Regards

 

[DavidT_macktool]

Man, We all see this quite often on this forum. Each time we direct you towards Parameterized Update and insert statements - they are easier to code and update than those confusing concatinated statements.

Update Command Parameters.

 

[MagicCity33]

Thank you both for your responses,

kulrom I tried setting my SQL statement up the way that you suggested and received the error "Syntax error in UPDATE Statement". I've tried several different ways of formatting the statement but always receive this error.

I even got a IS consultant to look at my code and he found nothing wrong either so this issue definitely intrigues me.

This is my first time trying to make changes to an Access DB through ADO.NET so please bear with me.

I've read and downloaded a few examples on a couple of websites and on a couple of the examples it stated that if you have numbers or numeric dates being entered into a Access database that you had to enclose them with the # sign.

kulrom thank you for your patience and assistance it is so hard for beginners to find quality help ( or programmers willing to help) these days so if you know of any sites or books I should pick up on concantenating statements I would really appreciate.

DavidT_macktool, forgive me for not knowing anything about parameterized statements. This is a first for me, so I knew nothing about parameterized update & insert statements. I found a few examples on the net and sat down and tried to write out a simple statement.

I wish to know how to do both concantenating statements and parameterized now that I know there are many alternatives. So if you wouldn't mind enlightening me or pointing me to a few sites illustrating this method then I would appreciate it.

Thanks

MagicCity33

"I can do all things in God who strengthens me!"

 

[DavidT_macktool]

No problem, and don't apoligize for not knowing something - that is what this forum is for...
As for tutorials and web sites, I'll direct you to jmcilhinney's signature. He has loaded it up with many great locations for information for 'newbees'.
http://www.vbdotnetforums.com/member.php?find=lastposter&t=8268

I have also directed people to utilize the dataform wizard to create insert, update, and delete statements until thier familiarity with them increases. The wizard creates nice structured parameterized statements - you can cut, copy and paste the code into your active form/project.

Sorry If it sounded like I was comming down on you - that was not my intention.