Is it possible to insert NEW record in table while selecting

Atul Rokade

Hi all,

I have two tables Sales_details and Login in Login table have

User_name, Password,Unique_no field( this data filled whenever new user added), Now i have Sales_details table(User_name,Unique_no,Sales_count,To_date) in this table sales detail has enter base on Login table,

Now what i exactly want when salesman done the sales at that time His user_name,unique_no extracted from login table and inserted into Sales_details but while inserting into sales details Sales_count should be 0 by default value when he try to first sale into the day afterwords sales count can be update as per his sales and To_date should be todays date

Now i able to extract User_name and Unique_no from Login to inserted Sales_details but i unable to insert by default value 0 in sales_count and Today date into To_date

note: im using ms-access database so i cant use trigger and store procedure tell me how can i executed query by c# inline coding??

insert into sales_tables(User_name,Unique_no) select User_name,Unique_no from login where unique_no=@unique_no;

3 Replies

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

Midhun Tp10y ago
Hi, there is some change in isnull method in ms access. Please replace
isnull(Sales_count,0)+1 with
IIF(ISNULL([Sales_count]), 0, [Sales_count])+1
Atul Rokade10y ago
exceptionis coming
Wrong number of arguments used with function in query expression 'isnull(Sales_count,0)+'.
i written following code:
cmd = new OleDbCommand("insert into Sales_Details(User_name,Unique_no,Sales_count,To_date) select l.User_name,l.Unique_no,isnull(Sales_count,0)+1,Date() from login l left join (select max(Sales_count) as Sales_count,Unique_no from Sales_Details where unique_no=@unique_no and convert(varchar(10),To_date,111)=convert(varchar(10),Date(),111)group by unique_no) st on l.Unique_no=st.Unique_no where 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;
}
Midhun Tp10y ago
Hi,

You can use left join in your select query for that. Somewhat like this -
insert into sales_tables(User_name,Unique_no,Sales_count,To_date)
select l.User_name,l.Unique_no,isnull(Sales_count,0)+1,getdate() from login l left join
(select max(Sales_count) as Sales_count,Unique_no from sales_tables where unique_no=@unique_no and convert(varchar(10),To_date,111)=convert(varchar(10),getdate(),111)
group by unique_no) st on l.Unique_no=st.Unique_no where unique_no=@unique_no;