Tech_Men
asked on
i need to compare in the products table and actions table
i need to do this :
in my products table i have for each product unit in stock alarm for exsample
UnitKey |MinInStock
1000 100
now i have a ActionTable whit all the products Qunataty in the stock
if the Product quantity is less from the MinInStock
i need to get message i have tryd to do this like that :
statusBar1.Text="";
listBox1.Items.Clear();
sqlConnection1.Close();
sqlConnection2.Close();
SqlConnection NewConn = new SqlConnection();
NewConn.ConnectionString=B izLog.Conn .ConnStrin g();
NewConn.Close();
IDbCommand cmd1 = this.sqlConnection1.Create Command();
IDbCommand cmd2 = this.sqlConnection2.Create Command();
cmd1.CommandText = "SELECT * FROM Products";
cmd2.CommandText = "Select * From VIEW1";
IDbCommand CmdGetTotalByCode=NewConn. CreateComm and();
IDbCommand CmdgetHatByCode = NewConn.CreateCommand();
CmdGetTotalByCode.CommandT ext="SELEC T SUM(Kamot) AS ParitTotal FROM Actions WHERE (CodeParit = @CA)";
SqlParameter parm = new SqlParameter("@CA",SqlDbTy pe.VarChar ,50,"CodeP arit");
parm.Direction=ParameterDi rection.In put;
CmdGetTotalByCode.Paramete rs.Add(par m);
CmdgetHatByCode.CommandTex t="SELECT ProMinimom FROM View1 WHERE (ProKey = @CA)";
SqlParameter parm1 = new SqlParameter("@CA",SqlDbTy pe.VarChar ,50,"Parit Num");
parm1.Direction=ParameterD irection.I nput;
CmdgetHatByCode.Parameters .Add(parm1 );
sqlConnection1.Open();
sqlConnection2.Open();
IDataReader reader = cmd1.ExecuteReader();
IDataReader reader1 = cmd2.ExecuteReader();
while (reader.Read())
{
string ProKey = reader["ProKey"].ToString( );
while(reader1.Read())
{
string Pkey=reader1["ProKey"].ToS tring();
if (ProKey.ToString()==Pkey.T oString())
{
string Degem=reader["ProKey"].ToS tring();
parm.Value=Degem.ToString( );
parm1.Value=Degem.ToString ();
NewConn.Open();
int t=Int32.Parse(CmdGetTotalB yCode.Exec uteScalar( ).ToString ());
int h=Int32.Parse(CmdgetHatByC ode.Execut eScalar(). ToString() );
NewConn.Close();
if (h>t)
{
listBox1.Items.Add("ProKey " + Degem.ToString() + " " + "U need to order moreי");
}
}
}
}
reader.Close();
reader1.Close();
sqlConnection1.Close();
sqlConnection2.Close();
but its dosent work well
in my products table i have for each product unit in stock alarm for exsample
UnitKey |MinInStock
1000 100
now i have a ActionTable whit all the products Qunataty in the stock
if the Product quantity is less from the MinInStock
i need to get message i have tryd to do this like that :
statusBar1.Text="";
listBox1.Items.Clear();
sqlConnection1.Close();
sqlConnection2.Close();
SqlConnection NewConn = new SqlConnection();
NewConn.ConnectionString=B
NewConn.Close();
IDbCommand cmd1 = this.sqlConnection1.Create
IDbCommand cmd2 = this.sqlConnection2.Create
cmd1.CommandText = "SELECT * FROM Products";
cmd2.CommandText = "Select * From VIEW1";
IDbCommand CmdGetTotalByCode=NewConn.
IDbCommand CmdgetHatByCode = NewConn.CreateCommand();
CmdGetTotalByCode.CommandT
SqlParameter parm = new SqlParameter("@CA",SqlDbTy
parm.Direction=ParameterDi
CmdGetTotalByCode.Paramete
CmdgetHatByCode.CommandTex
SqlParameter parm1 = new SqlParameter("@CA",SqlDbTy
parm1.Direction=ParameterD
CmdgetHatByCode.Parameters
sqlConnection1.Open();
sqlConnection2.Open();
IDataReader reader = cmd1.ExecuteReader();
IDataReader reader1 = cmd2.ExecuteReader();
while (reader.Read())
{
string ProKey = reader["ProKey"].ToString(
while(reader1.Read())
{
string Pkey=reader1["ProKey"].ToS
if (ProKey.ToString()==Pkey.T
{
string Degem=reader["ProKey"].ToS
parm.Value=Degem.ToString(
parm1.Value=Degem.ToString
NewConn.Open();
int t=Int32.Parse(CmdGetTotalB
int h=Int32.Parse(CmdgetHatByC
NewConn.Close();
if (h>t)
{
listBox1.Items.Add("ProKey
}
}
}
}
reader.Close();
reader1.Close();
sqlConnection1.Close();
sqlConnection2.Close();
but its dosent work well
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can do this by a simple select like this (i dont know the exact structure of you database so names are more like a guess):
select Products.*
from Products inner join ActionTable
on Products.ID == ActionTable.ProductID
where Products.MinStock < ActionTable.ProductQuantit
then parse the returned DataTable and do your alert thing
hth,
A.