specifying when to update the database depending on variable

Carol Cashman

hey guys , below i have a button and once this button is pressed, it will update the table with the values that are in a textbox.

but if p <= 0 i dont want to order to be processed, is there any way i can restrict when the databse is updated and these statements are executed to only when the stock is greater than 0

//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 take away the values entered in the textboxes from the stock count currently in the database table a rows in the database

// http://www.c-sharpcorner.com/forums/error-when-trying-reduce-a-certain-number-from-the-database

SqlCommand cmd = new SqlCommand("UPDATE StockTable set AmtOfStickers= AmtOfStickers - " + this.txtStickers.Text + ";", con);

SqlCommand cmd1 = new SqlCommand("UPDATE StockTable set AmtOfLids= AmtOfLids - " + this.txtLids.Text + ";", con);

SqlCommand cmd2 = new SqlCommand("UPDATE StockTable set AmtOfSJars= AmtOfSJars - " + this.txtSmallJars.Text + ";", con);

SqlCommand cmd3 = new SqlCommand("UPDATE StockTable set AmtOfLJars= AmtOfLJars - " + this.txtLargeJars.Text + ";", con);

SqlCommand cmd4 = new SqlCommand("INSERT INTO OrderDetails(OrderDate, CustomerID, LidNo, StickerNo, LJarNo, SJarNo) values('" + this.txtDate.Text + "','" + this.txtId.Text + "','"

+ this.txtLids.Text + "','" + this.txtStickers.Text + "','" + this.txtLargeJars.Text + "','" + this.txtSmallJars.Text + "')", con);

con.Open();

cmd.ExecuteNonQuery();

cmd1.ExecuteNonQuery();

cmd2.ExecuteNonQuery();

cmd3.ExecuteNonQuery();

cmd4.ExecuteNonQuery();

SqlCommand cmd5 = new SqlCommand();

cmd5.CommandText = "Select * from [StockTable]";

cmd5.Connection = con;

SqlDataAdapter da = new SqlDataAdapter(cmd5);

DataSet ds = new DataSet();

da.Fill(ds);

Label12.Text = ds.Tables[0].Rows[0]["AmtOfStickers"] + "";

Label13.Text = ds.Tables[0].Rows[0]["AmtOfSJars"] + "";

Label14.Text = ds.Tables[0].Rows[0]["AmtOfLJars"] + "";

Label15.Text = ds.Tables[0].Rows[0]["AmtOfLids"] + "";

int p = Convert.ToInt32(Label12.Text);

int p1 = Convert.ToInt32(Label13.Text);

int p2 = Convert.ToInt32(Label14.Text);

int p3 = Convert.ToInt32(Label15.Text);

if (p <= 0)

{

smtp.Text = "smtp.gmail.com";

username.Text = "[email protected]";

password.Text = "****";

from.Text = "[email protected]";

to.Text ="[email protected]";

subject.Text = "Stock is Low";

body.Text = "Hi Jerry, your sticker stock is 0, please restock soon to continue taking orders. Kay Oates.";

MailMessage mail = new MailMessage(from.Text, to.Text, subject.Text, body.Text);

SmtpClient Client = new SmtpClient(smtp.Text);

Client.Port = 587;

Client.Credentials = new System.Net.NetworkCredential(username.Text, password.Text);

Client.EnableSsl = true;

Client.Send(mail);

//https://stackoverflow.com/questions/15196381/display-messagebox-in-asp-net messagebox dispalying mail sent

Response.Write("");

}

else if (p >= 1 && p <= 500)

{

smtp.Text = "smtp.gmail.com";

username.Text = "[email protected]";

password.Text = "***";

from.Text = "[email protected]";

to.Text = "[email protected]";

subject.Text = "Stock is Low";

body.Text = "Hi Jerry
your small jar stock is low,
please restock soon to continue taking orders.
Kay Oates.";

MailMessage mail = new MailMessage(from.Text, to.Text, subject.Text, body.Text);

SmtpClient Client = new SmtpClient(smtp.Text);

Client.Port = 587;

Client.Credentials = new System.Net.NetworkCredential(username.Text, password.Text);

Client.EnableSsl = true;

Client.Send(mail);

//https://stackoverflow.com/questions/15196381/display-messagebox-in-asp-net messagebox dispalying mail sent

Response.Write("");

Server.Transfer("orderDisplay.aspx");

}

thank you!!!

2 Replies

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

Madhanmohan Devarajan8y ago
Accepted answer
Hi Carol,

