Allem Montinola Allem Montinola - 3 months ago 9
C# Question

C# Syntax SQL Error Select ... Insert


"The select list for the INSERT statement contains fewer items than
the insert list. The number of SELECT values must match the number of
INSERT columns."


I have a question is this right? The construction of my codes?

private void btnCheckOut_Click(object sender, EventArgs e)
{
DialogResult res = MessageBox.Show("Are you done adding your items?","Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (res == DialogResult.Yes)
{
Conn = k.getConn();
Conn.Open();
str = "INSERT INTO Items (itemCode, itemBrand, itemColor , qty) SELECT itemCode, itemBrand, itemColor FROM Purchase";
cmd = new SqlCommand(str, Conn);
cmd.ExecuteNonQuery();
dr = cmd.ExecuteReader();
cmd.Dispose();
Conn.Close();

MessageBox.Show("Successfully Added to your CART" ,"Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (res == DialogResult.No)
{

}
}`


And i want to transfer that in the database named Items

Answer

In the query below:

str = "INSERT INTO Items (itemCode, itemBrand, itemColor , qty) SELECT itemCode, itemBrand, itemColor FROM Purchase";
cmd = new SqlCommand(str, Conn);

you must pass exactly four column names in the select query so that the clumn list matches the columns list of insert query. If you donot want to fetch data for quantity column, then remove it from the INSERT query.
You can also set a default value for the quantity column in your database to ensure that it gets a default integral value instead of NULL.

Secondaly, there is no need to use the following statement after ExecuteNonQuery():

dr = cmd.ExecuteReader();

because you are not doin anything with the data that may be conming to dr.
Instead, if you are trying to get the number of rows affected by the insert query, you can use the following code:

int cnt = cmd.ExecuteNonQuery();

and then show the value of this variable where ever you want, may be in a label.


Hope this helps.