SQL "equals operator" in queries

Steve777 · June 24th, 2005, 10:26 AM

Please can you tell me what is wrong with this function?

When I pass strings for valueA and valueB, it works OK. But when I pass integers for valueA and valueB it does not work.

Does the "=" operator work for strings only? If so, how can I generalise this function to work no matter what type of variable valueA and valueB are?

------------

Function GetData(cursor, locking, table, fieldA, valueA, fieldB, valueB, order)

    Dim rsData
    Dim strSQL

    strSQL = "SELECT * FROM " & table & _
         " WHERE " & fieldA & " = " & valueA & " AND " & fieldB & " = " & valueB & _
         " ORDER BY " & order & " ASC;"

    Set rsData = Server.CreateObject("ADODB.RecordSet")
    rsData.Open strSQL, dcnDB, cursor, locking

    Set GetData = rsData

End Function

------------

Jeff Mason · June 24th, 2005, 10:42 AM

Quote:

quote:Originally posted by Steve777
When I pass strings for valueA and valueB, it works OK. But when I pass integers for valueA and valueB it does not work.

Are you sure about that? I would guess that your code fails just the other way around; i.e. it works for integers and numbers in general and fails for strings.

Consider what the generated SELECT statement would look like when I use the string abc for valueA and xyz for valueB, and use the 'field' (they're not fields in a relational database, they're columns) names fieldA and fieldB:

Code:

    SELECT * FROM table WHERE fieldA = abc AND fieldB = xyz
         ORDER BY order ASC;

The string values abc and xyz have to be quoted for the SELECT syntax to be legal.

It sometimes is helpful to put a debugging statement in there to display the generated SQL text string when things aren't working right.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com