VB.Net SQL 查询在 VB 中不起作用，但在 Access 中起作用 [英] VB.Net SQL Query not working in VB but does in Access

问题描述

我想弄清楚为什么这个查询没有返回结果.相同的查询在 Access 中工作正常，但在 VB.Net 中无效.任何帮助，将不胜感激.尝试填充数据集时代码失败.谢谢.

I'm trying to work out why this query does not return results. The same query works fine in Access but not in VB.Net. Any help would be appreciated. The code Fails when trying to fill the data set. Thanks.

Function Populate_Month()

    Dim dbConnection As New OleDbConnection
    Dim dbConnectionStr As String
    Dim dbProvider As String
    Dim dbSource As String

    dbProvider = "Microsoft.Jet.OLEDB.4.0;"
    dbSource = "C:\Users\Scraps\Documents\Gossamer.mdb"

    dbConnectionStr = "Provider=" & dbProvider & "Data Source=" & dbSource
    dbConnection = New OleDbConnection(dbConnectionStr)
    dbConnection.Open()

    Dim dbAdapter As OleDbDataAdapter
    Dim dbDataSet = New DataSet
    Dim dbQueryStr As String
    Dim CurrentRow As Integer

    dbQueryStr = "SELECT * FROM Forecast_TDL" & _
         "WHERE Forecast_TDL.EIAC & Forecast_TDL.LCN & Forecast_TDL.Servicing & Forecast_TDL.Interval & Forecast_TDL.Interval_Type" & _
         "NOT IN (SELECT EIAC & LCN & Servicing & Interval & Interval_Type FROM Grouped_Servicings);"

    dbAdapter = New OleDbDataAdapter(dbQueryStr, dbConnection)

    dbAdapter.Fill(dbDataSet, "forecast")
    CurrentRow = 0
    MsgBox(dbDataSet.Tables("forecast").Rows(CurrentRow)("Interval"))

    dbConnection.Close()
    Return Nothing

End Function

推荐答案

在 MS Access 中也没有办法.您必须执行以下操作:

There is no way that works in MS Access either. You would have to do something like:

SELECT * FROM Forecast_TDL 
WHERE Forecast_TDL.EIAC NOT IN(SELECT EIAC FROM Grouped_Servicings) AND 
Forecast_TDL.LCN NOT IN(SELECT LCN FROM Grouped_Servicings) AND
Forecast_TDL.Servicing NOT IN(SELECT Servicing FROM Grouped_Servicings) And 
Forecast_TDL.Interval NOT IN(SELECT Interval FROM Grouped_Servicings) And 
Forecast_TDL.Interval_Type NOT IN (SELECT Interval_Type FROM Grouped_Servicings)

更好地理解问题后，您可以通过使用 JOIN 来避免比较连接的字符串并查找不存在行的位置.

After understanding the question better, you can avoid comparing concatenated strings by using a JOIN and finding where a row doesn't exist.

SELECT * FROM Forecast_TDL
LEFT OUTER JOIN Grouped_Servicings
ON Forecast_TDL.EIAC = Grouped_Servicings.EIAC AND
 Forecast_TDL.LCN = Grouped_Servicings.LCN AND
 Forecast_TDL.Servicing = Grouped_Servicings.Servicing AND
 Forecast_TDL.Interval = Grouped_Servicings.Interval AND
 Forecast_TDL.Interval_Type = Grouped_Servicings.Interval_Type
WHERE Grouped_Servicings.EIAC IS NULL OR
      Grouped_Servicing.LCN IS NULL OR
      Grouped_Servicing.Servicing IS NULL OR
      Grouped_Servicing.Interval IS NULL OR
      Grouped_Servicing.Interval_Type IS NULL

这篇关于VB.Net SQL 查询在 VB 中不起作用，但在 Access 中起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！