如何从select表中为select表插入另一个表数据 [英] How to insert another table data from select query for exist table
问题描述
大家好,
实际上我正在创建一个日期基础应用程序,如果第一天登录,那么他的sales_count将是一个,同一个人再次登录然后它的sales_count将是2,相同的用户登录第二天然后它的sales_count再次1像
为此我在Login中创建了两个表Login和Sales_Details具有User_name,Password和Unique no。现在,在sales_details中,我想从登录详细信息中仅获取User_name,Unique_no,但同时我想在sales_details表中增加sales_count也在今天更新日期Sales_idtails具有Sales_id,User_name,Unique_No,Sales_count,To_Date字段实际上我尝试但是没有任何反应To_date为null然后今天在sales_details中插入date + sales_count为1
以下我粘贴我的整个代码
我尝试了什么:
string todaydate = DateTime.Now.ToString( dd / MM / yyyy);
string access = 从Sales_Details中选择To_Date 跨度>;
cmd = new OleDbCommand(access,con);
con.Open();
使用(OleDbDataReader read = cmd.ExecuteReader())
{
while (read.Read())
{
string date2 = read [ To_Date]。ToString();
if (todaydate == date2)
{
cmd = new OleDbCommand( 更新Sales_Details设置Sales_count = IIF(IsNull(Sales_count),0,Sales_count)+ 1,[To_Date] = Date(),其中[Unique_No] = @ Unique_No,con);
cmd.Parameters.AddWithValue( @ Unique_No,txtinput.Text);
con.Open();
int n = cmd.ExecuteNonQuery();
if (n == 0 )
{
MessageBox.Show( 无效的唯一编号);
}
else
{
this .DialogResult = DialogResult.OK;
}
con.Close();
}
else
{
< span class =code-keyword> int counter = 1 ;
cmd = new OleDbCommand( insert into Sales_Details从Login中选择User_name,Unique_No,其中Unique_No = @ Unique_No Union选择ISNULL(Sales_Count + 1,0)为[@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( 无效的唯一编号);
}
else
{
this .DialogResult = DialogResult.OK;
}
}
}
}
con.Close();
首先,检查您问题的过去答案:如何在访问表中插入增量(+1)值 [ ^ ]
Second所有...你的查询是错误的,永远不会执行:
update 登录 set Sales_count = IIF(IsNull(Sales_count), 0 ,Sales_count)+ 1 ,[To_Date] =日期()
其中 [Unique_No] = @ Unique_No
您必须使用 MAX [ ^ ]函数返回Sales_count
字段的最大值。
SELECT Max([Login] .Sales_count) AS MaxOfSalesCount
FROM [登录]
WHERE (([登录] .Unique_No = @ Unique_No)和 [登录] .To_Date = Date());
要返回单个单元格(结果),请使用 OleDbCommand.ExecuteScalar方法(System.Data.OleDb) [ ^ ]
用法:
public int NextSalesNo( string queryString,
OleDbConnection connection)
{
OleDbCommand command = new OleDbCommand(queryString,连接);
command.Connection.Open();
var result = command.ExecuteScalar();
返回结果== null ? 0 :结果+1;
connection.Close();
}
如果上述方法返回0
(零),您必须插入新数据。
在其他情况下,您必须使用以下查询更新登录
表:
更新登录 SET Sales_count = @ NewSales
WHERE (([登录] .Unique_No = @ Unique_No) AND [登录] .To_Date = Date());
其中@NewSales
是值你从上面的方法中退回。
试试!
我的第二个念头:你的数据库设计错了。
你不必更改/更新Sales_count
,您应该可以计算它。例如:您有表Sales
。在该表中,您存储有关某人销售的数据:
CREATE 表销售额
{
SaleID INT , - PK
UserID INT , - 卖方
SaleDate DateTime , - 销售日期
...
};
为了能够计算,你应该按UserID
对数据进行分组。总销售额:
SELECT UserID,COUNT(*)作为 CountOfSales
FROM 销售额
GROUP BY UserID;
日常销售:
SELECT UserID,DateOfSale,COUNT(*) As CountOfSales
< span class =code-keyword> FROM 销售额
GROUP BY UserID ,DateOfSale;
对于特定用户和日期:
SELECT COUNT(*) As CountOfSales
FROM Sales
WHERE ((UserID = @ UserID) AND (DateOfSale = @ Date) );
所以,请重新考虑一下你的数据库设计!
[/ EDIT ] 的
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:
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();
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:
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 forSales_count
field.
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:
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 return0
(zero), you have to insert new data.
In other case you have to updateLogin
table, by using below query:
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 / updateSales_count
, you should be able to count it. For example: you have got tableSales
. In that table you store the data about someone's sales:
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 byUserID
. For total sales:
SELECT UserID, COUNT(*) As CountOfSales FROM Sales GROUP BY UserID;
For daily sales:
SELECT UserID, DateOfSale, COUNT(*) As CountOfSales FROM Sales GROUP BY UserID, DateOfSale;
For specific user and date:
SELECT COUNT(*) As CountOfSales FROM Sales WHERE ((UserID=@UserID) AND (DateOfSale=@Date));
So, please, re-think your database design!
[/EDIT]
这篇关于如何从select表中为select表插入另一个表数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!