Link to home
Start Free TrialLog in
Avatar of jdrits
jdrits

asked on

Database Query Returns Numbers as Text With ADO

Greetings, experts.

I created the following function to perform an ADO database query against SQL Server 2000 and return a disconnected recordset:

Function sql_select_rs(strSQL, objPassedRS)
      Dim objConn, objIncRS
      Set objConn = Server.CreateObject("ADODB.Connection")

      Set objIncRS = Server.CreateObject("ADODB.Recordset")
      objIncRS.CursorLocation = 3      'adUseClient
      objConn.Open MAILAPPDSN    'My DSN info

      objIncRS.Open strSQL, objConn, 3, 1, 1      'adOpenStatic, adLockReadOnly, adCmdText

      if Not objIncRS Is Nothing then
            Set objIncRS.ActiveConnection = Nothing
      end if
      Set objPassedRS = objIncRS

      Set objIncRS = Nothing
      objConn.Close
      Set objConn = Nothing
      sql_select_rs = true
End Function

The problem is whenever I try to perform any arithmetic in VBScript on numerical fields (i.e. bigint type), I get a type mismatch error. For example, the following gives the error with the above code:

blnReturn = sql_select_rs("select * from emails where email_id = " & strID, objRS)
nSent = nSent + objRS("total_sent")

but nSent = nSent + Clng(objRS("total_sent")) seems to work, even though total_sent is defined as a bigint.

Is this because I used adCmdText in the open statement? I don't want to have to convert every field to numeric if it is already a numeric field. Any idea why this is occurring and how to fix globally rather than explicitly converting each field to numeric after the recordset is returned?

Thanks!

Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image

That is the problem when dealing with variants. What you can do is to add 0 or multiply by 1--that will ensure the variant is set to a numeric type.

FtB
So:

nSent = nSent + objRS("total_sent")*1

should do it.

FtB
Avatar of jdrits
jdrits

ASKER

I tried

nSent = nSent + (objRS("total_sent") + 0)

and

nSent = nSent + (objRS("total_sent") * 1)

and I still get the type mismatch error. If I do the following:

response.write objRS("total_sent")

I get a valid number (38 in this case).

If I do

nSent = nSent + Clng(objRS("total_sent"))

it works. That seems like I should do the Clng conversion, but it isn't feasible later on where I am doing arithmetic on a variety of fields. To convert each one to long is a big waste of resources, not to mention that it shouldn't be necessary to begin with.

I figured a variant conversion shoud occur without the +0 or *1 because I am already doing a mathematical function on the field, but the above tests show something else is wrong here.

Thanks for your help. I really appreciate your taking the time. It just looks like there's something else I may be overlooking.
I just want to make sure that I understand:


Response.write(objRS("total_sent"))            'this works and prints out 38
Response.write(objRS("total_sent")*1)         'this also works and prints out 38

TypeName

strTotalSent= objRS("total_sent")
response.write("strTotalSent: " & TypeName(strTotalSent))

numTotalSent = objRS("total_sent")*1
response.write("numTotalSent: " & TypeName(numTotalSent))

Also, I don't suppose that objRS.Fields("total_sent").value works any better?

FtB
Avatar of jdrits

ASKER

Response.write(objRS("total_sent"))  works.

Response.write(objRS("total_sent")*1) generates a type mismatch error.

The code:

strTotalSent= objRS("total_sent")
response.write("strTotalSent: " & TypeName(strTotalSent))

gives the following error:

Variable uses an Automation type not supported in VBScript: 'TypeName'

objRS.Fields("total_sent").value gives the same.

This is a weird one for sure.
jdrits,

The correct format is to use the CLng function to convert it to a number before or while you do the arithmetic. ADO RecordSet's are by natural a fancy "Collection." That being said the Collection by natural only store the data returned as String(Character) format. The only way that you would be able to return a numeric format would be to run the information through a Stored Procedure which allows you to specify the format that the data will be returned.

