Getting SQLException when debugging

Question

I've got a error which I can't understand. When I'm debugging and trying to run a insert statement, its throwing the following exception:

"There are fewer 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."

I have looked all over my code, and I can't find the mistake I've made.

This is the query and the surrounding code:

SqlConnection myCon = DBcon.getInstance().conn();
int id = gm.GetID("SELECT ListID from Indkøbsliste");
id++;

Console.WriteLine("LNr: " + listnr);
string streg = GetStregkode(navne);
Console.WriteLine("stregk :" + strege);
string navn = GetVareNavn(strege);
Console.WriteLine("navn :" + navne);

myCon.Open();
string query = "INSERT INTO Indkøbsliste (ListID, ListeNr, Stregkode, Navn, Antal, Pris) Values(" + id + "," + listnr + ", '" + strege + "','" + navn + "'," + il.Antal + ", "+il.Pris+")";
Console.WriteLine(il.Antal+" Antal");
Console.WriteLine(il.Pris+" Pris");
Console.WriteLine(id + " ID");
SqlCommand com = new SqlCommand(query, myCon);
com.ExecuteNonQuery();
com.Dispose();
myCon.Close();

auto increment columns can also have values be manually inserted, so that probably isn't the reason for this. — Jan S
– Jan S, Commented Sep 20, 2011 at 7:49
Yes, but only if set identity_insert <table> on is set in the session - which I assume it is not the default. — Christian.K
– Christian.K, Commented Sep 20, 2011 at 7:55

KV Prajapati · Accepted Answer · 2011-09-20 07:53:00Z

1

First of all check the connection string and confirm the database location and number of columns a table has.

Suggestion : Do not use hardcoded SQL string. Use parameterized sql statements or stored-proc.

Try parameterized way,

string query = "INSERT INTO Indkøbsliste (ListID, ListeNr, Stregkode, Navn, Antal, Pris)   
         Values (@ListID, @ListeNr, @Stregkode, @Navn, @Antal, @Pris)"

SqlCommand com = new SqlCommand(query, myCon);
com.Parameters.Add("@ListID",System.Data.SqlDbType.Int).Value=id;
com.Parameters.Add("@ListeNr",System.Data.SqlDbType.Int).Value=listnr;
com.Parameters.Add("@Stregkode",System.Data.SqlDbType.VarChar).Value=strege ;
com.Parameters.Add("@Navn",System.Data.SqlDbType.VarChar).Value=navn ;
com.Parameters.Add("@Antal",System.Data.SqlDbType.Int).Value=il.Antal;
com.Parameters.Add("@Pris",System.Data.SqlDbType.Int).Value=il.Pris;

com.ExecuteNonQuery();

answered Sep 20, 2011 at 7:53

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

+1 for never using concatenated SQL statements... ALWAYS use parametrized queries.

KV Prajapati Over a year ago

@marc_s Thank you Sir. I'm pleased to receive a compliment.

Christian.K · Accepted Answer · 2011-09-20 07:51:40Z

1

Please always use parametrized queries. This helps with errors like the one you have, and far more important protects against SQL injection (google the term, or check this blog entry - as an example).

For example, what are the actual values of strege and/or navn. Depending on that it may render your SQL statement syntactically invalid or do something worse.

It (looks like) a little more work in the beginning, but will pay off big time in the end.

answered Sep 20, 2011 at 7:51

Christian.K

49.6k10 gold badges108 silver badges155 bronze badges

Comments

Kjetil Watnedal · Accepted Answer · 2011-09-20 07:55:00Z

0

Are you using danish culture settings?

In that case if il.Pris is a double or decimal it will be printed using comma, which means that your sql will have an extra comma.

Ie:

INSERT INTO Indkøbsliste (ListID, ListeNr, Stregkode, Navn, Antal, Pris) Values(33,5566, 'stegkode','somename',4, 99,44)

where 99,44 is the price.

The solution is to use parameters instead of using the values directly in you sql. See some of the other answers already explaining this.

answered Sep 20, 2011 at 7:55

Kjetil Watnedal

6,1773 gold badges32 silver badges23 bronze badges

Collectives™ on Stack Overflow

Getting SQLException when debugging

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related