locked
unable to update & insert RRS feed

  • Question

  • User-494664520 posted

    Unable to update & insert the checkbox that i select.

    This method I use isit correct?or had any method that more suitable?

     if (CheckBoxSub8XS.Checked == true && txtSub8XS.Text != "")
                {
                    using (SqlConnection conStrstock8 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
                    {
                        string sqlupStockselect8 = "Select * FROM ProductStock WHERE ProductID=@pid AND ProductColorType=@protType AND ProductSize=@size";
    
                        using (SqlCommand cmdupstock8 = new SqlCommand(sqlupStockselect8, conStrstock8))
                        {
                            cmdupstock8.Parameters.AddWithValue("@pid", Id);
                            cmdupstock8.Parameters.AddWithValue("@protType", "Type8");
                            cmdupstock8.Parameters.AddWithValue("@size", " XS");
    
                            conStrstock8.Open();
                            SqlDataReader myReader = cmdupstock8.ExecuteReader();
                            
                            while (myReader.Read())
                            {
                                if (myReader.HasRows)
                                {
                                    using (SqlConnection conStrstock81 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
                                    {
    
                                        string sqlupStock81 = "UPDATE ProductStock SET ProductQty=@qty1 WHERE ProductID=@pid AND ProductColorType=@protType AND ProductSize=@size";
    
                                        using (SqlCommand cmdupstock81 = new SqlCommand(sqlupStock81, conStrstock81))
                                        {
                                            cmdupstock81.Parameters.AddWithValue("@size", " XS");
                                            cmdupstock81.Parameters.AddWithValue("@qty1", txtSub8S.Text);
                                            cmdupstock81.Parameters.AddWithValue("@protType", "Type8");
                                            cmdupstock81.Parameters.AddWithValue("@pid", Id);
    
                                            conStrstock81.Open();
                                            cmdupstock81.ExecuteNonQuery();
                                            conStrstock81.Close();
    
                                        }
                                    }
                                }
                                else
                                {
                                    using (SqlConnection conINstock8 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
                                    {
    
                                        string sqlINStock8 = "INSERT INTO ProductStock(ProductID,ProductColorType,ProductSize,ProductQty,ProductSizeEntre,ProductSizeQty) VALUES(@pid,@protType,@size,@qty,@sizeEnt,@sizeQty)";
    
                                        using (SqlCommand cmdINstock8 = new SqlCommand(sqlINStock8, conINstock8))
                                        {
                                            cmdINstock8.Parameters.AddWithValue("@pid", Id);
                                            cmdINstock8.Parameters.AddWithValue("@protType", "Type8");
                                            cmdINstock8.Parameters.AddWithValue("@size", " XS");
                                            cmdINstock8.Parameters.AddWithValue("@qty", txtSub8XS.Text);
                                            cmdINstock8.Parameters.AddWithValue("@sizeEnt", " XS");
                                            cmdINstock8.Parameters.AddWithValue("@sizeQty", txtSub8XS.Text);
    
                                            conINstock8.Open();
                                            cmdINstock8.ExecuteNonQuery();
                                            conINstock8.Close();
    
                                        }
                                    }
                                }
                            }
                            conStrstock8.Close();
                        }
                    }
                }

    Sunday, December 8, 2013 8:42 AM

Answers

  • User-1199946673 posted

    You're using a datareader to detemine if records exists, and if they exists, you update these records, and if not, you insert a record.. A much better approach would be to just execute the Update statement, without checking if the records exists or not. When you execute this query using ExecuteNonQuery, this method will return the number of records affected by the statement. If it returns 0, it means that no records where effected so obvioulsly they don't exist, so in that case, you should execute the Insert statement. You can use the same connection and command for both SQL statements.

    if (CheckBoxSub8XS.Checked == true && !string.IsNullOrEmpty(txtSub8XS.Text)) {
    	using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())) {
    		string sql = "UPDATE ProductStock SET ProductQty=@qty WHERE ProductID=@pid AND ProductColorType=@protType AND ProductSize=@size";
    		using (SqlCommand cmd = new SqlCommand(sql, conn)) {
    			cmd.Parameters.AddWithValue("@qty", txtSub8S.Text);
    			cmd.Parameters.AddWithValue("@pid", ID);
    			cmd.Parameters.AddWithValue("@protType", "Type8");
    			cmd.Parameters.AddWithValue("@size", " XS");
    			conn.Open();
    			if (cmd.ExecuteNonQuery() == 0) {
    				//No records affected, insert new record
    				cmd.CommandText = "INSERT INTO ProductStock (ProductID, ProductColorType, ProductSize, ProductQty, ProductSizeEntre, ProductSizeQty) VALUES (@pid, @protType, @size, @qty, @sizeEnt, @sizeQty)";
    				//the first 4 parameters are already added to the parameter collection
    				cmd.Parameters.AddWithValue("@sizeEnt", " XS");
    				cmd.Parameters.AddWithValue("@sizeQty", txtSub8XS.Text);
    				cmd.ExecuteNonQuery();
    			}
    		}
    	}
    }

    And next time, please don't open 3 threads for the same (related) problem? Also, since you're using SQL Server, you should post your questions in antoher forum:

    http://forums.asp.net/1226.aspx/1?SQL+Server+SQL+Server+Express+and+SQL+Compact+Edition

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 8, 2013 2:57 PM

All replies

  • User-1509636757 posted

    As we can see that the code involves multiple db trips to select, update and insert.. I would recommend to write stored procedure and send required parameters to it will be better way.

    hope it helps./.

    Sunday, December 8, 2013 11:35 AM
  • User-1199946673 posted

    You're using a datareader to detemine if records exists, and if they exists, you update these records, and if not, you insert a record.. A much better approach would be to just execute the Update statement, without checking if the records exists or not. When you execute this query using ExecuteNonQuery, this method will return the number of records affected by the statement. If it returns 0, it means that no records where effected so obvioulsly they don't exist, so in that case, you should execute the Insert statement. You can use the same connection and command for both SQL statements.

    if (CheckBoxSub8XS.Checked == true && !string.IsNullOrEmpty(txtSub8XS.Text)) {
    	using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())) {
    		string sql = "UPDATE ProductStock SET ProductQty=@qty WHERE ProductID=@pid AND ProductColorType=@protType AND ProductSize=@size";
    		using (SqlCommand cmd = new SqlCommand(sql, conn)) {
    			cmd.Parameters.AddWithValue("@qty", txtSub8S.Text);
    			cmd.Parameters.AddWithValue("@pid", ID);
    			cmd.Parameters.AddWithValue("@protType", "Type8");
    			cmd.Parameters.AddWithValue("@size", " XS");
    			conn.Open();
    			if (cmd.ExecuteNonQuery() == 0) {
    				//No records affected, insert new record
    				cmd.CommandText = "INSERT INTO ProductStock (ProductID, ProductColorType, ProductSize, ProductQty, ProductSizeEntre, ProductSizeQty) VALUES (@pid, @protType, @size, @qty, @sizeEnt, @sizeQty)";
    				//the first 4 parameters are already added to the parameter collection
    				cmd.Parameters.AddWithValue("@sizeEnt", " XS");
    				cmd.Parameters.AddWithValue("@sizeQty", txtSub8XS.Text);
    				cmd.ExecuteNonQuery();
    			}
    		}
    	}
    }

    And next time, please don't open 3 threads for the same (related) problem? Also, since you're using SQL Server, you should post your questions in antoher forum:

    http://forums.asp.net/1226.aspx/1?SQL+Server+SQL+Server+Express+and+SQL+Compact+Edition

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 8, 2013 2:57 PM
  • User-494664520 posted

    sorry for late reply..may me my time zone is different with you all,so im now just trying the solution that your provide to me, is usable i will mark as answered

    Thx you anyway

    Monday, December 9, 2013 7:23 AM
  • User-494664520 posted

    Sorry for interrupt again, it able to update succesful. But i key in "50" in the textfield, it just able to store "10" and when i update, it not able to update also..

    Monday, December 9, 2013 7:57 AM
  • User-494664520 posted

    i solve the problem already..

    thx for the help ya

    Monday, December 9, 2013 8:25 AM