Coding seems to be fine, but you need to rearrange few statement in your code. Instead of using the update commands before checking the p (stock value), you should first retrieve the values from Database to check whether p (stock value) is less than 0 or between 1 to 500 and then use the update statement as shown below.
// Database connection strings
string cs = System.Configuration.ConfigurationManager.ConnectionStrings
["RegistrationConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(cs);
// Command to fetch the stock details from SQL
SqlCommand cmd5 = new SqlCommand();
cmd5.CommandText = "Select * from [StockTable]";
cmd5.Connection = con;
SqlDataAdapter da = new SqlDataAdapter(cmd5);
DataSet ds = new DataSet();
da.Fill(ds);
int p = Convert.ToInt32(ds.Tables[0].Rows[0]["AmtOfStickers"]) ;
// Checking the stock, whether it is low. If low then do not save record in Database, just send email.
if (p <= 0)
{
smtp.Text = "smtp.gmail.com";
username.Text = "[email protected]";
password.Text = "****";
from.Text = "[email protected]";
to.Text ="[email protected]";
subject.Text = "Stock is Low";
body.Text = "Hi Jerry, your sticker stock is 0, please restock soon to continue taking orders. Kay Oates.";
MailMessage mail = new MailMessage(from.Text, to.Text, subject.Text, body.Text);
SmtpClient Client = new SmtpClient(smtp.Text);
Client.Port = 587;
Client.Credentials = new System.Net.NetworkCredential(username.Text, password.Text);
Client.EnableSsl = true;
Client.Send(mail);
//https://stackoverflow.com/questions/15196381/display-messagebox-in-asp-net messagebox dispalying mail
sent
Response.Write("");
}
else if (p >= 1 && p <= 500)
{
//Send email
smtp.Text = "smtp.gmail.com";
username.Text = "[email protected]";
password.Text = "***";
from.Text = "[email protected]";
to.Text = "[email protected]";
subject.Text = "Stock is Low";
body.Text = "Hi Jerry
your small jar stock is low,
please restock soon to continue taking orders.
Kay Oates.";
MailMessage mail = new MailMessage(from.Text, to.Text, subject.Text, body.Text);
SmtpClient Client = new SmtpClient(smtp.Text);
Client.Port = 587;
Client.Credentials = new System.Net.NetworkCredential(username.Text, password.Text);
Client.EnableSsl = true;
Client.Send(mail);
//https://stackoverflow.com/questions/15196381/display-messagebox-in-asp-net messagebox dispalying mail
sent
Response.Write("");
Server.Transfer("orderDisplay.aspx");
}
else{
// Update textbox values in Database
SqlCommand cmd = new SqlCommand("UPDATE StockTable set AmtOfStickers= AmtOfStickers - " +
this.txtStickers.Text + ";", con);
SqlCommand cmd1 = new SqlCommand("UPDATE StockTable set AmtOfLids= AmtOfLids - " + this.txtLids.Text + ";",
con);
SqlCommand cmd2 = new SqlCommand("UPDATE StockTable set AmtOfSJars= AmtOfSJars - " + this.txtSmallJars.Text
+ ";", con);
SqlCommand cmd3 = new SqlCommand("UPDATE StockTable set AmtOfLJars= AmtOfLJars - " + this.txtLargeJars.Text
+ ";", con);
SqlCommand cmd4 = new SqlCommand("INSERT INTO OrderDetails(OrderDate, CustomerID, LidNo, StickerNo, LJarNo,
SJarNo) values('" + this.txtDate.Text + "','" + this.txtId.Text + "','"
+ this.txtLids.Text + "','" + this.txtStickers.Text + "','" + this.txtLargeJars.Text + "','" +
this.txtSmallJars.Text + "')", con);
con.Open();
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cmd3.ExecuteNonQuery();
cmd4.ExecuteNonQuery();
}
Amit Gupta8y ago
Hey

Before answering to your question, it would been an humble advise to be specific to the issue you are asking, highlight some issue of area instead of poating all your code and use code highlighter for posting code.

Coming to your question, if you want to update when p>0, then write your code before entering to the button click block.

Instead 0f writing so many SqlCommand is not a good approach, you should go for some SP.

However, if not then you can execute all the statement in 1 query by using semicolon in between your queries like

Update table set value =1; update table2 set value =5

This would reduce code complexity and easy to maimtain.

Moreover, you can create a DBHelper class that would reduce your code duplicacy, you can also create a function for sending email instead writing 2 times the same code.

I also want to suggest to put proper naming convention for controls.

Hope you take this is in a good spirit

Good Luck