AddNew Row

[Peter]

I am moving data from one Access table to another.

the following code works for most of the tables except one:
I get "System.Data.OleDb.OleDbException: Syntax error in INSERT INTO
statement"

How can I get the entire SQL insert string to find out what is causing the
Syntax error?

//--------------------------------------------------------------------------
--------------------
dataRow = dataTable.NewRow();
foreach(DataColumn cl in dsNew.Tables[0].Columns)
{
dataRow[cl.ColumnName] = dr[cl.ColumnName];
}
dataTable.Rows.Add(dataRow);

this._dt.UpdateImportData(adapter, dsCurrent, importInfo.TableName);
//
//
//
public void UpdateImportData(OleDbDataAdapter adapter, DataSet ds, string
tableName)
{
try
{
adapter.Update(ds, tableName); // this is where the error
occurs
adapter.Dispose();
}
catch(Exception e)
{
throw new Exception(e.ToString());
}
}


Peter

 

[Cor]

Hi Peter,

Mostly this kind of errors have to do with the primary key which is not
supplied before the add to the datatable.

I hope this helps?

Cor

 

[Kevin Yu [MSFT]]

Thanks for Cor's quick response.

Hi Peter,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you got a syntax error in INSERT INTO
statement when trying to update records to an Access table. And you need to
find a way to see what SQL statement is actually executed that causes the
problem. If there is any misunderstanding, please feel free to let me know.

Based on my experience, we can get the executed command statements by
handling the OleDbDataAdapter.RowUpdating event. The RowUpdating event
fires before a row is going to be updated. In OleDbRowUpdatingEventArgs,
the Command member is the OleDbCommand object actually executed on
database. We can use e.Command.CommandText and e.Command.Parameters to get
these values.

As Peter mentioned, this might have something to do with the primary key.
If you are having problem debugging this issue, please feel free to let me
know. Please also paste the Command text and parameter's type, size and
value here.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Peter]

Thanks everyone for your help, one of the field's name was column once I
renamed it to column_name everything worked.

Is there a way to use column as the column name?

 

[Kevin Yu [MSFT]]

Hi Peter,

As "column" is a reserved word in T-SQL, it's not recommended to use it as
a field name in table. If you have to use it, you can add brackets like
[column] when using in SQL statements. Here is an example:

INSERT INTO Table1([column]) values("hello")

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi Peter,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Cor]

Hi Peter,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu

Hi Kevin,

The email adres from Peter is as far as I have seen
(e-mail address removed).

I could not resist

Sorry



Cor

 

[Kevin Yu [MSFT]]

Hi Cor,

They are different Peters.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Peter]

Yes, thank you!