Hello

I am getting a issue when inserting a record in the MS Access DB "Syntax error in INSERT INTO statement."

If i manually execute the insert query in the query editor of the access it works fine.

And i have one column whose type is Auto number, i am not using that column in the insert query (rest all the columns i explicitly specified in the insert statement.
).

so please can any one help me out so fix this issue.



Query is :

insert into referral (authid,action,memidtype,memid,dob,memberlname,memberfname,membermi,x_authcode,reftoprovidtype,refertoprovid,refertoprovidlname,refertoprovidfname,refertoprovidmi,referfromprovidtype,referfromprovid,admitprovidtype,admitphysprovid,effdate,termdate,emergency,transferinout,disdiagnosis,admitdate,dischargedate,deferreddliab,reinsurance,primarydiagnosis,authstatus,reason,attprovidtype,attprovid,self,outofarea,admittype,clinicalnotes,processid,formatid,ediclientid,edistatus,secondaryid,x_dispositionid) values ('65247','0',2,'10013544501','05/27/1959','HU','YOLA','','authcode',5,'15205101','All Florida Orthopedics','','',5,'10486801',5,'1234','09/11/2007','12/10/2007','N','N','disDia','01/01/1980','12/31/2078','N','N','1324','APPROVED','reason',5,'1234','N','N','Urgent','cliniacl notes','0','0','0','READY','','')


Thanks
Mohammed Irfan

Are you using the parameter objects?

String sql = @"INSERT INTO ReferralVALUES (@AuthId, @Action, @MemIdType, @memid, @dob, @memberlname, @memberfname, @membermi, @x_authcode, @reftoprovidtype, @refertoprovid, @refertoprovidlname, @refertoprovidfname, @refertoprovidmi, @referfromprovidtype, @referfromprovid, @admitprovidtype, @admitphysprovid, @effdate, @termdate, @emergency, @transferinout, @disdiagnosis, @admitdate, @dischargedate, @deferreddliab, @reinsurance, @primarydiagnosis, @authstatus, @reason, @attprovidtype, @attprovid, @self, @outofarea, @admittype, @clinicalnotes, @processid, @formatid, @ediclientid, @edistatus, @secondaryid, @x_dispositionid)";

