How to insert another table data from select query for exist table

Question

0.00/5 (No votes)

See more:

Hi all,

Actually im creating one date base application where if person first day login then his sales_count will be one, same person login again then its sales_count will be 2 , same user login next day then its sales_count again 1 like
For this i created two tables Login and Sales_Details in Login having User_name,Password and Unique no. Now, in sales_details i want to fetch only User_name,Unique_no from login detail but at same time i want to increment sales_count in sales_details table also update today date Sales_details having Sales_id,User_name,Unique_No,Sales_count,To_Date field actually i tried but nothing happen if To_date is null then insert today date+sales_count as 1 in sales_details

below im pasting my whole code

What I have tried:

C#

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
                     {
                         
                        int counter=1;
                        cmd = new OleDbCommand("insert into Sales_Details select User_name,Unique_No from Login where Unique_No=@Unique_No Union select ISNULL(Sales_Count+1,0) as [@Sales_Count],DATE() AS [To_date]", con);
                         cmd.Parameters.AddWithValue("@Unique_No", txtinput.Text);
                         cmd.Parameters.AddWithValue("@Sales_count",counter);
                         
                           
                             int n1 = cmd.ExecuteNonQuery();

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

                             }
                             else
                             {
                                 this.DialogResult = DialogResult.OK;
                             }
    
                         
                     }
                 }
             }
              con.Close();

Posted 16-Aug-16 7:22am

Atul Rokade

Updated 16-Aug-16 9:57am

Add a Solution

Comments

Maciej Los 16-Aug-16 14:59pm

This is your 3. or 4. question about the same! Your questions[^]
Don't you understand that your question is unclear. Untill, you don't explain what is wrong with your code, no one will be able to help you...

Atul Rokade 16-Aug-16 15:07pm

@Maciej : this is my 2nd question only first one i deleted because of of not explaining exactly problem

Maciej Los 16-Aug-16 16:02pm

Check my asnwer.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-08-16T09:57:00

First of all, check past answer to your question: How to insert increment(+1) value in access table[^]

Second of all... Your query is wrong and will never execute:

SQL

update Login set Sales_count= IIF(IsNull(Sales_count), 0, Sales_count) + 1, [To_Date]=Date()
where [Unique_No]=@Unique_No

You have to use MAX[^] function to return the maximum value for Sales_count field.

SQL

SELECT Max([Login].Sales_count) AS MaxOfSalesCount
FROM [Login]
WHERE (([Login].Unique_No=@Unique_No) AND [Login].To_Date=Date());

To return single cell (result), use OleDbCommand.ExecuteScalar Method (System.Data.OleDb)[^]

Usage:

C#

public int NextSalesNo(string queryString, 
    OleDbConnection connection) 
{
    OleDbCommand command = new OleDbCommand(queryString, connection);
    command.Connection.Open();
    var result = command.ExecuteScalar();
    return  result == null ? 0 : result +1;
    connection.Close();
}

In case of above method return 0 (zero), you have to insert new data.
In other case you have to update Login table, by using below query:

SQL

UPDATE Login SET Sales_count = @NewSales
WHERE (([Login].Unique_No=@Unique_No) AND [Login].To_Date=Date());

where @NewSales is the value you get returned from the above method.

Try!

[EDIT]
My second thought: your database design is wrong.

You don't have to change / update Sales_count, you should be able to count it. For example: you have got table Sales. In that table you store the data about someone's sales:

SQL

CREATE TABLE Sales
{
    SaleID INT,  -- PK
    UserID INT,  -- seller
    SaleDate DateTime, -- the date of sale
    ...
};

To be able to count it, you should group data by UserID. For total sales:

SQL

SELECT UserID, COUNT(*) As CountOfSales
FROM Sales
GROUP BY UserID;

For daily sales:

SQL

SELECT UserID, DateOfSale, COUNT(*) As CountOfSales
FROM Sales
GROUP BY UserID, DateOfSale;

For specific user and date:

SQL

SELECT COUNT(*) As CountOfSales
FROM Sales
WHERE ((UserID=@UserID) AND (DateOfSale=@Date));

So, please, re-think your database design!

[/EDIT]