Solved

Database Query Returns Numbers as Text With ADO

Posted on 2004-09-10
25
818 Views
Last Modified: 2008-02-01
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!

0
Comment
Question by:jdrits
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 6
  • +2
25 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12031827
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
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12031830
So:

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

should do it.

FtB
0
 
LVL 2

Author Comment

by:jdrits
ID: 12031888
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12031939
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))

0
 
LVL 46

Expert Comment

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

FtB
0
 
LVL 2

Author Comment

by:jdrits
ID: 12032010
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.
0
 
LVL 2

Expert Comment

by:larellnielsen
ID: 12032030
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
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12032038
Set to variable first:

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

OR

s = objRS("total_sent")
nSent = nSent + cint(s)
0
 
LVL 2

Expert Comment

by:larellnielsen
ID: 12032071
s = cint(objRS("total_sent"))
nSent = nSent + s
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12032093
I am somewhat confused here, however, because I have performed arithmetical operations on recordset objects without having to explicity type cast them.

FtB
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12032105
>>s = cint(objRS("total_sent"))
>>nSent = nSent + s

Huh?  That the same thing I just did...
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12032116
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
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12032212
I'm sure his datatypes are not standard...most likely.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12032246
>>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
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12032268
Connecting through DSN, so who knows? :)
0
 
LVL 15

Accepted Solution

by:
Thogek earned 500 total points
ID: 12032960
Clueless question, here, but...
Any chance that the T-SQL bigint data type simply doesn't match any of the numeric data types VBScript recognizes, and thus needs to be cast into a type recognized by VBScript before VBScript can use it?

T-SQL bigint: http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_3ss4.asp
"Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). Storage size is 8 bytes."

VBScript Data Types: http://msdn.microsoft.com/library/en-us/script56/html/vbsdatatype.asp
"Byte: Contains integer in the range 0 to 255."
"Integer: Contains integer in the range -32,768 to 32,767."
"Long: Contains integer in the range -2,147,483,648 to 2,147,483,647."

I could be missing it, but... I don't see a match between the two -- which would explain VBScript's not knowing what to do with a bigint data type, until it is cast as one of the types it does recognize.
0
 
LVL 15

Expert Comment

by:Thogek
ID: 12033011
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.
0
 
LVL 2

Author Comment

by:jdrits
ID: 12034829
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.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12034839
>>Thanks for everyone's help, especially fritz. <<

That is what the split button was for!!!


Well, at least you have your problem solved.

FtB
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12034928
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
0
 
LVL 2

Author Comment

by:jdrits
ID: 12035642
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:8080/Web/Web_Languages/ASP/Q_21127529.html to claim your points.

:)
0
 
LVL 2

Author Comment

by:jdrits
ID: 12035684
Sorry folks, looks like I can't do that. Oh well.

Rules, rules rules...
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12036815
Just remember it for next time! ;)
0
 
LVL 2

Author Comment

by:jdrits
ID: 12036855
I will. Thanks for your understanding.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question