Solved

Database Query Returns Numbers as Text With ADO

Posted on 2004-09-10
25
820 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

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 would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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