Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initialized

Question

User-2074858223 posted

Hi please am trying to modify this code to just update data in two columns, actually this code use to insert into product table and at the same time cheak if the item doesnt exist then insert or else update. But this time i just want to update only. Any help to modify this?

  protected void btnSave_Click(object sender, EventArgs e)
    {
         string productcategory = ddlproductcategory.SelectedItem.Text.Trim();
      //string color = ddlcolor.SelectedItem.Text.Trim();
         int quantity = Convert.ToInt32(!string.IsNullOrEmpty(txtamount.Text.Trim()) ? txtamount.Text.Trim() : "0");
      //  decimal price = Convert.ToDecimal(!string.IsNullOrEmpty(txtPrice.Text.Trim()) ? txtPrice.Text.Trim() : "0");
        string query = "";
        int itemQuantity = IsItemExistWithQuantity(productcategory);
        if (itemQuantity > 0)
        {
            int updatedQuantity = itemQuantity + quantity;
            //query = "UPDATE Products SET Quantity = " + updatedQuantity + " WHERE ProductName = '" + productname + "'";

            query = "UPDATE Sells SET AmountPaid = @AmountPaid  WHERE PaymentStatus  = @PaymentStatus ";
            //InertProduct(query, updatedQuantity, productcategory);
        }
        else
        {
            //query = "INSERT INTO Products VALUES('" + productname + "',' " + productcategory + " '," + quantity + "," + price + ") ";

         //   query = "INSERT INTO Products VALUES( @ProductCategory,@Quantity_PerSqM,@Date) ";
            UpdateProduct(query, productcategory, quantity);
        }
        // Insert or update item.
        // InsertUpdateStock(query);
        Bindproductcategory();
    }

    public int IsItemExistWithQuantity(string productcategory)
    {
        int quantity = 0;
        using (SqlConnection con = new SqlConnection(str))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT AmountPaid FROM Sells WHERE PaymentStatus = @PaymentStatus", con))
            {
                con.Open();
                cmd.Parameters.AddWithValue("@PaymentStatus ", productcategory);
                object obj = cmd.ExecuteScalar();
                con.Close();
                if (obj != null)
                {
                    quantity = Convert.ToInt32(obj);
                }
            }
        }

        return quantity;
    }

    public void InsertUpdateStock(string query)
    {
        using (SqlConnection con = new SqlConnection(str))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

   
    public void UpdateProduct(string query, string productcategory, int quantity)
    {
        using (SqlConnection con = new SqlConnection(str))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                con.Open();


                cmd.Parameters.AddWithValue("@PaymentStatus", productcategory);
                cmd.Parameters.AddWithValue("@AmountPaid", quantity);
               // cmd.Parameters.AddWithValue("@Color", color);
                cmd.Parameters.AddWithValue("@Date", DateTime.Now);
                cmd.ExecuteNonQuery();
                con.Close();
                string message = string.Empty;
                {
                    lblMessage2.Visible = true;
                    lblMessage2.Text = "Data Submitted Successfully";
                }
                ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Data Submitted Successfully ... !!')</script>", false);
            }
        }
    }
    }

Server Error in '/' Application.
ExecuteNonQuery: CommandText property has not been initialized
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized

Source Error:


Line 225:               // cmd.Parameters.AddWithValue("@Color", color);
Line 226:                cmd.Parameters.AddWithValue("@Date", DateTime.Now);
Line 227:                cmd.ExecuteNonQuery();
Line 228:                con.Close();
Line 229:                string message = string.Empty;

Answer 1

User475983607 posted

The error indicates the command property is not initialized and this block of code is responsible for assigning the SQL query string.  

        string query = "";
        int itemQuantity = IsItemExistWithQuantity(productcategory);
        if (itemQuantity > 0)
        {
            int updatedQuantity = itemQuantity + quantity;
            //query = "UPDATE Products SET Quantity = " + updatedQuantity + " WHERE ProductName = '" + productname + "'";

            query = "UPDATE Sells SET AmountPaid = @AmountPaid  WHERE PaymentStatus  = @PaymentStatus ";
            //InertProduct(query, updatedQuantity, productcategory);
        }
        else
        {
            //query = "INSERT INTO Products VALUES('" + productname + "',' " + productcategory + " '," + quantity + "," + price + ") ";

         //   query = "INSERT INTO Products VALUES( @ProductCategory,@Quantity_PerSqM,@Date) ";
            UpdateProduct(query, productcategory, quantity);
        }

