Error when trying reduce a certain number from the database

Carol Cashman

hey guys,

I am creating a Materials resource planning system for an admin to be able to keep track of the materials needed, used etc.

When I receive an order via the phone the admin will enter in the size of the product they want and the amount of boxes needed (20 items per box).

This will then calculate all the materials needed to make the order (eg: small Jars, Large Jars, lids and stickers).

I am looking to find a solution to when the order is put through the system to deduct these materials needed to complete this order, to make sure the stock levels are kept up to date and do not run out of materials.

Therefore i am looking for a way to take the figure that shows up in a label on a web form away from the database level which is stored in a table called StockTable.

I have this code only for one material (stickers) to see if it worked, which it didnt.

this is my code:

protected void Button1_Click(object sender, EventArgs e)
{
//Creating a connection to my database using the connection string
string cs = System.Configuration.ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(cs);
//preparing a query which will delete a row in the database based on the id that is entered
SqlCommand cmd = new SqlCommand("UPDATE StockTable SET AmtOfStickers-'" + txtStickers.Text, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Response.Redirect("stock.aspx");
}
}

this is the error i am getting:

An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code

Additional information: ExecuteNonQuery: Connection property has not been initialized.

any advice is greatly appriciated.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilesh Shah8y ago
Accepted answer
I entirely missed the - (minus) sigh in your query.

that's what I suggested you in first answer, always take the SQL query string from your code and try to execute in SQL server, did you do that?

if you want to deduct then use this query:
SqlCommand cmd = new SqlCommand("UPDATE StockTable SET AmtOfStickers = AmtOfStickers-" + txtStickers.Text, con);
Carol Cashman8y ago
thank you so much!!!! @Nilesh Shah
Carol Cashman8y ago
@Nilesh Shah

thank you for your help!

I removed the single quotes as it was integer, but the same error is still coming up.

I think it is the

SqlCommand cmd = new SqlCommand("UPDATE StockTable SET
AmtOfStickers-" + txtStickers.Text, con); piece of code.

not sure this is the right way to deduct the value in the textbox from the database, any suggestions?
Nilesh Shah8y ago
Hi,

I suggest you to first of all check the connection string is proper and you can connect to SQL server using that values.

I see a simple mistake in your update query:
SqlCommand cmd = new SqlCommand("UPDATE StockTable SET AmtOfStickers-'" + txtStickers.Text, con);
there is a single quote ' before the value of textbox, so you are missing a closing single quote.
if the data type of AmtOfStickers in sql server is numeric or integer, then remove the single quote or if it is varchar or some text then add the missing single quote after the textbox value.

Advice for life time: always take the value of dynamic query string you generate in your code and execute it in SQL server. it should execute properly without any error, then only your code will work.