By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,743 Members | 1,058 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,743 IT Pros & Developers. It's quick & easy.

Syntax Error in INSERT INTO Statement

P: n/a
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[i].ColumnName.ToString() + ":" +
dr[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()].ToString() +
" ------ " + dsAccess.Tables["DT"].Columns[i].ColumnName.ToString());

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

{

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

}

}

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

if (drAccessAdd.HasErrors == false)

{
iTotalRecords++;

daAccess.Update(dsAccess, "DT");

}


Mar 6 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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 wrote:
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[i].ColumnName.ToString() + ":" +
dr[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()].ToString() +
" ------ " + dsAccess.Tables["DT"].Columns[i].ColumnName.ToString());

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

{

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

}

}

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

if (drAccessAdd.HasErrors == false)

{
iTotalRecords++;

daAccess.Update(dsAccess, "DT");

}

Mar 6 '06 #2

P: n/a
RSH
Brilliant!!

Talk about a needle in a haystack.

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

Ron
"John Murray" <jm*****@pluck.com> wrote in message
news:ul****************@TK2MSFTNGP11.phx.gbl...
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 wrote:
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[i].ColumnName.ToString() + ":" +
dr[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()].ToString() +
" ------ " + dsAccess.Tables["DT"].Columns[i].ColumnName.ToString());

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

{

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

}

}

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

if (drAccessAdd.HasErrors == false)

{
iTotalRecords++;

daAccess.Update(dsAccess, "DT");

}


Mar 6 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.