ExecuteNonQuery() returns -1

Question

For some reason result is always -1 and nothing get added to the database. I executed the query in SQL Server and it runs fine. I don't get any exception whatsoever and I don't use any stored procedure.

SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=RAINBOW;Integrated Security=True"); SqlCommand cmd; cmd = new SqlCommand("INSERT INTO ItemDetails.item(description,category_id) VALUES (@item_desc,@cat_id)", con); cmd.Parameters.AddWithValue("@item_desc", txtitemdesc.Text); cmd.Parameters.AddWithValue("@cat_id", GetCategoryID()); try { con.Open(); int result = cmd.ExecuteNonQuery(); if (result > 0) { MessageBox.Show("Record Inserted Successfully!"); } else { MessageBox.Show("Failed to add record"); } } catch (SqlException ex) { MessageBox.Show("An error has occured! " + ex); } finally { con.Close(); }

Edit

 int GetCategoryID() { int cat_id = 0; cmd = new SqlCommand("SELECT category_id FROM ItemDetails.category WHERE category_desc=@cat_desc", con); con.Open(); cmd.Parameters.AddWithValue("@cat_desc", cboCategory.Text); reader = cmd.ExecuteReader(); while (reader.Read()) { cat_id = int.Parse(reader["category_id"].ToString()); } reader.Close(); con.Close(); return cat_id; }

Did you try a select already if that is also working (but from C#)? One thing that is always a good thing to control is the connectionstring if it is correct and if it works as intended (a plain select with no parameters is a good choice there for that test). — Thomas
– Thomas, Commented Nov 25, 2015 at 9:17
and also did you check if GetCategoryID() and txtitemdesc.Text contain values? — Thomas
– Thomas, Commented Nov 25, 2015 at 9:17
Remove the AddWithValue() and add parameter like this - cmd.Parameters.Add("@item_desc", SqlDbType.VarChar, 1000).Value = txtitemdesc.Text; — Krishnraj Rana
– Krishnraj Rana, Commented Nov 25, 2015 at 9:17
Are there any triggers on this table? Constraints or FKs which might get violated? Perhaps even other columns requiring a value in this table, that could prevent the values being inserted? — Bridge
– Bridge, Commented Nov 25, 2015 at 9:19
If you use the select statement it's a normal behavior because For select statements, the return value of ExecuteNonQuery is -1 but since your problem is with Insert you should have no problem if you are sure that GetCategoryID() and txtitemdesc.Text contain values and nothing else prevent the values to being inserted. Have a look at this answer: stackoverflow.com/a/32736842/2946329 — Salah Akbari
– Salah Akbari, Commented Nov 25, 2015 at 9:22

Krishnraj Rana · Accepted Answer · 2015-11-25 15:41:08Z

If possible then don't use AddWithValue(). Actually when you are not providing type explicitly, it will try to convert implicitly and sometimes the implicit conversion may not be the most optimal of conversions. You can find some more discussion in this link.

And most important thing is don't forget to clear parameters before assign, by using this line.

cmd.Parameters.Clears();

Check below code.

string sqlQuery = "INSERT INTO ItemDetails.item(description,category_id) VALUES (@item_desc,@cat_id)"; using (SqlCommand cmd = new SqlCommand(sqlQuery, con)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Clears(); // Add this same line in your getcategory function. cmd.Parameters.Add("@item_desc", SqlDbType.VarChar, 1000).Value = txtitemdesc.Text; cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value = GetCategoryID(); try { con.Open(); int result = cmd.ExecuteNonQuery(); if (result > 0) { MessageBox.Show("Record Inserted Successfully!"); } else { MessageBox.Show("Failed to add record"); } } catch (SqlException ex) { MessageBox.Show("An error has occured! " + ex); } finally { con.Close(); } }

the problem with my code is the GetCategoryID().I coded it to return an integer but somehow i can''t get it to work with Add or AddWithValue both your code and i work if i hardcode the id
@user2650277: Can you show us your GetCategoryID() function's code in your question ?
@user2650277: cboCategory is combobox ?? if yes then it should be cboCategory.SelectedValue
error happen on the following line reader = cmd.ExecuteReader();
@user2650277: Chage this line from cmd.Parameters.AddWithValue("@cat_desc", cboCategory.Text); to this line cmd.Parameters.Add("@cat_desc", SqlDbType.varchar, 500).Value = cboCategory.Text;

Collectives™ on Stack Overflow

ExecuteNonQuery() returns -1

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related