Writing to database error!

[brandonyoung]

If anyone can point out my error it would be much appreciated.

This is the error message: Syntax error in UPDATE statement.

Here's my code:

Sub addSpot(ByRef row As Integer, ByRef col As Integer, ByRef value As String)


        Dim con As New OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String


        dbProvider = ("PROVIDER=Microsoft.Jet.OLEDB.4.0;")
        dbSource = ("Data Source = C:/Day Nursery.mdb")


        con.ConnectionString = dbProvider & dbSource


        con.Open()


        sql = "SELECT * FROM Design"
        da = New OleDb.OleDbDataAdapter(sql, con)


        da.Fill(ds, "AddressBook")


        Dim cb As New OleDb.OleDbCommandBuilder(da)


        ds.Tables("AddressBook").Rows(row).Item(col) = value
        da.Update(ds, "AddressBook")


        MsgBox("Data updated")


        con.Close()


    End Sub

Thanks in Advance.....

 

[jmcilhinney]

I have added formatting tags to your code snippet to make it comfortable to read. Please do so yourself in future. I have also moved this thread to a more appropriate forum. Please post in the most appropriate forum for the topic, not just the first one.

First, if you want to build a connection string in pieces then you should use, in your case, an OleDbConnectionStringBuilder. It is far less dodgy than the way you're doing it.

As for the question, in situations like this, the culprit is almost always your column names. Most likely, one or more of your column names contains special characters, e.g. spaces, or else is a reserved word, e.g. Password for Access. You have three options:

1. Change your column names. This should be your first option. You should always avoid special characters and reserved words in identifiers if at all possible.

2. Write out the full column list in the query instead of using a wildcard. You will be forced to escape the offending column names and then the command builder will follow your lead.

3. Set the QuotePrefix and QuoteSuffix properties of the command builder so it will escape column names in the non-query commands.

 

[brandonyoung]

Thanks for helping me out with those tags and moving the post.

I have been banging my head against a wall trying to come up with syntax to produce my desired outcome. It seems pretty simple but when eve I find a good enough example online it doesn't quite come out like I hope. Could you please link me to a decent example or show me some syntax which I could use.

I'm hoping to add a new column with new information and edit already existing data.

Thanks so much for your time once again,
Brandon

 

[jmcilhinney]

As I have already indicated, there's nothing really wrong with your code. It's your column names that are the issue. Change them and the issue will go away. If you don't want to change them for whatever reason then I've given you an easy solution, i.e. option 3.

 

[cjard]

List the names of all your columns in the Design table and we'll tell you which ones are likely a problem