'There are more columns in the INSERT statement than values specified in the VALUES clause [closed]

I want to save new record in SQL table. one of this table field is "username" that I want to add record by using session and other fields are getting from user. here is my code in C# asp.net:

protected void savesubmit_Click(object sender, EventArgs e)
{

    SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=Khayati;Integrated Security=True");
    string qu = string.Format("insert into Ordertb (nokar,modeledokht,tozihat,username) values('{0}','{1}',N'{2}',N'{3}')", DropDownList1.Text, DropDownList2.Text, tozihtxt.Text, Convert.ToString(Session["username1"]));
    SqlDataAdapter da = new SqlDataAdapter(qu, con);
    DataSet ds = new DataSet();
    da.Fill(ds, "ordertbl");

}

but when i run it i see this error:

There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Description: An unhandled exception occurred during the execution of the current web request. please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

c++sharpasp.netsql-server


Solution 1:[1]

Your problem is that you are attempting to insert 3 values:

values('{0}','{1}',N'{2}')

Into 4 columns:

(nokar,modeledokht,tozihat,username)

I believe you meant to do this:

values('{0}','{1}',N'{2}','{3}')

Side note:

Always use Command.Parameters instead of parsing your command as string! When parsing the command as a string, you are subjected to SQL injections and errors like the one you are having. Using Command.Parameters makes it safer and easier.

Example:

SqlCommand Command = new SqlCommand();
Command.CommandText = "insert into tbl (col) values (@val)";
Command.Parameters.Add(new SqlParameter("val", valueVariable));
Command.ExecuteNonQuery();

Or, in your case:

SqlCommand Command = new SqlCommand();

Command.CommandText = @"insert into Ordertb 
                        (nokar,modeledokht,tozihat,username) 
                        values 
                        (@nokar,@modeledokht,@tozihat,@username)";

Command.Parameters.Add(new SqlParameter("nokar", DropDownList1.Text));
Command.Parameters.Add(new SqlParameter("modeledokht", DropDownList2.Text));
Command.Parameters.Add(new SqlParameter("tozihat", tozihtxt.Text));
Command.Parameters.Add(new SqlParameter("username", Convert.ToString(Session["username1"])));

Command.ExecuteNonQuery();

Solution 2:[2]

insert into Ordertb (nokar,modeledokht,tozihat,username) values('{0}','{1}',N'{2}')

You've specified that you are setting four columns but only provide 3 values.

(And have a downvote for SQL Injection vulnerability: always parametrise your queries.)

Solution 3:[3]

Basically you just need to add the fourth value to your SQL statement.

protected void savesubmit_Click(object sender, EventArgs e)
{

    SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=Khayati;Integrated Security=True");
    string qu = string.Format("insert into Ordertb (nokar,modeledokht,tozihat,username) values('{0}','{1}',N'{2}', '{3}')", DropDownList1.Text, DropDownList2.Text, tozihtxt.Text, Convert.ToString(Session["username1"]));
    SqlDataAdapter da = new SqlDataAdapter(qu, con);
    DataSet ds = new DataSet();
    da.Fill(ds, "ordertbl");

}

Nevertheless you should think about using SQL parameter.

Solution 4:[4]

To avoid this error, make sure that the number of values specified in the VALUES clause matches the number of columns specified in the INSERT INTO clause:

Make sure all values or not empty or null.

(or)

try this way:

INSERT INTO Ordertb ( nokar,modeledokht,tozihat,username ) VALUES ( 'Mickey', 'Mouse', 'M' ,'XYZ')

Solution 5:[5]

Also it is good when you use using to stablish a connection so you dont have to worry about closing it after.

    using (SqlCommand commandInsert = new SqlCommand(YourQuery, YourConnectionString))
        {
            commandInsert.Parameters.Add(parameterAccount);
            commandInsert.Parameters.Add(parameterPassword);
            commandInsert.Parameters.Add(parameterBalance);
            commandInsert.Parameters.Add(parameterAccountStatus);
            commandInsert.Parameters.Add(parameterDateOfCreation);

            commandInsert.ExecuteNonQuery();

            connection.Close();

        };

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Richard
Solution 3 André
Solution 4 VIGNESH ARUNACHALAM
Solution 5 EAzevedo

Related Questions