新添加到Access数据库的数据在查询中不起作用

So this Monday, I tried to run the following query and it worked:

所以在本周一,我尝试运行以下查询并且它有效:

SELECT 
    DatumOntslag, p.Naam, d.Naam, med.ID, m.Naam, HoeVaakPerDag 
FROM 
    Patient p, Diagnose d, Medicatie med, Medicijn m 
WHERE 
   p.DiagnoseID = d.ID 
   AND med.PatientID = p.ID 
   AND med.MedicijnID = m.ID 
   AND p.ID = " + lbPatientsID.SelectedItem.ToString();

In this, the lbPatientsId.SelectedItem.ToString() is a variable from my program. Now if I open my Access database and add a new row of data to the table "Patient" and I try to run that query, it doesn't work. However, when I run the query with the ID of an old patient it does work.

在这里,lbPatientsId.SelectedItem.ToString()是我程序中的一个变量。现在,如果我打开我的Access数据库并向表“Patient”添加新的数据行,并且我尝试运行该查询,则它不起作用。但是,当我使用旧患者的ID运行查询时,它确实有效。

This works (p.ID (Patient ID) created before today):

这是有效的(今天之前创建的p.ID(患者ID)):

SELECT 
    DatumOntslag, p.Naam, d.Naam, med.ID, m.Naam, HoeVaakPerDag 
FROM 
    Patient p, Diagnose d, Medicatie med, Medicijn m 
WHERE 
    p.DiagnoseID = d.ID 
    AND med.PatientID = p.ID 
    AND med.MedicijnID = m.ID 
    AND p.ID = 21;

This doesn't work (p.ID (Patient ID) created today):

这不起作用(今天创建的p.ID(患者ID)):

SELECT 
    DatumOntslag, p.Naam, d.Naam, med.ID, m.Naam, HoeVaakPerDag 
FROM 
    Patient p, Diagnose d, Medicatie med, Medicijn m 
WHERE 
    p.DiagnoseID = d.ID 
    AND med.PatientID = p.ID 
    AND med.MedicijnID = m.ID 
    AND p.ID = 31;

2 个解决方案

#1

So since you quote

所以既然你引用了

lbPatientsID.SelectedItem.ToString()

, it would pass as "lbPatientsID.SelectedItem.ToString()".

,它将作为“lbPatientsID.SelectedItem.ToString()”传递。

So if you want make it work as

所以,如果你想让它工作

P.ID = "{Some selected value}"

do like this:

这样做:

SELECT DatumOntslag, p.Naam, d.Naam, med.ID, m.Naam, HoeVaakPerDag 
FROM  Patient p, Diagnose d, Medicatie med, Medicijn m 
WHERE p.DiagnoseID = d.ID 
AND med.PatientID = p.ID 
AND med.MedicijnID = m.ID 
AND p.ID = " "+ lbPatientsID.SelectedItem.ToString();

Then I think it would WORK as what you want.

然后我认为它会像你想要的那样工作。

#2

Since p.ID is being compared to 31 I guess this should work (should show you data)

因为p.ID被比较为31我想这应该工作(应该显示数据)

SELECT DatumOntslag, p.Naam, d.Naam, med.ID, m.Naam, HoeVaakPerDag 
    FROM Patient p, Diagnose d, Medicatie med, Medicijn m 
    WHERE p.DiagnoseID = d.ID AND med.PatientID = 31
        AND med.MedicijnID = m.ID AND p.ID = 31;

Or also trying making your where less restrictive

或者也尝试让你的限制更少

WHERE 
    p.DiagnoseID = d.ID AND med.PatientID = 31
--  AND med.MedicijnID = m.ID AND p.ID = 31; 

WHERE 
--  p.DiagnoseID = d.ID AND med.PatientID = 31
--  AND 
    med.MedicijnID = m.ID AND p.ID = 31;

Also try removeing the WHERE an use TOP to avoid overloading the DataBase

还尝试删除WHERE使用TOP以避免重载DataBase

SELECT TOP 10 DatumOntslag, p.Naam, d.Naam, med.ID, m.Naam, HoeVaakPerDag 
    FROM Patient p, Diagnose d, Medicatie med, Medicijn m 
    WHERE p.DiagnoseID = d.ID AND med.MedicijnID = m.ID

#1