p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   General .NET (http://p2p.wrox.com/forumdisplay.php?f=122)
-   -   automatic alert message by detecting sql command (http://p2p.wrox.com/showthread.php?t=92880)

meme May 14th, 2014 06:13 AM

automatic alert message by detecting sql command
 
hii guys, please help me to solve my problem.
actually im doing inventory system where one of its function is to automatically detect the stock/product that has reach its limit by using alert message.
for this case, i have product table that consist pid, pname, vendorid, productamount, lead time and safetystockamount.
im doing it using c#.net (visual studio 2010). im using sql server for database.

in my page, i actually will show the product details by using gridview tool in visual studio by referring to product table in my database and when the user first come into the page, the alert message will come out. This alert message only will pop out if there is one or more product that reached its stock limit. If all the product amount are in more than safetyamount then this alert message will not coming up.

my sql command for this function is :
<code>
select case when (P_Amount <= SafetyStockamount)
then 'Limit stock has been reached'
end as Alert
from Product </code>

when i execute this statement in my sql server management studio then the result will show the list of product stock level condition. if the product amount is less than safetystockamount then it will show 'limit stock has been reached' while if the product amount is in good condition then it will be 'null'.

For the c# code i also tried to combine both alert message code and sql command but as i can see the program only execute the alert message code so everytime when i open the page then the alert message will pop up. It will happen everytime which means even though one of the product amount is still in good condition (> then safetystockamount), the pop up alert also will come out. @@

below is my c# code :
<code>if (Page.IsPostBack)
{
SqlConnection connection = new SqlConnection("Data Source=MELINA_YEO-VAIO; Initial Catalog=InventorySystem; Integrated Security=True");
connection.Open();
SqlCommand abc = new SqlCommand("Select case when (P_Amount <= SafetyStockamount) then 'Limit stock has been reached' end as Alert from Product");
Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\"> alert('Limit stock has been reached');</script>");
} </code>


my javascript code:
<script type="text/javascript">
alert('Limit stock has been reached!');
</script>

please give me solution for this problem. thanks in advance :)[8)]

Imar May 14th, 2014 06:22 AM

Hi there,

You're never executing your SQL command or deal with the results. All you do is declare the command, but the SQL statement will never be fired against the database.

Take a look at this tutorial to see how to execute the command, and deal with its return value: http://www.csharp-station.com/Tutori...otNet/lesson03

Cheers.

Imar

meme May 14th, 2014 07:55 AM

Quote:

Originally Posted by Imar (Post 297575)
Hi there,

You're never executing your SQL command or deal with the results. All you do is declare the command, but the SQL statement will never be fired against the database.

Take a look at this tutorial to see how to execute the command, and deal with its return value: http://www.csharp-station.com/Tutori...otNet/lesson03

Cheers.

Imar

imar, i tried this already, but still not working [B)].
this is my code

<code> Page page = HttpContext.Current.Handler as Page;
if
(page != null)
{
string message = "Limit stock has been reached!";
ScriptManager.RegisterStartupScript(page, page.GetType(), "err_msg", "alert('" + message + "');", true);
}
}



class SqlCommandDemo
{
SqlConnection conn;

public SqlCommandDemo()
{
// Instantiate the connection
conn = new SqlConnection("Data Source=MELINA_YEO-VAIO; Initial Catalog=InventorySystem; Integrated Security=True");
}



protected void ReadData(object sender, EventArgs e)
{
SqlDataReader rdr = null;

try
{

conn.Open();


SqlCommand cmd = new SqlCommand("Select case when (P_Amount <= SafetyStockamount) then 'Limit stock has been reached' end as Alert from Product", conn);


rdr = cmd.ExecuteReader();


while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{

if (rdr != null)
{
rdr.Close();
}

if (conn != null)
{
conn.Close();
}
}
}
}
}</code>

but when i execute the page does not show up @@
do i do sth wrong again with this ? [:(][:confused:]

Imar May 14th, 2014 09:34 AM

You need to do something with the results from the command. Currently, you loop over the data and output it to the console which isn't all that useful in a web application.

You need to add some if logic that alerts the message whenever some row in your data set matches your criteria. I think, but that's only a wild guess as I am still unsure what you're trying to accomplish) that you may need a WHERE clause for a product ID in your SQL statement. Then you can read your data and inside the while (dr.Read()) call check the return value and if it matches some condition then output your alert. E.g.:

Code:

while (rdr.Read())
                {
  if (rdr[0].ToString == "Whatever you're cheking"))
  {
    string message = "Limit stock has been reached!";
                ScriptManager.RegisterStartupScript(page, page.GetType(), "err_msg", "alert('" + message + "');", true);
  }
                }

Hope this helps,

Imar


All times are GMT -4. The time now is 12:03 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.