Data type conflict in criteria expression

Question

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

name (short String)	value (Integer)
MiPaCount	5

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

ID (Integer, Byte)	Startzeit (short String)
1	11:30

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

If ID field is number or autonumber, don't use quote or apostrophe delimiters. Those are for text fields. Date fields use # delimiter. — June7, Nov 16 '21 at 01:23
Print out the query and try running it directly against access. To see the query, add `Response.Write(dbQuery) : Response.End` just before the `objRecordset.Open dbQuery, objConnection` line. — Flakes, Nov 16 '21 at 03:27
@Flakes you’re assuming they are using VBScript in Classic ASP but the question is not tagged [tag:asp-classic] and there is nothing in the question to suggest this isn’t just calling an Access database from VBScript. — user692942, Nov 16 '21 at 08:07
Are you using Classic ASP? Last question you asked was in relation to a HTA application. — user692942, Nov 16 '21 at 08:14
@user692942 it's the same .vbs-file running inside my hta-file as in my last question. Sorry, didn't think about other possibilities. Will add hta-tag. — Soyaro, Nov 16 '21 at 09:44
@June7 I will edit my question to make my data more readable. — Soyaro, Nov 16 '21 at 09:46
...I can't figure out how to get SO to show the table properly. The result is way worse than everything I had before... — Soyaro, Nov 16 '21 at 10:30
@Soyaro You need a line break before table markdown or it isn't parsed correctly. Fixed. — user692942, Nov 16 '21 at 12:54

score 2 · Accepted Answer · answered Nov 16 '21 at 06:55

2

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.

answered Nov 16 '21 at 06:55

Gustav

53,498
7
29
55

The OP has tagged the question [tag:vbscript] not [tag:vba]. The `Format()` function does not exist in VBScript plus they’d need to modify your CSql code to work in VBScript. – user692942 Nov 16 '21 at 08:05
1

@user692942: Missed that, sorry. Then the questioneer is hosed, as VBScript has no data types. Three or more functions will be needed; or an additional argument to specify the expected data type of argument `sConVal`, and the function must be expanded to handle these. – Gustav Nov 16 '21 at 08:10
I found multiple results pointing at the datatype in DB but I didn't figure out how to handle that. Now I found the function "vartype" and am thinking about adding an additional switch. Could that work? I'm a little confused about the statement from @Gustav that "VBScript has no data types" right now. Could it be that this function only works in ASP-Classic? Then I can't use it and do indeed need an additional parameter for the function. – Soyaro Nov 16 '21 at 10:25
1

@Soyaro VBScript is typeless as in it doesn't support `As` in declarations like VBA for example. All it's variables are data type `Variant` but through sub typing supports types like `Integer`, `String`, `Date` etc., using `VarType()` and `VarTypeName()` can help you identify these. Recommend reading through the [Official Documentation](https://learn.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/scripting-articles/t0aew7h6(v=vs.84)). – user692942 Nov 16 '21 at 12:50
@user692942 I'll have time to give it a try in 2 to 3 hours. I'll report the result when I'm done. – Soyaro Nov 16 '21 at 15:02
2

got it to work based on this answers strings. I added a new parameter to the function to name what type the reference column contains, added a second switch below the first one that adds the WHERE to my dbQuery and got rid of the if else inside the wirst switch. – Soyaro Nov 16 '21 at 22:29

score -1 · Answer 2 · answered Nov 16 '21 at 14:42

-1

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.

answered Nov 16 '21 at 14:42

Craig Covey

1
1

1

tmp, in my code, is a variable that contains a number (1, in my example). by calling it with "tmp" I would use a hardcoded string while I require it to use a variable in that situation (currently possible valueas are 1 to 5) – Soyaro Nov 16 '21 at 14:58
Code proposition is same as previous code – Tom Carter Nov 16 '21 at 16:16

Data type conflict in criteria expression

2 Answers2