How To use insert query in select statement

[Atul Rokade]

Hi all,

Iam creating one application in C#.net in that when user enter any sales then his count should be increment , if user sale in First login then his sales count will be 1 after in whole day everytime suppose he enter a sales then his sales_count will updated+1, next day suppose he again sale then again start from 1 sales count should don base on DATE , now i have two tables LOGIN and SALES_DETAILS , in login table User_name, Password,Unique_No field have , where User_name, Unique_No, Sales_count, To_Date field have

Now what i want when when user enter his sales detail at that time his User_name,Unique_no should auto save(BASE ON LOGIN TABLE Where Condition) in Sales_Details field at same time Sales_count should be increase, also To_Date For this i written a code but it wont working (Last so many days im trying to resolve this issue but failed ..),



string todaydate = DateTime.Now.ToString("dd/MM/yyyy");


string access = "select To_Date from Sales_Details";
cmd = new OleDbCommand(access, con);
con.Open();
using (OleDbDataReader read = cmd.ExecuteReader())
{
while (read.Read())
{
string date2 = read["To_Date"].ToString();
if (todaydate == date2)
{
cmd = new OleDbCommand("update Sales_Details set Sales_count= IIF(IsNull(Sales_count), 0, Sales_count) + 1, [To_Date]=Date() where [Unique_No]=@Unique_No", con);
cmd.Parameters.AddWithValue("@Unique_No", txtinput.Text);
con.Open();
int n = cmd.ExecuteNonQuery();

if (n == 0)
{
MessageBox.Show("Invalid Unique No.");

}
else
{
this.DialogResult = DialogResult.OK;
}

con.Close();



}
else
{



cmd = new OleDbCommand("INSERT INTO Sales_Details SELECT lg.User_name,lg.Unique_no,IIF(isnull(sd.Sales_Count,0)+1),sd.To_Date=DATE() FROM Login lg LEFT JOIN Sales_Details sd ON lg.Unique_no=sd.Unique_no WHERE lg.Unique_no=@Unique_No ", con);

cmd.Parameters.AddWithValue("@Unique_No", txtinput.Text);



int n1 = cmd.ExecuteNonQuery();

if (n1 == 0)
{
MessageBox.Show("Invalid Unique No.");

}
else
{
this.DialogResult = DialogResult.OK;
}


MessageBox.Show("recrod insert");


}
}
}
con.Close();//i used debugger also but it always went to this line only






Atul

Continue reading...