Solved

Database Query Returns Numbers as Text With ADO

Posted on 2004-09-10
25
806 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
  • 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now