469,647 Members | 1,728 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

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[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
2 2482
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
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.

Similar topics

6 posts views Thread by Gérard Leclercq | last post: by
2 posts views Thread by eric dugal | last post: by
3 posts views Thread by Neil Zanella | last post: by
3 posts views Thread by Nathan Sokalski | last post: by
5 posts views Thread by amitbadgi | last post: by
6 posts views Thread by ewpatton | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.