using (OleDbCommand cmd = new OleDbCommand(sql, conn)
{
cmd.Parameters.AddWithValue("@AuthId", 65247);
cmd.Parameters.AddWithValue("@Action", 0);
etc...........................
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Hi,

Syntax error in INSERT INTO statement.

This error occurs there are reserved words in Insert statement. You can check the keyword from this page: http://sqlserver2000.databases.aspfaq.com/what-are-reserved-access-odbc-and-sql-server-keywords.html

This KB tells about this error:

You may receive a "Syntax error in INSERT INTO statement" error message when you use ADO.NET code to access Office Access 2003

http://support.microsoft.com/kb/892608/en-us

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

This error tells the statement is not right. The statement is complex. You’d better show them out to checkwhether the statement is in a right format.

The following thread discuss a similar issue: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/712575d5-d2c5-4702-a861-67094e373aa5

Hope this helps.

Best regards,

Ling Wang

---

Please remember to click “Mark as Answer�on the post that helps you, and to click “Unmark as Answer�if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Are you using the parameter objects?

String sql = @"INSERT INTO ReferralVALUES (@AuthId, @Action, @MemIdType, @memid, @dob, @memberlname, @memberfname, @membermi, @x_authcode, @reftoprovidtype, @refertoprovid, @refertoprovidlname, @refertoprovidfname, @refertoprovidmi, @referfromprovidtype, @referfromprovid, @admitprovidtype, @admitphysprovid, @effdate, @termdate, @emergency, @transferinout, @disdiagnosis, @admitdate, @dischargedate, @deferreddliab, @reinsurance, @primarydiagnosis, @authstatus, @reason, @attprovidtype, @attprovid, @self, @outofarea, @admittype, @clinicalnotes, @processid, @formatid, @ediclientid, @edistatus, @secondaryid, @x_dispositionid)";

using (OleDbCommand cmd = new OleDbCommand(sql, conn)
{
cmd.Parameters.AddWithValue("@AuthId", 65247);
cmd.Parameters.AddWithValue("@Action", 0);
etc...........................
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Hello

Thanks for your help.

I did the code changes and execute the code as memntied, now its giving the error saying that

Error ::: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

Please help me out to solve this issue.

string sql = @"INSERT INTO Referral VALUES (@AuthId, @Action, @MemIdType, @memid, @dob, @memberlname, @memberfname,
@membermi, @x_authcode, @reftoprovidtype, @refertoprovid, @refertoprovidlname, @refertoprovidfname, @refertoprovidmi,
@referfromprovidtype, @referfromprovid, @admitprovidtype, @admitphysprovid, @effdate, @termdate, @emergency,
@transferinout, @disdiagnosis, @admitdate, @dischargedate, @deferreddliab, @reinsurance, @primarydiagnosis, @authstatus,
@reason, @attprovidtype, @attprovid, @self, @outofarea, @admittype, @clinicalnotes, @processid, @formatid, @ediclientid,
@edistatus, @secondaryid, @x_dispositionid)";


EDIcmd.Parameters.AddWithValue("@AuthId", "'" + referralRow[0].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@Action", "'" + referralRow[1].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@MemIdType", referralRow[2].ToString().Trim());
EDIcmd.Parameters.AddWithValue("@memid", "'" + referralRow[3].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@dob", "'" + referralRow[4].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@memberlname", "'" + referralRow[5].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@memberfname", "'" + referralRow[6].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@membermi", "'" + referralRow[7].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@x_authcode", "'" + referralRow[8].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@reftoprovidtype", referralRow[9].ToString().Trim());
EDIcmd.Parameters.AddWithValue("@refertoprovid", "'" + referralRow[10].ToString().Trim() + "'");


EDIcmd.Parameters.AddWithValue("@refertoprovidlname", "'" + referralRow[11].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@refertoprovidfname", "'" + referralRow[12].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@refertoprovidmi", "'" + referralRow[13].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@referfromprovidtype", referralRow[14].ToString().Trim());
EDIcmd.Parameters.AddWithValue("@referfromprovid", "'" + referralRow[15].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@admitprovidtype", referralRow[16].ToString().Trim());
EDIcmd.Parameters.AddWithValue("@admitphysprovid", "'" + referralRow[17].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@effdate", "'" + referralRow[18].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@termdate", "'" + referralRow[19].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@emergency", "'" + referralRow[20].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@transferinout", "'" + referralRow[21].ToString().Trim() + "'");




EDIcmd.Parameters.AddWithValue("@disdiagnosis", "'" + referralRow[22].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@admitdate", "'" + referralRow[23].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@dischargedate", "'" + referralRow[24].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@deferreddliab", "'" + referralRow[25].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@reinsurance", "'" + referralRow[26].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@primarydiagnosis", "'" + referralRow[27].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@authstatus", "'" + referralRow[28].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@reason", "'" + referralRow[29].ToString().Trim() + "'");

EDIcmd.Parameters.AddWithValue("@attprovidtype", referralRow[30].ToString().Trim());

EDIcmd.Parameters.AddWithValue("@attprovid", referralRow[31].ToString().Trim() + "'");




EDIcmd.Parameters.AddWithValue("@self", "'" + referralRow[32].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@outofarea", "'" + referralRow[33].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@admittype", "'" + referralRow[34].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@clinicalnotes", "'" + referralRow[35].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@processid", "'" + referralRow[36].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@formatid", "'" + referralRow[37].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@ediclientid", "'" + referralRow[38].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@edistatus", "'" + referralRow[40].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@secondaryid", "'" + referralRow[41].ToString().Trim() + "'");
EDIcmd.Parameters.AddWithValue("@x_dispositionid", "'" + referralRow[42].ToString().Trim() + "'");




EDIcmd.CommandText = CommandType.Text.ToString();
EDIcmd.ExecuteNonQuery(); //

Sorry about that, you will have to list thecolumns before like so (....list columns) VALUES ( ....same order.....)

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Hi,

Syntax error in INSERT INTO statement.

This error occurs there are reserved words in Insert statement. You can check the keyword from this page: http://sqlserver2000.databases.aspfaq.com/what-are-reserved-access-odbc-and-sql-server-keywords.html

This KB tells about this error:

You may receive a "Syntax error in INSERT INTO statement" error message when you use ADO.NET code to access Office Access 2003

http://support.microsoft.com/kb/892608/en-us

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

This error tells the statement is not right. The statement is complex. You’d better show them out to checkwhether the statement is in a right format.

The following thread discuss a similar issue: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/712575d5-d2c5-4702-a861-67094e373aa5

Hope this helps.

Best regards,

Ling Wang

---

Please remember to click “Mark as Answer�on the post that helps you, and to click “Unmark as Answer�if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.