• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 831
  • Last Modified:

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!

0
jdrits
Asked:
jdrits
  • 8
  • 6
  • 6
  • +2
1 Solution
 
fritz_the_blankCommented:
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
 
fritz_the_blankCommented:
So:

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

should do it.

FtB
0
 
jdritsAuthor Commented:
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
Independent Software Vendors: 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!

 
fritz_the_blankCommented:
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
 
fritz_the_blankCommented:
Also, I don't suppose that objRS.Fields("total_sent").value works any better?

FtB
0
 
jdritsAuthor Commented:
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
 
larellnielsenCommented:
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
 
alorentzCommented:
Set to variable first:

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

OR

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

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

Huh?  That the same thing I just did...
0
 
fritz_the_blankCommented:
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
 
alorentzCommented:
I'm sure his datatypes are not standard...most likely.
0
 
fritz_the_blankCommented:
>>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
 
alorentzCommented:
Connecting through DSN, so who knows? :)
0
 
ThogekCommented:
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
 
ThogekCommented:
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
 
jdritsAuthor Commented:
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
 
fritz_the_blankCommented:
>>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
 
alorentzCommented:
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
 
jdritsAuthor Commented:
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
 
jdritsAuthor Commented:
Sorry folks, looks like I can't do that. Oh well.

Rules, rules rules...
0
 
alorentzCommented:
Just remember it for next time! ;)
0
 
jdritsAuthor Commented:
I will. Thanks for your understanding.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 8
  • 6
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now