The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Question

0.00/5 (No votes)

See more:

string str = "insert into TblCheckOut(Name,Address,Mobile,RoomType,InDate,OutDate,Days,RoomCharge,RoomBill,Otherservices,TotalBill,Status) values ('" + combocustomer.Text + "','" + lbladdress.Text + "','" + lblmobile.Text + "','" + lbltype.Text + "','" + dateTimePicker1.Text + "','" + dateTimePicker2.Text + "','" + lbldays.Text + "','" + txtroomcharge.Text + "','" + txtOtherServices.Text + "','" + txtTotoalBill.Text + "','" + combostatus.Text + "')";

Find the above code with query but am facing issue , can you please help for this error

What I have tried:

<pre>string str = "insert into TblCheckOut(Name,Address,Mobile,RoomType,InDate,OutDate,Days,RoomCharge,RoomBill,Otherservices,TotalBill,Status) values ('" + combocustomer.Text + "','" + lbladdress.Text + "','" + lblmobile.Text + "','" + lbltype.Text + "','" + dateTimePicker1.Text + "','" + dateTimePicker2.Text + "','" + lbldays.Text + "','" + txtroomcharge.Text + "','" + txtOtherServices.Text + "','" + txtTotoalBill.Text + "','" + combostatus.Text + "')";

Posted 24-May-20 4:20am

Member 11297177

Updated 24-May-20 7:48am

Add a Solution

4 solutions

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)

OriginalGriff · Answer 1 · 2020-05-24T04:42:00

Even if you think you have fixed it, don't do it like that!
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Oh, and validate your inputs before you pass them to SQL - numbers should be checked using TryParse and the numeric value passed as a parameter, DateTimePicker values should be passed as DateTime, not string.
And that lot implies you are storing everything as VARCHAR or NVARCHAR, which is a very bad idea - it means that you need to convert it when you want to do anything useful with it - compare it, sum it, sort it - and that's when you find all the bad data in your DB.
Validate user input - we all make mistakes - and only store good data in appropriate datatypes. It seems like extra work, but it's pretty trivial, and it saves you masses of work later on.

Patrice T · Answer 2 · 2020-05-24T06:02:00

C#

string str = "insert into TblCheckOut(Name,Address,Mobile,RoomType,InDate,OutDate,Days,RoomCharge,RoomBill,Otherservices,TotalBill,Status) values ('" + combocustomer.Text + "','" + lbladdress.Text + "','" + lblmobile.Text + "','" + lbltype.Text + "','" + dateTimePicker1.Text + "','" + dateTimePicker2.Text + "','" + lbldays.Text + "','" + txtroomcharge.Text + "','" + txtOtherServices.Text + "','" + txtTotoalBill.Text + "','" + combostatus.Text + "')";

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

MadMyche · Answer 3 · 2020-05-24T07:49:00

The proper way to do this; as previously stated; is via the use of the Sql Parameters Collection[^].

Now the easiest way to implement in your case is going to be break out the INSERT table and columns portion of your command into multiple lines like this

SQL

INSERT TblCheckOut
( Name
, Address
, Mobile
, RoomType
, InDate
, OutDate
, Days
, RoomCharge
, RoomBill
, Otherservices
, TotalBill
, Status
)

And then replicate that into your values, pre-pending an ampersand onto the various column names like this for the VALUES half of your query

SQL

VALUES
( @Name
, @Address
, @Mobile
, @RoomType
, @InDate
, @OutDate
, @Days
, @RoomCharge
, @RoomBill
, @Otherservices
, @TotalBill
, @Status
)

And then take those values once again; copy them and pre-pend those lines with cmd.Parameters.AddWithValue (replace cmd to reflect your SqlCommand variable name)

C#

