CODEWITHSUNDEEP
c#sql-serverexecutenonquery
user2650277
user2650277

Reputation: 6739

ExecuteNonQuery() returns -1

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;
        }

Upvotes: 0

Views: 477

Answers (1)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

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();
    }
}

Upvotes: 1

Related Questions