今天数据过滤器来自第二个表,它不在访问数据库的第一个表中 [英] today data filter from second table which not in first table in access database
问题描述
您好我有一个访问数据库,在数据库中我创建三个表第一个表名是附加,第二个是模板而第三个是不存在我想过滤哪个数据不在附件过滤器中只有今天日期从模板表中我不知道我想要旧的用户名和插入缺席表
表详情
Hi I have an access database and in database i create Three Table First Table Name Is Attandance, Second is Template And Third is Absent i want to filter which data not in Attandance filter by Only Today Date from Template table and i don't want old dated User ID and Insert in Absent Table
Table Details
#Attandance Field = TemplateID(AutoNumber), UserID(Number),State(Text),Att_Date(Date/Time)
Template Field = TemplateID(AutoNumber),UserID(Number),Full_Name(Te xt),Age(Number)
Absent Field = TemplateID(AutoNumber), UserID(Number),State(Text),Att_Date(Date/Time)
C#编码----->开始< --------
C# coding -----> Start <--------
string[] mylist = new string[3000];
int i = 0;
str = "select a.*,b.* from Template a, Attandance b where a.UserID=b.UserID and ATT_Date="+mydate+"";//order by ATT_Date desc
cmd = new OleDbCommand(str, conn);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
String ddate = dr[14].ToString().Substring(0, dr[14].ToString().Length - 11).Trim();
if (ddate.Trim().Equals(mydate.Trim()) == false)
{
mylist[i] = dr[1].ToString();
i++;
dt.Rows.Add(dr[1].ToString(), dr[6].ToString(), dr[7].ToString() + dr[8].ToString(), dr[9].ToString(), dr[10].ToString(), ddate);
}
}
dr.Close();
C#编码 - ---->结束< --------
C# coding -----> END <--------
推荐答案
嗨Raj,
而不是此行
Hi Raj,
Instead of this line
str = "select a.*,b.* from Template a, Attandance b where a.UserID=b.UserID and ATT_Date="+mydate+"";
你可以试试这行,这会给你你所期待的。
you can try this line this will give you what your expecting.
select A.* from Template A where A.UserID not in(select Distinct UserID from Attandance where convert (nvarchar( 50),Att_Date,103)=convert (nvarchar( 50),GetDate(),103))
// Instead of GetDate() you can give your Date.
我希望这会对你有所帮助。
感谢
Mohan G
I hope this will help you.
Thank's
Mohan G
>
这篇关于今天数据过滤器来自第二个表,它不在访问数据库的第一个表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!