CODEWITHSUNDEEP
c#sql-serverdatabase
achap1989
achap1989

Reputation: 39

I am having trouble inserting into my database with c#

I get this error when trying to insert data into my database.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near the keyword 'User'.

Here is the code:

if(txtRegisterSecurityAnswerOne.TextLength >0 && txtRegisterSecurityAnswerTwo.TextLength >0)
{
    SqlConnection connection1 = new SqlConnection(
        Properties.Settings.Default.BlackBookDBConnectionString);

    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = "INSERT INTO User (Username, Password, SecurityQuestionOne, "
        + "SecurityQuestionTwo, SecurityAnswerOne, SecurityAnswerTwo); VALUES ("
        + txtRegisterUsername.Text + ","
        + txtRegisterPassword.Text + ","
        + lstRegisterSecurityQuestionOne.SelectedText + ","
        + lstRegisterSecurityQuestionTwo.SelectedItem + ","
        + txtRegisterSecurityAnswerOne.Text + ","
        + txtRegisterSecurityAnswerTwo.Text + ")";
    cmd.CommandText = "INSERT INTO USer ()";
    cmd.Connection = connection1;

    connection1.Open();
    cmd.ExecuteNonQuery();
    connection1.Close();
}

I have edited my code. However it still does not insert anything into my database for some reason.

if(txtRegisterSecurityAnswerOne.TextLength >0 && txtRegisterSecurityAnswerTwo.TextLength >0)
{
    SqlConnection connection1 = new SqlConnection(Properties.Settings.Default.BlackBookDBConnectionString);

    string sqlquery = "INSERT INTO [User] (Username,Password,SecurityQuestionOne,"
        + "SecurityAnswerOne,SecurityQuestionTwo,SecurityAnswerTwo) "
        + "VALUES (@Username,@Password,@QuestionOne,@AnswerOne,@QuestionTwo,@AnswerTwo)";
    SqlCommand command = new SqlCommand(sqlquery, connection1);

    string userName = txtRegisterUsername.Text;
    command.Parameters.AddWithValue("Username", userName);

    string password = txtRegisterRepeatPassword.Text;
    command.Parameters.AddWithValue("Password", password);

    string questionOne = lstRegisterSecurityQuestionOne.SelectedText;
    command.Parameters.AddWithValue("QuestionOne", questionOne);

    string questionTwo = lstRegisterSecurityQuestionTwo.SelectedText;
    command.Parameters.AddWithValue("QuestionTwo", questionTwo);

    string answerOne = txtRegisterSecurityAnswerOne.SelectedText;
    command.Parameters.AddWithValue("AnswerOne", answerOne);

    string answerTwo = txtRegisterSecurityAnswerTwo.SelectedText;
    command.Parameters.AddWithValue("AnswerTwo", answerTwo);

    command.Connection = connection1;

    connection1.Open();
    command.ExecuteNonQuery();
    connection1.Close();
}

Upvotes: 0

Views: 191

Answers (3)

Suman Banerjee
Suman Banerjee

Reputation: 1961

if(txtRegisterSecurityAnswerOne.TextLength >0 && txtRegisterSecurityAnswerTwo.TextLength >0)
{
    SqlConnection connection1 = new SqlConnection(Properties.Settings.Default.BlackBookDBConnectionString);
    connection1.Open();
    string sqlquery = "INSERT INTO [User] (Username,Password,SecurityQuestionOne,"
        + "SecurityAnswerOne,SecurityQuestionTwo,SecurityAnswerTwo) "
        + "VALUES (@Username,@Password,@QuestionOne,@AnswerOne,@QuestionTwo,@AnswerTwo)";

    SqlCommand command = new SqlCommand(sqlquery, connection1);

    string userName = txtRegisterUsername.Text;
    command.Parameters.Add("@Username", SqlDbType.VarChar, 200).Value = userName;
    string password = txtRegisterRepeatPassword.Text;
    command.Parameters.Add("@Password", SqlDbType.VarChar, 200).Value = password;
    string questionOne = lstRegisterSecurityQuestionOne.SelectedText;
    command.Parameters.Add("@QuestionOne", SqlDbType.VarChar, 200).Value = questionOne;
    string questionTwo = lstRegisterSecurityQuestionTwo.SelectedText;
    command.Parameters.Add("@QuestionTwo", SqlDbType.VarChar, 200).Value = questionTwo;
    string answerOne = txtRegisterSecurityAnswerOne.SelectedText;
    command.Parameters.Add("@AnswerOne", SqlDbType.VarChar, 200).Value = answerOne;
    string answerTwo = txtRegisterSecurityAnswerTwo.SelectedText;
    command.Parameters.Add("@AnswerTwo", SqlDbType.VarChar, 200).Value = answerTwo;

    command.ExecuteNonQuery();
    connection1.Close();
}

Upvotes: 1

ekad
ekad

Reputation: 14614

Remove this line:

cmd.CommandText = "INSERT INTO USer ()";

EDIT
After looking at your new code, you have wrong parameters names (missing @). You should change your code to this:

string userName = txtRegisterUsername.Text;
command.Parameters.AddWithValue("@Username", userName);

string password = txtRegisterRepeatPassword.Text;
command.Parameters.AddWithValue("@Password", password);

string questionOne = lstRegisterSecurityQuestionOne.SelectedText;
command.Parameters.AddWithValue("@QuestionOne", questionOne);

string questionTwo = lstRegisterSecurityQuestionTwo.SelectedText;
command.Parameters.AddWithValue("@QuestionTwo", questionTwo);

string answerOne = txtRegisterSecurityAnswerOne.SelectedText;
command.Parameters.AddWithValue("@AnswerOne", answerOne);

string answerTwo = txtRegisterSecurityAnswerTwo.SelectedText;
command.Parameters.AddWithValue("@AnswerTwo", answerTwo);

Upvotes: 3

suff trek
suff trek

Reputation: 39767

  1. Remove second line assigning cmd.CommandText - it's overwriting the first one

  2. User is a keyword in SQL server, if you have a table with that name (which you should not) enclose it into square brackets:

    cmd.CommandText = "INSERT INTO [User] ...

On a side note - learn about parametrized queries. They're a great way to avois SQL injection attacks (not to mention confusing mess of string concatenation)

Upvotes: 2

Related Questions