CODEWITHSUNDEEP
c#
Siva
Siva

Reputation: 259

error while executing a ms-access query

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();

Upvotes: 0

Views: 649

Answers (3)

unlimit
unlimit

Reputation: 3752

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,

  1. Do the insert using your sql: "Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "')
  2. Get the @@identity using Select @@identity and store it in a variable say idnt
  3. 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

Upvotes: 0

Kristof
Kristof

Reputation: 3315

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;

Upvotes: 0

juanreyesv
juanreyesv

Reputation: 853

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:

  1. Insert into CompanyDetails table
  2. Insert into UserCompanyDetails table

Hope this helps you

Upvotes: 2

Related Questions