locked
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement RRS feed

  • Question

  • System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.OleDb.OleDbException: Syntax error in INSERT INTO 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 AppsStoreService.Service1.RegistrationTest(String username, String password) in C:\Users\Kaung Htet\Documents\Visual Studio 2010\Projects\AppStore\AppsStoreService\Service1.asmx.cs:line 116 --- End of inner exception stack trace ---

    Can someone check my error?. I used web service method to connect to database and when I try to INSERT INTO two or more data, my method doesn't work and shows such error. Here is my web service method:

    [WebMethod]
            public bool Registration(string firstname, string lastname, string username, string password, string country, string dob, string email)
            {
                OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\\Temp\\AppsStore.mdb");
                conn.Open();
    
                OleDbCommand cmd = new OleDbCommand("INSERT INTO Registration_Details (FirstName, LastName, UserName, Password, Country, DOB, Email) VALUES (@firstname, @lastname, @username, @password, @country, @dob, @email)",
                conn);
                cmd.Parameters.AddWithValue("@firstname",firstname);
                cmd.Parameters.AddWithValue("@lastname", lastname);
                cmd.Parameters.AddWithValue("@username", username);
                cmd.Parameters.AddWithValue("@password", password);
                cmd.Parameters.AddWithValue("@country", country);
                cmd.Parameters.AddWithValue("@dob", dob);
                cmd.Parameters.AddWithValue("@email", email);
    
                bool result;
                if (cmd.ExecuteNonQuery() == 1)
                    result = true;
                else
                    result = false;
    
                conn.Close();
    
                return result;
            }

    Monday, February 20, 2012 6:49 PM

Answers

  • In addition, note that Password is a keyword. So, you must enclose it in square brackets:

    OleDbCommand cmd = new OleDbCommand("INSERT INTO Registration_Details (FirstName, LastName, UserName, [Password], Country, DOB, Email) 
                                                   VALUES (?, ?, ?, ?, ?, ?, ?)", conn);


    Marco Minerva [MCPD]
    Blog: http://blogs.ugidotnet.org/marcom
    Twitter: @marcominerva

    • Edited by Marco MinervaMVP Wednesday, February 22, 2012 9:14 AM
    • Proposed as answer by Heslacher Wednesday, February 22, 2012 9:17 AM
    • Marked as answer by Dummy yoyo Monday, March 5, 2012 4:49 AM
    Wednesday, February 22, 2012 9:14 AM
  • OleDb doesn't support named parameters. Try with:

    OleDbCommand cmd = new OleDbCommand(
          "INSERT INTO Registration_Details (FirstName, LastName, UserName, Password, Country, DOB, Email) 
           VALUES (?, ?, ?, ?, ?, ?, ?)", conn);


    Marco Minerva [MCPD]
    Blog: http://blogs.ugidotnet.org/marcom
    Twitter: @marcominerva

    • Edited by Marco MinervaMVP Monday, February 20, 2012 7:20 PM
    • Proposed as answer by Dummy yoyo Wednesday, February 22, 2012 9:38 AM
    • Marked as answer by Dummy yoyo Monday, March 5, 2012 4:49 AM
    Monday, February 20, 2012 7:20 PM

All replies

  • OleDb doesn't support named parameters. Try with:

    OleDbCommand cmd = new OleDbCommand(
          "INSERT INTO Registration_Details (FirstName, LastName, UserName, Password, Country, DOB, Email) 
           VALUES (?, ?, ?, ?, ?, ?, ?)", conn);


    Marco Minerva [MCPD]
    Blog: http://blogs.ugidotnet.org/marcom
    Twitter: @marcominerva

    • Edited by Marco MinervaMVP Monday, February 20, 2012 7:20 PM
    • Proposed as answer by Dummy yoyo Wednesday, February 22, 2012 9:38 AM
    • Marked as answer by Dummy yoyo Monday, March 5, 2012 4:49 AM
    Monday, February 20, 2012 7:20 PM
  • Hi KHtet,

    Welcome to the MSDN forum!

    I agree with Marco. Please see: 

    OleDbCommand.Parametershttp://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx

    The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

    SELECT * FROM Customers WHERE CustomerID = ?

    You may try the following code:

                OleDbCommand cmd = new OleDbCommand("INSERT INTO Registration_Details (FirstName, LastName, UserName, Password, Country, DOB, Email) VALUES (?, ?, ?, ?, ?, ?, ?)",
                conn);
                cmd.Parameters.AddWithValue("@FirstName",firstname);
                cmd.Parameters.AddWithValue("@LastName", lastname);
                cmd.Parameters.AddWithValue("@UserName", username);
                cmd.Parameters.AddWithValue("@Password", password);
                cmd.Parameters.AddWithValue("@Country", country);
                cmd.Parameters.AddWithValue("@DOB", dob);
                cmd.Parameters.AddWithValue("@Email", email);

    Hope it helps.

    Have a nice day!

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 22, 2012 8:48 AM
  • In addition, note that Password is a keyword. So, you must enclose it in square brackets:

    OleDbCommand cmd = new OleDbCommand("INSERT INTO Registration_Details (FirstName, LastName, UserName, [Password], Country, DOB, Email) 
                                                   VALUES (?, ?, ?, ?, ?, ?, ?)", conn);


    Marco Minerva [MCPD]
    Blog: http://blogs.ugidotnet.org/marcom
    Twitter: @marcominerva

    • Edited by Marco MinervaMVP Wednesday, February 22, 2012 9:14 AM
    • Proposed as answer by Heslacher Wednesday, February 22, 2012 9:17 AM
    • Marked as answer by Dummy yoyo Monday, March 5, 2012 4:49 AM
    Wednesday, February 22, 2012 9:14 AM
  • Thank you very much
    Sunday, October 13, 2013 7:29 AM