Is it possible to insert NEW record in table while selecting

Question

Hi all I have two tables Sales_details and Login in Login table have User_name PasswordUnique_no field( this data filled whenever new user added) Now i have Sales_details table(User_nameUnique_noSales_countTo_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_nameunique_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_nameUnique_no) select User_nameUnique_no from login where unique_no=@unique_no;

Midhun T P · Answer

Hi there is some change in isnull method in ms access. Please replace isnull(Sales_count0)+1 with IIF(ISNULL([Sales_count]) 0 [Sales_count])+1

Atul Rokade · Answer

exceptionis coming Wrong number of arguments used with function in query expression 'isnull(Sales_count0)+'. i written following code: cmd = new OleDbCommand(insert into Sales_Details(User_nameUnique_noSales_countTo_date) select l.User_namel.Unique_noisnull(Sales_count0)+1Date() from login l left join (select max(Sales_count) as Sales_countUnique_no from Sales_Details where unique_no=@unique_no and convert(varchar(10)To_date111)=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 T P · Answer

Hi You can use left join in your select query for that. Somewhat like this - insert into sales_tables(User_nameUnique_noSales_countTo_date) select l.User_namel.Unique_noisnull(Sales_count0)+1getdate() 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_date111)=convert(varchar(10)getdate()111) group by unique_no) st on l.Unique_no=st.Unique_no where unique_no=@unique_no ;