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

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;
        }

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

Thank you very much