cmd.Parameters.AddWithValue("@Name", );
cmd.Parameters.AddWithValue("@Address", );
cmd.Parameters.AddWithValue("@Mobile", );
cmd.Parameters.AddWithValue("@RoomType", );
cmd.Parameters.AddWithValue("@InDate", );
cmd.Parameters.AddWithValue("@OutDate", );
cmd.Parameters.AddWithValue("@Days", );
cmd.Parameters.AddWithValue("@RoomCharge", );
cmd.Parameters.AddWithValue("@RoomBill", );
cmd.Parameters.AddWithValue("@Otherservices", );
cmd.Parameters.AddWithValue("@TotalBill", );
cmd.Parameters.AddWithValue("@Status", );

And then you fill in the values from your textboxes, dropdowns, etc... And we can now see which item you forgot

C#

cmd.Parameters.AddWithValue("@Name", combocustomer.Text);
cmd.Parameters.AddWithValue("@Address", lbladdress.Text);
cmd.Parameters.AddWithValue("@Mobile", lblmobile.Text);
cmd.Parameters.AddWithValue("@RoomType", lbltype.Text);
cmd.Parameters.AddWithValue("@InDate", dateTimePicker1.Text);
cmd.Parameters.AddWithValue("@OutDate", dateTimePicker2.Text);
cmd.Parameters.AddWithValue("@Days", lbldays.Text);
cmd.Parameters.AddWithValue("@RoomCharge", txtroomcharge.Text);
cmd.Parameters.AddWithValue("@RoomBill", );                    // missing value
cmd.Parameters.AddWithValue("@Otherservices", txtOtherServices.Text);
cmd.Parameters.AddWithValue("@TotalBill", txtTotoalBill.Text);
cmd.Parameters.AddWithValue("@Status", combostatus.Text);

And actually in this state your program most likely won't compile.

Obviously once you got all of this worked out you can remove all of the line breaks; so your final code should be similar to this

C#

string str = "INSERT TblCheckOut (Name,Address,Mobile,RoomType,InDate,OutDate,Days,RoomCharge,RoomBill,Otherservices,TotalBill,Status) VALUES (@Name,@Address,@Mobile,@RoomType,@InDate,@OutDate,@Days,@RoomCharge,@RoomBill,@Otherservices,@TotalBill,@Status)";

SqlCommand cmd = new SqlCommand(str, conn);
cmd.Parameters.AddWithValue("@Name", combocustomer.Text);
cmd.Parameters.AddWithValue("@Address", lbladdress.Text);
cmd.Parameters.AddWithValue("@Mobile", lblmobile.Text);
cmd.Parameters.AddWithValue("@RoomType", lbltype.Text);
cmd.Parameters.AddWithValue("@InDate", dateTimePicker1.Text);
cmd.Parameters.AddWithValue("@OutDate", dateTimePicker2.Text);
cmd.Parameters.AddWithValue("@Days", lbldays.Text);
cmd.Parameters.AddWithValue("@RoomCharge", txtroomcharge.Text);
cmd.Parameters.AddWithValue("@RoomBill", );                      // missing value
cmd.Parameters.AddWithValue("@Otherservices", txtOtherServices.Text);
cmd.Parameters.AddWithValue("@TotalBill", txtTotoalBill.Text);
cmd.Parameters.AddWithValue("@Status", combostatus.Text);

Now, some other observations about this:
1. All of your values are being put in as text; several appear to be numeric or dates
2. You have column names that are generally avoided as they are special or reserved words

Member 11297177 · Answer 4 · 2020-05-24T04:38:00

Solution 1

I got it , i have missed one txtbill.text value here. is working fine .

Posted 24-May-20 4:38am

Member 11297177

Comments

MadMyche 24-May-20 13:06pm

If you did things properly you wouldn't have had this problem to begin with.

Dave Kreskowiak 24-May-20 14:29pm

Yep, it'll work fine, right up to the point where someone enters an apostrophe in one of those fields. If someone enters a name like "Pete O'Leary", your code is going to crash again.

Rewrite the code using parameters and you won't have that problem.

The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

4 solutions

Solution 2

Solution 3

Solution 4

Solution 1

Add your solution here

Preview 0