Invalid INSERT INTO Sql Command

Question

5.00/5 (1 vote)

See more:

When this code is ran I get this runtime error:
"Syntax error in insert into statement"

C#

void SaveData()
        {//do the real work here
            string insertCMD = "INSERT INTO Users (Username,Password,RegisterEmail) VALUES (@Username,@[Password],@RegisterEmail)";
            string ConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\RS_Studio_Login.mdb";
            using (OleDbConnection MyConn = new OleDbConnection(ConnStr))
            {
                try
                {//insertCommand.Parameters.Add("Time", OleDbType.Char).Value = strTime;
                    MyConn.Open();
                    OleDbCommand Cmd = new OleDbCommand(insertCMD, MyConn);
                    Cmd.Parameters.Add("@Username", OleDbType.Char).Value = textEdit1.Text;
                    Cmd.Parameters.Add("@[Password]", OleDbType.Char).Value = textEdit2.Text;
                    Cmd.Parameters.Add("@RegisterEmail", OleDbType.Char).Value = textEdit3.Text;
                    Cmd.ExecuteNonQuery();
                    MyConn.Close();
                    MessageBox.Show("User creation successful!");
                    this.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

        }

Posted 3-Jul-12 19:15pm

shelby67

Add a Solution

2 solutions

Solution 1

According to MSDN article here[^], the OLEDB Provider does not support the Sql Style of Parameters. As it does not support the named parameters, they state as

Quote:
Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Change your code to look like the following:

C#

string insertCMD = "INSERT INTO Users (Usern,Passw,RegisterEmail) VALUES (?,?,?)";

and

C#

insertCMD.Parameters.Add(textEdit1.Text);
insertCMD.Parameters.Add(textEdit2.Text);
insertCMD.Parameters.Add(textEdit3.Text);

Does this help?

Posted 3-Jul-12 19:27pm

Pankaj Nikam

Updated 4-Jul-12 22:34pm

v3

Comments

shelby67 4-Jul-12 1:31am

Even with these changes it fails. No error message at all, nothing to show it failed.

Pankaj Nikam 4-Jul-12 1:38am

What are you getting in the MessageBox.Show(ex.Message); statement?

shelby67 4-Jul-12 11:51am

tells me there's an error with the sql command syntax.
I've made the suggested changes to the following:
void SaveData()
{//do the real work here
string insertCMD = "INSERT INTO Users (Usern,[Passw],RegisterEmail) VALUES (@Usern,@Pwd,@RegisterEmail)";
string ConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Sagitta\Documents\RS_Studio_User_Login.mdb";
using (OleDbConnection MyConn = new OleDbConnection(ConnStr))
{
try
{//insertCommand.Parameters.Add("Time", OleDbType.Char).Value = strTime;
MyConn.Open();
OleDbCommand Cmd = new OleDbCommand(insertCMD, MyConn);
Cmd.Parameters.Add("@Usern", OleDbType.Char).Value = textEdit1.Text;
Cmd.Parameters.Add("@Pwd", OleDbType.Char).Value = textEdit2.Text;
Cmd.Parameters.Add("@RegisterEmail", OleDbType.Char).Value = textEdit3.Text;
Cmd.ExecuteNonQuery();
MyConn.Close();
MessageBox.Show("User creation successful!");
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

}

and used "Passw" and "Usern" instead of "Username and "Password" in the database

Pankaj Nikam 5-Jul-12 4:35am

Check out the updated answer. Does it solve your problem?

Sandeep Mewara 4-Jul-12 1:40am

See my answer. It's because of the reserved keyword used.

Pankaj Nikam 4-Jul-12 1:44am

:) yes! I had used one of the keywords as the name of the column in SQL Server 2008 and used the square brackets to surround it and it worked. Hence I thought that it must be similar with Access as both being an MS product :) Thanks for the tip.

Sandeep Mewara 4-Jul-12 1:53am

:thumbsup:

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2012-07-03T19:39:00

Solution 2

This problem may occur if your database table contains column names that use Microsoft Jet 4.0 reserved words.
PASSWORD is a KEYWORD here[^] and thus an error.

Change the column name to get away with the error.

Details, refer here: MS Support: You may receive a "Syntax error in INSERT INTO statement" error message [^]

Posted 3-Jul-12 19:39pm

Sandeep Mewara

v2

Comments

Vani Kulkarni 4-Jul-12 1:42am

+5!

Pankaj Nikam 4-Jul-12 1:44am

+5 from me too :)

[no name] 4-Jul-12 2:11am

my 5+

[no name] 5-Jul-12 4:41am

+5