Passing global integers to SQL statements

Kenny Alligood · July 24th, 2003, 09:55 AM

I need some assistance and direction on how to pass an integer global variable to an SQL statement.

Here is the statement that I am trying to get functioning:

Code:

strSQL = _
"UPDATE tblData SET tblData.intInvalid = gintUser, tblData.intReason = 4, " & _
    "tblData.dtmInvalid = Date(), tblData.blnTrained = True, tblData.intTrainer = gintUser, " & _
    "tblData.dtmTrained = Date() " & _
"WHERE tblData.intCase = gintCaseNumber;"

I am having problems with the statement recognizing gintUser and gintCaseNumber. Both of these variables are set in the program and are correct - I checked this by the MsgBox function. I know to pass a string variable to a statement is needs to be in this format '" & gstrVariable & "' but I do not know how to get an interger passed. I have tried various ways to no avail and would appreciate any assistance in this area.

For background information the data that these variables are created from is this -- gintUser is DataType Number (Byte) and gintCaseNumber is DataType Long Integer (AutoNumber).

Thanx in advance for your help and ideas.

Kenny Alligood

SerranoG · July 24th, 2003, 12:06 PM

Quote:

quote:Originally posted by Kenny Alligood
Here is the statement that I am trying to get functioning:

Code:

strSQL = _
"UPDATE tblData SET tblData.intInvalid = gintUser, tblData.intReason = 4, " & _
    "tblData.dtmInvalid = Date(), tblData.blnTrained = True, tblData.intTrainer = gintUser, " & _
    "tblData.dtmTrained = Date() " & _
"WHERE tblData.intCase = gintCaseNumber;"

Hmmm... I think you need to place the two variables outside your SQL string:

Code:

strSQL = _
"UPDATE tblData SET tblData.intInvalid = " & gintUser & _
", tblData.intReason = 4, " & _
    "tblData.dtmInvalid = Date(), tblData.blnTrained = True, tblData.intTrainer = " & gintUser & ", " & _
    "tblData.dtmTrained = Date() " & _
"WHERE tblData.intCase = " & gintCaseNumber & ";"

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Kenny Alligood · July 25th, 2003, 08:25 AM

Thanx for the advice Greg. I thought I had tries that and other options but the solution was something of a mistake that I programmed in elsewhere. It seems that I had taken my Integer value and made it into a String therefore I needed '" & gintUser & "' for it to work. It looks like I have another problem to track down and correct first.

Kenny Alligood

Kenny Alligood · August 14th, 2003, 09:29 AM

I still have been unable to overcome this obstacle and would again like to humbly request your expertise. Let me give you the details of everything and hopefully that will assist you.

                     VB 6.0 (front end) A2K (back end)
Data Types
   gintUser Integer Data Type Number/Field Size Integer
   gintCaseNumber Integer Data Type AutoNumber/Field Size Long Integer

SQL string strSQL = _
                   "UPDATE tblData " & _
                   "SET tblData.blnTrained = True, tblData.intTrainer = ' & gintUser & ', " & _
                       "tblData.dtmTrained = Date() " & _
                   "WHERE tblData.intCase= ' & gintCaseNumber & ';"

ADO Command cxn.Execute strSQL, , adCmdText + adExecuteNoRecords

Response Run Time Error '-2147217913 (80040e07)'
          Data Type Mismatch in criteria expression

I know the error denotes that I am trying to mix data types but for the life of me I am unable to locate where that is happening. Everything that I do to assign these variables deals with them as Integers and not strings. There is one place that I use this on the field that becomes the variable ... Format(tblData.intCase,'00000') AS intCase ... but I do not see where that would have an bearing on the data type.

I am at the end of my rope here and really need your assistance to overcome this. If you need any further details please do not hesitate to ask and thank you....

Kenny Alligood

SerranoG · August 14th, 2003, 10:00 AM

Kenny, two things.

Quote:

quote:Originally posted by Kenny Alligood
VB 6.0 (front end) A2K (back end)
Data Types
gintUser Integer Data Type Number/Field Size Integer
gintCaseNumber Integer Data Type AutoNumber/Field Size Long Integer

If the A2K end has gintCaseNumber as long then the VB6.0 should also be type LONG. So both should be consistent. Change it to glngCaseNumber of type Long.

Quote:

quote:SQL string strSQL = _
                 "UPDATE tblData " & _
                 "SET tblData.blnTrained = True, tblData.intTrainer = ' & gintUser & ', " & _
                     "tblData.dtmTrained = Date() " & _
                 "WHERE tblData.intCase= ' & gintCaseNumber & ';"

Your quotation marks are wrong. Remember, when the variable is a text field, number, or a date, it looks like this, respectively:

"[strField] = '" & gstrField & "'" (note the single quotes)
"[intField] = " & gintField
"[dtmField] = #" & gdtmField & "#" (note the pound signs)

So in your case,

Code:

strSQL = "UPDATE tblData SET tblData.blnTrained = True, " & _
   "tblData.intTrainer = " & gintUser & ", " & _
   "tblData.dtmTrained = #" & Date() & "# " & _
   "WHERE tblData.lngCase = " & glngCaseNumber & ";"

Notes:

the gintUser does not get single quotes around it.
The Date() must be taken out of the string because it's a function. The pound signs are required for dates.
The glngCaseNumber does not get single quotes around it.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division