Syntax Error in INSERT INTO Statement

[RSH]

I have a rather simple script that transfers data from a SQL server database
to an Access database. The procedure is intended to be dynamic so I am
basically adding a datarow and then passing the data with a simple loop. I
am using OleDbCommandbuilder to build the INSERT command but for some reason
when it gets to a particular table in the database it errors out giving me
"Syntax Error in INSERT INTO Statement". The cmmandbuilder built the
statement so can someone please tell me how to see what the INSERT INTO
statement looks like so I can figure out where the error is?

There are two snippets of code below, the top one is my Commandbuilder, and
the bottome one is the loop where the datarows are added and updated.

Thanks for any help!
Ron


String strAccessSQL = "SELECT * FROM " + strCurTable;

OleDbDataAdapter daAccess = new OleDbDataAdapter(strAccessSQL, cnAccess);

DataSet dsAccess = new DataSet();

daAccess.Fill(dsAccess, "DT");

OleDbCommandBuilder cbDevelopment = new OleDbCommandBuilder(daAccess);







DataRow drAccessAdd;

drAccessAdd = dsAccess.Tables["DT"].NewRow();

for (Int16 i = 0; i < dr.ItemArray.Length; i++)

{

Console.WriteLine(strCurTable + " " +
dsSQLServer.Tables[0].Columns.ColumnName.ToString() + ":" +
dr[dsSQLServer.Tables[0].Columns.ColumnName.ToString()].ToString() +
" ------ " + dsAccess.Tables["DT"].Columns.ColumnName.ToString());

if (dsSQLServer.Tables[0].Columns.ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns.ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns.ColumnName.ToString()];

}

}

dsAccess.Tables["DT"].Rows.Add(drAccessAdd);

if (drAccessAdd.HasErrors == false)

{


iTotalRecords++;

daAccess.Update(dsAccess, "DT");

}

 

[John Murray]

Most likely one of the column names you have is an access reserved
keyword. Try adding cbDevelopment.QuotePrefix = "[" and
cbDevelopment.QuoteSuffix = "]" right after you construct the builder.

John

 

[RSH]

Brilliant!!

Talk about a needle in a haystack.

Thanks a ton!!!!!!!!!!!!

Ron

Most likely one of the column names you have is an access reserved
keyword. Try adding cbDevelopment.QuotePrefix = "[" and
cbDevelopment.QuoteSuffix = "]" right after you construct the builder.

John

I have a rather simple script that transfers data from a SQL server
database to an Access database. The procedure is intended to be dynamic
so I am basically adding a datarow and then passing the data with a
simple loop. I am using OleDbCommandbuilder to build the INSERT command
but for some reason when it gets to a particular table in the database it
errors out giving me "Syntax Error in INSERT INTO Statement". The
cmmandbuilder built the statement so can someone please tell me how to
see what the INSERT INTO statement looks like so I can figure out where
the error is?

There are two snippets of code below, the top one is my Commandbuilder,
and the bottome one is the loop where the datarows are added and updated.

Thanks for any help!
Ron


String strAccessSQL = "SELECT * FROM " + strCurTable;

OleDbDataAdapter daAccess = new OleDbDataAdapter(strAccessSQL, cnAccess);

DataSet dsAccess = new DataSet();

daAccess.Fill(dsAccess, "DT");

OleDbCommandBuilder cbDevelopment = new OleDbCommandBuilder(daAccess);







DataRow drAccessAdd;

drAccessAdd = dsAccess.Tables["DT"].NewRow();

for (Int16 i = 0; i < dr.ItemArray.Length; i++)

{

Console.WriteLine(strCurTable + " " +
dsSQLServer.Tables[0].Columns.ColumnName.ToString() + ":" +
dr[dsSQLServer.Tables[0].Columns.ColumnName.ToString()].ToString() +
" ------ " + dsAccess.Tables["DT"].Columns.ColumnName.ToString());

if (dsSQLServer.Tables[0].Columns.ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns.ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns.ColumnName.ToString()];

}

}

dsAccess.Tables["DT"].Rows.Add(drAccessAdd);

if (drAccessAdd.HasErrors == false)

{


iTotalRecords++;

daAccess.Update(dsAccess, "DT");

}