itemQuantity is less than or equal to zero and means the "query" variable is an empty string.  You need to assign a SQL string to "query" when itemQuantity is less than or equal to zero.

Visual Studio comes with a debugger that allows you to set a breakpoint and single step over your code logic.  It is very helpful for finding basic bugs like the one above. The following link explains how to get started with the debugger.

https://msdn.microsoft.com/en-us/library/y740d9d3.aspx

Answer 2

User-832373396 posted

<g class="gr_ gr_8 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="8" data-gr-id="8">Hi</g> <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="5" data-gr-id="5">micah</g>,

But this time <g class="gr_ gr_15 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="15" data-gr-id="15">i</g> just want to update only. Any help to modify this?

Sir, based on your code, please modify a bit, if itemQuantity>0, then update the record, not insert because when <g class="gr_ gr_772 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="772" data-gr-id="772">only</g> itemQuantity>0, it means this record exists :

 protected void btnSave_Click(object sender, EventArgs e)
        {
            string productcategory = ddlproductcategory.SelectedItem.Text.Trim();
            //string color = ddlcolor.SelectedItem.Text.Trim();
            int quantity = Convert.ToInt32(!string.IsNullOrEmpty(txtamount.Text.Trim()) ? txtamount.Text.Trim() : "0");
            //  decimal price = Convert.ToDecimal(!string.IsNullOrEmpty(txtPrice.Text.Trim()) ? txtPrice.Text.Trim() : "0");
            string query = "";
            int itemQuantity = IsItemExistWithQuantity(productcategory);
            if (itemQuantity > 0)
            {
                int updatedQuantity = itemQuantity + quantity;
                //query = "UPDATE Products SET Quantity = " + updatedQuantity + " WHERE ProductName = '" + productname + "'";

                query = "UPDATE Sells SET AmountPaid = @AmountPaid  WHERE PaymentStatus  = @PaymentStatus ";
                //InertProduct(query, updatedQuantity, productcategory);

                UpdateProduct(query, productcategory, quantity);// please note this line code,if itemQuantity>0,then update the record .

            }
            //else
            //{
            //    //query = "INSERT INTO Products VALUES('" + productname + "',' " + productcategory + " '," + quantity + "," + price + ") ";

            //    //   query = "INSERT INTO Products VALUES( @ProductCategory,@Quantity_PerSqM,@Date) ";
            //   // UpdateProduct(query, productcategory, quantity);
            //    InertProduct(query, updatedQuantity, productcategory);
            //}
            // Insert or update item.
            // InsertUpdateStock(query);
           // Bindproductcategory();
        }

It should work :)

With regards, Angelia Jolie

Answer 3

User-2074858223 posted

i tried it but its not updating

Answer 4

User475983607 posted

i tried it but its not updating

This response is to vague and not very helpful. 

Has the error gone away?   Is there any way, you can post the updated code and error?

Answer 5

User-2074858223 posted

