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;
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.
But this time i 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 only 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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
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 ";
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?
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.
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();
}
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.
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.
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
Member
227 Points
1070 Posts
Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initialized
Nov 16, 2017 03:34 PM|micah2012|LINK
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?
All-Star
52201 Points
23277 Posts
Re: Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initializ...
Nov 16, 2017 03:43 PM|mgebhard|LINK
The error indicates the command property is not initialized and this block of code is responsible for assigning the SQL query string.
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
Contributor
5290 Points
2307 Posts
Re: Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initializ...
Nov 17, 2017 06:17 AM|AngelinaJolie|LINK
Hi micah,
Sir, based on your code, please modify a bit, if itemQuantity>0, then update the record, not insert because when only itemQuantity>0, it means this record exists :
It should work :)
With regards, Angelia Jolie
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
227 Points
1070 Posts
Re: Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initializ...
Nov 20, 2017 01:12 PM|micah2012|LINK
i tried it but its not updating
All-Star
52201 Points
23277 Posts
Re: Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initializ...
Nov 20, 2017 02:14 PM|mgebhard|LINK
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?
Member
227 Points
1070 Posts
Re: Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initializ...
Nov 20, 2017 02:26 PM|micah2012|LINK
yes here, am trying to update a record using AmountPaid ,PaymentStatus and table ID
All-Star
52201 Points
23277 Posts
Re: Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initializ...
Nov 20, 2017 03:48 PM|mgebhard|LINK
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?
The UPDATE script has a WHERE clause that accepts a product category and uses it to filter by PaymentStatus which does not make sense.
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?
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
Member
227 Points
1070 Posts
Re: Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initializ...
Nov 20, 2017 07:18 PM|micah2012|LINK
i have modified the code to what am looking for but the i get this error
All-Star
52201 Points
23277 Posts
Re: Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initializ...
Nov 20, 2017 07:37 PM|mgebhard|LINK
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.
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.
Member
227 Points
1070 Posts
Re: Server Error in '/' Application. ExecuteNonQuery: CommandText property has not been initializ...
Nov 20, 2017 09:24 PM|micah2012|LINK
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