Question Problems writing to database

[brandonyoung]

Hi if anyone could offer any advice I would be very grateful.....

I'm trying to write to a spot in a database and am getting this error message:

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

Here is 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


Dim databaseName As String = "C:/The Ascot Day Nursery.mdb"


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


con.ConnectionString = dbProvider & dbSource


con.Open()


sql = "SELECT * FROM brandontable"


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")


con.Close()


End Sub

 

[jmcilhinney]

Your database table doesn't have a primary key, so the adapter is unable to uniquely identify records in order to update or delete them. There's rarely a good reason for a table not having a primary key.

 

[brandonyoung]

OK so at what point would I declare a primary key? Is it a command every time I create a new record or do I just set it up when I set up the table?

Thanks for your patience

 

[jmcilhinney]

It's part of configuring the table in the database. If there is no suitable column already in the table (things like names are generally not suitable) then you should add a new column and set its data type to AutoNumber and then make it the primary key. The system will then automatically generate sequential numbers to store in that column and use them, both at in the database and the application, to uniquely identify each record.

 

[brandonyoung]

So I changed the table configuration so there is a primary key that automatically increases although the above code still gives me an error. Any thoughts as to why?

Error I get is "Syntax error in UPDATE statement"

Thanks again

 

[jmcilhinney]

Most likely, one or more of your column names contains special characters, e.g. spaces, or is a reserved word. Your first option should be to change your column names to remove all special characters and reserved words. If you can't do that for some reason then you should set the QuotePrefix and QuoteSuffix properties of your command builder to escape the column names.