Syntax Error in INSERT INTO Statement

  • Thread starter Thread starter RSH
  • Start date Start date
R

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

}
 
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
 
Brilliant!!

Talk about a needle in a haystack.

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

Ron


John Murray said:
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");

}
Click to expand...
Click to expand...
 
Back
Top