條件運算式中的資料型別沖突

我目前正在嘗試在我的 vbscript 中使用 db（.accdb 檔案）。

我的函式來自由 hta 檔案執行的 .vbs 檔案：

function dbCall(sAction, sPayload, sTable, sConCol, sConVal)
    updateLocalDB()
    
    Dim sConnectionString, objConnection, objRecordset, dbQuery, lTemp
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & localDB
    
    'Query für die DB zusammenbauen
    Select Case sAction
        Case "get"
            If sConCol = False Then
                dbQuery = "SELECT " & sPayload & " FROM " & sTable
            Else
                dbQuery = "SELECT " & sPayload & " FROM " & sTable & " WHERE " & sConCol & " = '" & sConVal & "'"
            End If
    End Select
    
    'do DB-Stuff
    objConnection.open sConnectionString
    objRecordset.Open dbQuery, objConnection
    IF objRecordset.fields.Count = 1 Then
        lTemp = objRecordset.fields(0)
    End If
    objRecordset.close
    objConnection.close
    dbCall = lTemp
End function

結果用于決定我的 hta 檔案設計的一些事情。

我有多種用途。像這樣稱呼它：

getSlotAmount = dbCall("get", "value", "config", "name", "MiPaCount")

根據

但如果我用

iStart = dbCall("get", "startzeit", "slots", "ID", tmp)

我從頂部得到錯誤。該表目前看起來像

previously the "startzeit"-column was a time-type but since I'm only storing and not calculating Data in the DB it's not that important I tried to use an integer and a string as tmp but in all these cases it gives me an error on the line where I try to objRecordset.open (german: "Datentypenkonflikt in Kriterienausdruck", translating it by google resulted in the title). While creating this question SO offered me multiple similar posts that lead me to two more experiments.

When I tried to change the string "dbQuery = ..." like this:

dbQuery = "SELECT " & sPayload & " FROM " & sTable & " WHERE " & sConCol & " = '" & sConVal

it said that a required value is missing. When I tried this:

dbQuery = "SELECT " & sPayload & " FROM " & sTable & " WHERE " & sConCol & " = """ & sConVal & """"

I again got the error from the title so I returned to my original string (since it works fine with my first example of using dbCall). What am I missing?

EDIT: Found a solution based on the accepted answer. with this function it works:

function dbCall(sAction, sPayload, sTable, conCol, conVal, conType)
    updateLocalDB()
    
    Dim sConnectionString, objConnection, objRecordset, dbQuery, lTemp
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & localDB
    
    'Query für die DB zusammenbauen
    Select Case sAction
        Case "get"
            dbQuery = "SELECT " & sPayload & " FROM " & sTable
    End Select
    Select Case conType
        Case "str"
            dbQuery = dbQuery  & " WHERE " & ConCol & " = '" & ConVal & "'"
        Case "int"
            dbQuery = dbQuery  & " WHERE " & ConCol & " = " & ConVal
    End Select
    
    'do DB-Stuff
    objConnection.open sConnectionString
    objRecordset.Open dbQuery, objConnection
    IF objRecordset.fields.Count = 1 Then
        lTemp = objRecordset.fields(0)
    End If
    objRecordset.close
    objConnection.close
    dbCall = lTemp
End function

uj5u.com熱心網友回復：

您沒有希望處理多種資料型別，并希望處理欄位名稱的保留字。因此，例如，您需要：

' For text:
dbQuery = "SELECT " & sPayload & " FROM " & sTable & " WHERE [" & sConCol & "] = '" & sConVal & "'"

' For numbers:
dbQuery = "SELECT " & sPayload & " FROM " & sTable & " WHERE [" & sConCol & "] = " & Str(sConVal) & ""

' For dates:
dbQuery = "SELECT " & sPayload & " FROM " & sTable & " WHERE [" & sConCol & "] = #" & Format(DateValue(sConVal), "yyyy\/mm\/dd") & "#"

您可以使用我的函式CSql來簡化它，但我還沒有在這樣的場景中測驗過它。

uj5u.com熱心網友回復：

嘗試替換此行：

iStart = dbCall("get", "startzeit", "slots", "ID", tmp)

有了這個：

iStart = dbCall("get", "startzeit", "slots", "ID", "tmp")

您正在該引數中尋找一個值，而 tmp（不帶引號）應該是一個數字值。

標籤：ms-access vbscript hta

上一篇：access中的子查詢：如何獲取上一行的計算值

下一篇：如果復選框為false，如何從資料庫中排除記錄？