yes here, am trying to update a record using AmountPaid ,PaymentStatus and table ID

  protected void btnSave_Click(object sender, EventArgs e)
    {


        string productcategory = ddlproductcategory.SelectedItem.Text.Trim();
            //string color = ddlcolor.SelectedItem.Text.Trim();
            int quantity = Convert.ToInt32(!string.IsNullOrEmpty(txtamount.Text.Trim()) ? txtamount.Text.Trim() : "0");
            //  decimal price = Convert.ToDecimal(!string.IsNullOrEmpty(txtPrice.Text.Trim()) ? txtPrice.Text.Trim() : "0");
            string query = "";
            int itemQuantity = IsItemExistWithQuantity(productcategory);
            if (itemQuantity > 0)
            {
                int updatedQuantity = itemQuantity + quantity;
                //query = "UPDATE Products SET Quantity = " + updatedQuantity + " WHERE ProductName = '" + productname + "'";

                query = "UPDATE Sells SET AmountPaid = @AmountPaid  WHERE PaymentStatus  = @PaymentStatus  AND ID=@ID";
                //InertProduct(query, updatedQuantity, productcategory);

                UpdateProduct(query, productcategory, quantity, id);// please note this line code,if itemQuantity>0,then update the record .

            }
            //else
            //{
            //    //query = "INSERT INTO Products VALUES('" + productname + "',' " + productcategory + " '," + quantity + "," + price + ") ";

            //    //   query = "INSERT INTO Products VALUES( @ProductCategory,@Quantity_PerSqM,@Date) ";
            //   // UpdateProduct(query, productcategory, quantity);
            //    InertProduct(query, updatedQuantity, productcategory);
            //}
            // Insert or update item.
            // InsertUpdateStock(query);
           // Bindproductcategory();
        }
       
    public int IsItemExistWithQuantity(string productcategory)
    {
        int quantity = 0;
        using (SqlConnection con = new SqlConnection(str))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT AmountPaid FROM Sells WHERE PaymentStatus = @PaymentStatus", con))
            {
                con.Open();
                cmd.Parameters.AddWithValue("@PaymentStatus ", productcategory);
                object obj = cmd.ExecuteScalar();
                con.Close();
                if (obj != null)
                {
                    quantity = Convert.ToInt32(obj);
                }
            }
        }

        return quantity;
    }

    public void InsertUpdateStock(string query)
    {
        using (SqlConnection con = new SqlConnection(str))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

   
    public void UpdateProduct(string query, string productcategory, int quantity, int id)
    {
        using (SqlConnection con = new SqlConnection(str))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                con.Open();


                cmd.Parameters.AddWithValue("@PaymentStatus", productcategory);
                cmd.Parameters.AddWithValue("@AmountPaid", quantity);
               cmd.Parameters.AddWithValue("@ID", id);
                cmd.Parameters.AddWithValue("@Date", DateTime.Now);
                cmd.ExecuteNonQuery();
                con.Close();
                string message = string.Empty;
                {
                    lblMessage2.Visible = true;
                    lblMessage2.Text = "Data Submitted Successfully";
                }
                ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Data Submitted Successfully ... !!')</script>", false);
            }
        }
    }
    }

Answer 6

User475983607 posted

For crying out loud!  Has the error gone away and now you are facing a new issue?

You have to realize that we cannot see your code, DB schema, or look at the data.  Have you verified, by setting a breakpoint, that the following produces a value greater than zero?

int itemQuantity = IsItemExistWithQuantity(productcategory);

The UPDATE script has a WHERE clause that accepts a product category and uses it to filter by PaymentStatus which does not make sense.

query = "UPDATE Sells SET AmountPaid = @AmountPaid  WHERE PaymentStatus  = @PaymentStatus ";

cmd.Parameters.AddWithValue("@PaymentStatus ", productcategory);

A date parameter is added to the update but there is no date parameter in the SQL.  Is this a typo or are you not showing the current running code base?

cmd.Parameters.AddWithValue("@Date", DateTime.Now);

Please take a moment to review that your logic is correct.  Also please start using the Visual Studio debugger to single-step through the code and clearly explain the problem you are having. The debugger will help find basic logical errors like posted above.  Please learn how to use the debugger.

https://msdn.microsoft.com/en-us/library/y740d9d3.aspx

Answer 7

User-2074858223 posted

i have modified the code to what am looking for but the i get this error

Server Error in '/' Application.
The multi-part identifier "System.Web.UI.WebControls.TextBox" could not be bound.
The multi-part identifier "System.Web.UI.WebControls.DropDownList" could not be bound.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The multi-part identifier "System.Web.UI.WebControls.TextBox" could not be bound.
The multi-part identifier "System.Web.UI.WebControls.DropDownList" could not be bound.

Source Error:


