Writing changes from datagrid to table - HOW???

[copyco]

I've seen example after example on this and I'm just not getting it. I
just can't seem to get changes from my datagrid to write back to the
table that it's from. I'm trying to use a commandbuilder object to
write the changes in the datagrid back to the table. I'm getting an
error on the "dataAdapter.Update" method. The error states that there's
a syntax error in the "INSERT INTO statement." When I view the debug
output of the commandText, it has question marks in the SQL statement
where the parameters go. Should those be automatically replaced, or are
those what is causing the syntax error? I'm really lost and need help.
There is something I'm missing. Any help is appreciated. My code
is below.

'add new Number to the Datagrid...
dvNumbers.AllowNew = True
Dim newRow As DataRowView = dvNumbers.AddNew
newRow("number") = comNum
newRow("comName") = comName
newRow("callCount") = 1
newRow("lastCall") = vNow
newRow("blocked") = vBlocked
newRow.EndEdit()
dvNumbers.AllowNew = False

'add new Number to the Table...
Dim cbNumbers As New OleDbCommandBuilder(daNumbers)
daNumbers.InsertCommand = cbNumbers.GetInsertCommand
Debug.WriteLine(daNumbers.InsertCommand.CommandText)
daNumbers.Update(dsNumbers, "Numbers") '** ERROR line

 

[copyco]

Ok, I'm talking to myself here. I just wanted to reply as to give the
reason for the problem so it would help someone else. I found that the
problem is that the commandBuilder automatically generates the SQL
statements, but if any column names are reserved words, it will not
handle these correctly. Usually, when you construct an SQL statement,
you'd want to enclose reserved words in brackets. But the
commandBuilder isn't smart enough to know which column names are also
reserved words. In my table, I was using a column called "number."
Apparently that's a reserved word. I tried programmatically modifying
the commandText so it would add the brackets on the field, but that
didn't work. The only solution was to modify the table itself and
rename the columns so that I have no reserved words used for the column
names (aka. field names).

 

[copyco]

Ah! I knew there had to be another solution. I think I saw an example
of this somewhere, but didn't think it applied to my situation. Thanks!