error while executing a ms-access query

Question

I created a query to insert into two ms access tables at a time in c#. I got the exception

{System.Data.OleDb.OleDbException: Characters found after end of SQL statement. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at CompanyDetails.Model.CompanyDetailsModel.setCompanyDetailsToDB(CompanyDetailsDataList _cmpDetailsList) in E:\Project\PBAttendence\ModifyPrivileage\CompanyDetails\Model\CompanyDetailsModel.cs:line 62}

my sample code is given below please solve my problem. sorry for my bad English.

int companyID = _cmpDetailsList[0].CompanyID;
                    string companyName = _cmpDetailsList[0].CompanyName;
                    string contactID = _cmpDetailsList[0].ContactID;
                    string companyAddress = _cmpDetailsList[0].CompanyAddress;

                    if (companyID == -1)
                    {
                        OleDbCommand cmd = new OleDbCommand("Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "');Insert into UserCompanyDetails([UserID],[CompanyID]) values (" + "Select [UserID] from UserDetails;" + "," + "Select @@identity;" + "); ", conn);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                    else
                    {
                        OleDbCommand upcmd = new OleDbCommand("update CompanyDetails set [CompanyName] = '" + companyName + "',[CompanyAddress] = '" + companyAddress + "',[ContactID] = '" + contactID + "' where [CompanyID] = @cmpID;", conn);
                        conn.Open();
                        upcmd.Parameters.AddWithValue("@cmpID", companyID);
                        upcmd.ExecuteNonQuery();
                        conn.Close();
                    }

now i split into two insert command but i got the error {System.Data.OleDb.OleDbException: Syntax error. in query expression 'Select [UserID] from UserDetails;

OleDbCommand cmd = new OleDbCommand("Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "');", conn);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                        OleDbCommand cmd1 = new OleDbCommand("Insert into UserCompanyDetails([UserID],[CompanyID]) values (" + "Select [UserID] from UserDetails;" + "," + "Select @@identity" + ");", conn);
                        conn.Open();
                        cmd1.ExecuteNonQuery();
                        conn.Close();

there are 2 queries in your question , it would be helpfull if you pointed out which line throws the error(hint: it's line 62 ;) ). — Kristof, Jun 06 '13 at 06:09
i think Select [UserID] from UserDetails; in your Insert statement is the problem. Do you want to insert it for multiple users? — Vishal Vaishya, Jun 06 '13 at 06:13
@VishalVaishya yes absolutely i want to insert into multiple users — Siva, Jun 06 '13 at 06:14
I guess your second insert statement should be like this: Insert into UserCompanyDetails([UserID],[CompanyID]) Select [UserID], @@identity from UserDetails; — Vishal Vaishya, Jun 06 '13 at 06:20

score 2 · Answer 1 · answered Jun 06 '13 at 06:15

2

The problem is this line of code:

OleDbCommand cmd = new OleDbCommand("Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "');Insert into UserCompanyDetails([UserID],[CompanyID]) values (" + "Select [UserID] from UserDetails;" + "," + "Select @@identity;" + "); ", conn);

You have two insert statements in the same OleDbCommand. Try to move this into two different steps:

Insert into CompanyDetails table
Insert into UserCompanyDetails table

Hope this helps you

answered Jun 06 '13 at 06:15

juanreyesv

853
9
22

is possible to execute two queries at a time – Siva Jun 06 '13 at 06:19
@Siva yes it is possible but you have to create a Stored Procedure to do it for you. You can't add two separate queries into the same OleDbCommand, that will generate the error that you have posted. [Here](http://stackoverflow.com/questions/3287545/how-do-i-make-a-stored-procedure-in-ms-access) is a good post on how to create Stored procedures in Access. You can go either way, I think is easier to split your code into two separate OleDbCommands because I've assumed that you're not using Stored procs – juanreyesv Jun 07 '13 at 00:29

unlimit · Answer 2 · 2013-06-06T10:07:54.707

0

You cannot have ; in queries in Access. See http://office.microsoft.com/en-us/access-help/HV080760224.aspx You will have to do the two inserts separately as suggested by @juanreyesv

You will have to do 3 queries,

Do the insert using your sql: "Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "')
Get the @@identity using Select @@identity and store it in a variable say idnt
Use the identity value obtained in 2. to do the third insert: "Insert into UserCompanyDetails([UserID],[CompanyID]) Select UserID, " + idnt.ToString() + " from UserDetails"

Refer to http://msdn.microsoft.com/en-us/library/ks9f57t0%28VS.71%29.aspx

edited Jun 06 '13 at 10:07

answered Jun 06 '13 at 06:20

unlimit

3,672
2
26
34

i try your query OleDbCommand cmd = new OleDbCommand("Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "'); Insert into UserCompanyDetails([UserID],[CompanyID]) Select X.UserID, Y.CompID from (Select UserID from UserDetails) X, (Select @@identity As CompID) Y;", conn); still i got the same error – Siva Jun 06 '13 at 06:30
Please debug and post the sql statement that is generated. You can do this this by adding a extra variable: `string strSql = "Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "'); Insert into UserCompanyDetails([UserID],[CompanyID]) Select X.UserID, Y.CompID from (Select UserID from UserDetails) X, (Select @@identity As CompID) Y;"; OleDbCommand cmd = new OleDbCommand(strSQL);` – unlimit Jun 06 '13 at 06:40
Put a breakpoint and see what is the value of strSQL and post it. – unlimit Jun 06 '13 at 06:55
this is the value in strSQL Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('TCS','adfasf','ad'); Insert into UserCompanyDetails([UserID],[CompanyID]) Select X.UserID, Y.CompID from (Select UserID from UserDetails) X, (Select @@identity As CompID) Y; – Siva Jun 06 '13 at 06:58
I think you cannot do two inserts in one statement. You will have to do the two inserts separately as suggested by @juanreyesv. – unlimit Jun 06 '13 at 07:57

score 0 · Answer 3 · answered Jun 06 '13 at 06:22

First of all , it would have been easier with the raw sql command then your code generating the sql.
You might consider making a stored procedure since your command is getting kinda complex
If i'm correct , what you are currently trying to do is :

Insert into table1(x,y,z) values a,b,c;
Insert into table2(x,y) values select * from table3; , @@identity

The second sql command is invalid in both syntax and logic, your @@identity won't be static since you're inserting new records during your command.
My recommendation would be to do something like this :

Insert into table1(x,y,z) values a,b,c;
declare @table1Id int = @@identity
Insert into table2(x,y) select colA, @table1Id from table3;

error while executing a ms-access query

3 Answers3

Linked