Line 181:                                                cmd.CommandText = "UPDATE Sells SET TotalAmount = " + txtamount + ", PaymentStatus = " + ddlpaymentstatus + " WHERE SellId = '" + txtitemid + "'";
Line 182:                                                con.Open();
Line 183:                                                inserted = cmd.ExecuteNonQuery();
Line 184:                                                con.Close();
Line 185:                                            }

      ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('No of Quantity entered is not available in Stock')", true);
                                    }
                                    if (inserted > 0)
                                    {
                                        //int updatedStock = availableQuantity - quantity;
                                        using (SqlConnection con = new SqlConnection())
                                        {
                                            con.ConnectionString = str;
                                            using (SqlCommand cmd = new SqlCommand())
                                            {
                                                cmd.CommandType = CommandType.Text;
                                                cmd.Connection = con;
                                                cmd.CommandText = "UPDATE Sells SET TotalAmount = " + txtamount + ", PaymentStatus = " + ddlpaymentstatus + " WHERE SellId = '" + txtitemid + "'";
                                                con.Open();
                                                inserted = cmd.ExecuteNonQuery();
                                                con.Close();
                                            }
                                        }
                                    }
                                    BindProductsSells();
                                }

Answer 8

User475983607 posted

It seems as though you are designing and coding at the same time.   The code snippet shows half thought out implementations which makes it near impossible to figure out the intention.  You've gone from parameter queries to dynamic queries.  The SQL is different from the original and there is no indication  where the following variables come from; txtamount, ddlpaymentstatus , or txtitemid.  I assume they are references to server controls given the error.

Consider coming up with a design first. 

• Step 1 do this
• Step2 to do this
• step 3 do this 

Then write code that accomplishes each step.  This will make debugging your code much easier plus it will be clearer for the forum members to provide assistance. 

Answer 9

User-2074858223 posted

Please let me forget the previous problems that i have posted here, the idea of all these is to just update the AmountPaied, PaymentStartus where table ID is.

Example this is Sells table

SellId  |    UserName    |   CustomerName   |  ProductCategory  | PaymentStatus | AmountPaied

-------------------------------------------------------------------------------------------------------------------

1            me22                   MYname               Wears                deposit                 $100

--------------------------------------------------------------------------------------------------------------------

This is payment table

Id  |    UserName    | SellId |  CustomerName   | PaymentStatus | AmountPaied

-------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------

So now all i want to do is to update Column PaymentStaus and AmountPaid in table Sells while i insert Username, Customername, paymentstatus and amountpaid into payment table

 <div class="clearfix"></div>
                  <h3>
                                                 Update Record
                                             </h3>
                  <div class="well col-lg-6">
                        <div class="clearfix"></div>
                                          <p class="">

                                              <asp:DropDownList ID="ddlcustomername" runat="server"
                      CssClass="btn btn-default dropdown-toggle" Width="100%" >
                   
                                                </asp:DropDownList>
                                               </p>
                      <div class="clearfix"></div>
                                          <p class="">

                                             <asp:TextBox ID="txtitemid" runat="server" class="form-control" placeholder="Item ID" />
                                              
                                               </p>

                    
                                              <div class="clearfix"></div>
                                          <p class="">

                                              <asp:DropDownList ID="ddlpaymentstatus" runat="server" CssClass="btn btn-default dropdown-toggle" Width="100%">
                <asp:ListItem>--Select payment status--</asp:ListItem>
                  <asp:ListItem>Full payment</asp:ListItem>
                 <asp:ListItem>Credit Order</asp:ListItem>
                 <asp:ListItem>Deposit</asp:ListItem>
                                                </asp:DropDownList>
                                               </p>
                                              <div class="clearfix"></div>
                                             <p>
                                                   <asp:TextBox ID="txtamount" runat="server" CssClass="form-control"  placeholder="Amount"></asp:TextBox>
                                             </p>
                                               <div class="clearfix"></div>
                                              <p class=" pull-right">
                        <asp:Button ID="btnSave" runat="server" CssClass="btn btn-info btn-circle" Text="Save Record" Height="50px" Width="120px" Font-Bold="True" OnClick="btnSave_Click"/>
                                                      
                                                       
                                                       
                                                       <asp:Label ID="Label6" runat="server"></asp:Label>
                                                   </p>

                                             </div>
                    
                  </div>


          
            
            
        </div>
        <div class="" >

            

            


            
        </div>
  
    <!--/#content.col-md-0-->

   
        <div class="row" style="width: 100%">

            <!--/span-->

            
        </div>
        <!--/span-->
    



        </div>


        
     
    </div>
</asp:Content>