The reason you can use the Response.Write and have it display as 38 is that 3 and 8 are valid characters so no data type conversion is necessary since Response.Write writes character to the Response stream.

So like fritz_the_blank said you will need to utilize the CLng function to do your arithmetic. What you may want to do is to store the data in that Numeric format in a variable within your code so that it can be utilize like numeric data later in your code.

LaRell
Set to variable first:

s = objRS("total_sent")
nSent = nSent + (s)

OR

s = objRS("total_sent")
nSent = nSent + cint(s)
s = cint(objRS("total_sent"))
nSent = nSent + s
I am somewhat confused here, however, because I have performed arithmetical operations on recordset objects without having to explicity type cast them.

FtB
>>s = cint(objRS("total_sent"))
>>nSent = nSent + s

Huh?  That the same thing I just did...
Here is my sample code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
</HEAD>

<BODY>
<%
dim strDataPath, objConnection
strDataPath = SErver.MapPath("family.mdb")
set objConnection=Server.CreateObject("ADODB.Connection")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
       + " Data Source= " & strDataPath & ";"_
       + " Mode=Share Deny None;User Id=admin;PASSWORD=;"

objConnection.ConnectionTimeout = 15
objConnection.CommandTimeout =  10
objConnection.Mode = 3 'adModeReadWrite
if objConnection.state = 0 then
  objConnection.Open strConnectString
end if

dim strSQL, objRS

strSQL= "SELECT intID FROM Person2"
set objRS=Server.CreateObject("ADODB.RecordSet")
objRS.Open strSQL,objConnection,3,3
response.write(objRS("intID") & "<BR>")  'First record returns value of 1
response.write(objRS("intID")*2 & "<BR>") 'this calculation returns a 2
response.write(objRS("intID")+8 & "<BR>") 'this calculation returns a 9
objRS.Close
set objRS = Nothing
objConnection.close
set objConnection = Nothing

%>


</BODY>
</HTML>


and here is the output:

1
2
9


As you can see, I am not doing any type casting.

FtB
I'm sure his datatypes are not standard...most likely.
>>I'm sure his datatypes are not standard...most likely.<<

I am not sure that I understand--there are a limited number of data types unless s/he is using a non-standard DBMS that works with ADO?

@jdrits:

Is it possible for you to recreate my page with your DBMS for testing purposes? Again, I have done the above in many cases without issue.

FtB
Connecting through DSN, so who knows? :)
ASKER CERTIFIED SOLUTION
Avatar of Thogek
Thogek
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or, put differently:
T-SQL bigint is a 64-bit integer.

VBScript Byte is a 8-bit integer.
VBScript Integer is a 16-bit integer.
VBScript Long is a 32-bit integer.

VBScript does not have a defined 64-bit integer type (which a T-SQL bigint is), and so may get confused when asked to intepret one.
Avatar of jdrits

ASKER

Thogek is right. I tried the same code with a field of type int and it worked. When I switched back to bigint, it failed.

Thanks for everyone's help, especially fritz. I knew that I could explicity to a Clng to make it work, but I didn't want to do that for 23 numerical fields that I was manipulating arithmatically.
>>Thanks for everyone's help, especially fritz. <<

That is what the split button was for!!!


Well, at least you have your problem solved.

FtB
Well Fritz, I said it was a data type problem, and you put in hours of your time, and neither was worth it!  Se la vi'...same old EE
Avatar of jdrits

ASKER

Fritz and alorentz, I didn't mean to exclude you, but I felt it was important to award the points to the person who actually solved the problem.

What I usually do in these circumstances is to put up a new "question" for the two of you to post to, then split a new set of points between you.

So please go to http://oldlook.experts-exchange.com/questions/21127529/Fritz-the-blank-and-alorentz-please-post-here-so-I-can-award-you-the-points-for-your-help.html to claim your points.

:)
Avatar of jdrits

ASKER

Sorry folks, looks like I can't do that. Oh well.

Rules, rules rules...
Just remember it for next time! ;)
Avatar of jdrits

ASKER

I will. Thanks for your understanding.