comparing data of textbox with database and update database with the new value

Question

i am trying to compare the value of textbox with the data in database, and if the value in textbox is bigger den the value of data in database, a alert message will appear and if it is smaller than the value of the data in the datebase, it will minus the database value with the value of textbox and update the value of database. there is no error but when i click on the button, nothing appear and no changes have made to the database. is there anything wrong with my code? thx

protected void btn_buy_Click(object sender, EventArgs e)
{


    hookUp = new SqlConnection("Server=localhost\\SqlExpress;Database=VoteNow;" +
    "Integrated Security=True" );
    sqlCmd = new SqlCommand("SELECT  UnitAvailable  FROM stockdetails1 WHERE StockID = 3", hookUp);
    hookUp.Open();
    reader = sqlCmd.ExecuteReader();
    int amountkey;
    amountkey = Convert.ToInt32(amount.Text);
    while (reader.Read())
    {
        int unitavailable = reader.GetInt32(0);
        int unitavailable1;
        if (amountkey <= unitavailable)
        {
           unitavailable1 = unitavailable - amountkey;
            SqlCommand sqlupdateCmd = new SqlCommand("UPDATE StockDetails Set UnitAvailable = '+unitavailable1+' WHERE StockID = 3", hookUp);

            sqlupdateCmd.ExecuteNonQuery();

            Response.Write("<script LANGUAGE='JavaScript'> alert('The units available is enough.')</script>"); 
        }
        else 
        {
            Response.Write("<script LANGUAGE='JavaScript'> alert('The units available is not enough.')</script>");

        }
    }
    reader.Close();
    hookUp.Close();
}

}

Rahul Singh · Accepted Answer · 2014-11-23 08:58:52Z

1

You are missing sqlupdateCmd.ExecuteNonQuery() after your update command. Also, I don't understand Why you are alerting the same message again in else block of code? The other thing is, probably UnitAvailable is a single value so instead of using ExecuteReader use ExecuteScalar.

Also, consider implementing your code with using which will dispose the expensive resources such as connection and command objects.Please check this for more.

Your Javascript alert shoul look like:-

Response.Write("<script type=\"text/javascript\">alert('The units available is not enough.')</script>");

Update 2:

Please use ExecuteScalar as i mentioned above like this:-

sqlCmd = new SqlCommand("SELECT  UnitAvailable  FROM StockDetails WHERE StockID = 3", hookUp);
hookUp.Open();
int unitavailable = Convert.ToInt32(sqlCmd.ExecuteScalar());
int amountkey;
amountkey = Convert.ToInt32(amount.Text);
int unitavailable1;
if (amountkey <= unitavailable)
{
    Response.Write("<script LANGUAGE='JavaScript'> alert('The units available is not enough.')</script>");
}
else
{
     unitavailable1 = unitavailable - amountkey;
     SqlCommand sqlupdateCmd = new SqlCommand("UPDATE StockDetails Set UnitAvailable = @unitavailable1 WHERE StockID = 3", hookUp);
     sqlupdateCmd.Parameters.Add("@unitavailable1",SqlDbType.Int).Value = unitavailable1;
     sqlupdateCmd.ExecuteNonQuery();
}

edited Nov 23, 2014 at 8:58

answered Nov 23, 2014 at 6:18

Rahul Singh

21.8k6 gold badges43 silver badges61 bronze badges

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

14 Comments

ben mah Over a year ago

ah...wrong message for the else... but if the value of textbox is bigger than the value of data in database, it should have the message appear, but it is not working too

Rahul Singh Over a year ago

@benmah - Have you debugged the code? It is actually going to that else block? Also, have updated the correct alert box syntax.

ben mah Over a year ago

yup, i removed the message for else, but the code did not work either. no changes have been done to the database. or is it the part where i pass data from database has some problems?

Rahul Singh Over a year ago

@benmah - You are the best person to answer that! Put a breakpoint and debug and see if you are getting correct values from DB and if your else block is actually getting executed.

ben mah Over a year ago

the alert message is working ald now but when i add the sqlupdateCmd.ExecuteNonQuery() after the update statement, when i debug an error came out, it says that There is already an open DataReader associated with this Command. which should i close 1st?

|

Banusundar Arumugam · Accepted Answer · 2014-11-23 07:01:05Z

0

I hope u have to make two changes to the code.

U should add ExecuteNonQuery after the update statement.
Also the variable unitavailable1, which is the new available unit had been provided within the quotes..so probably the value is not substituted. Try to close the quotes before that and open it after the variable and use '+' to concatenate them like this

"UPDATE StockDetails Set UnitAvailable = "+unitavailable1+" WHERE StockID = 3".

Try this...Hope this will work...

answered Nov 23, 2014 at 7:01

Banusundar Arumugam

2513 silver badges9 bronze badges

1 Comment

ben mah Over a year ago

after i add the execute non query to the code the error appear and said that There is already an open DataReader associated with this Command. which must be closed first?

Collectives™ on Stack Overflow

comparing data of textbox with database and update database with the new value

2 Answers 2

14 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related