473,322 Members | 1,398 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 2788
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Gérard Leclercq | last post by:
ACCESS First fields are TEXT, last 2 are Numbers The name of the fields are correct. Dim MyConn Set MyConn=Server.CreateObject("ADODB.Connection") MyConn.Open...
4
by: dcarson | last post by:
I've read about this error in several other discussions, but still can't seem to pinpoint the problem with my code. Everything seemed to be working fine for some time, but it now tends to bomb out...
3
by: William | last post by:
i am using a dataset and a DataAdapter to update a table with the following schema: ResourceID ProjectID LastName FirstName Year Nov Dec Jan
7
by: kosta | last post by:
hello! one of my forms communicates with a database, and is supposed to add a row to a table using an Insert statement... however, I get a 'oledb - syntax error' exception... I have double...
2
by: eric dugal | last post by:
Hi all!! I need your help.... i'm working since 2 hours on a simple insert statement, but couldn't handle it. Here is my code : public int ExecQuery(string SqlString) {
3
by: Neil Zanella | last post by:
Hello, I am trying to execute ADO.NET INSERT statement where one of the fields is coming from a password HTML control. When I access the text with password.Value and print with Response.Write...
3
by: Nathan Sokalski | last post by:
When trying to submit data to an Access database using ASP.NET I recieve the following error: System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41...
5
by: amitbadgi | last post by:
Hi guys, I am getting the following error in teh insert statement , I am converting this asp application to asp.net, here is teh error, Exception Details:...
6
by: ewpatton | last post by:
Good day, I've been trying to work with SQL and an Access database in order to handle custom user profiles. I haven't had any trouble reading from my database, but inserting new entries into...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.