I'm currently trying to use a db (.accdb-file) in my vbscript.

my function from a .vbs-file that's executed by a hta-file:

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

the results are used to decide some things for the design of my hta-file.

I have multiple uses for it. calling it like this:

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

returns a number according to

but if I call it with

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

I get the error from the top. The table currently looks like

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

CodePudding user response：

You are missing that you wish to handle several data types, and to take care of reserved words for field names. So, for example, you would need:

' 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") & "#"

You may be able to simplify this by using my function CSql, but I haven't tested it in a scenario like this.

CodePudding user response：

Try replacing this line:

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

with this:

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

You're looking for a value in that parameter and tmp (without quotations) is expected to be a numerical one.