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 .Connectio n")
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!
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
Set objIncRS = Server.CreateObject("ADODB
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!
So:
nSent = nSent + objRS("total_sent")*1
should do it.
FtB
nSent = nSent + objRS("total_sent")*1
should do it.
FtB
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.
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("tota l_sent")) 'this works and prints out 38
Response.write(objRS("tota l_sent")*1 ) 'this also works and prints out 38
TypeName
strTotalSent= objRS("total_sent")
response.write("strTotalSe nt: " & TypeName(strTotalSent))
numTotalSent = objRS("total_sent")*1
response.write("numTotalSe nt: " & TypeName(numTotalSent))
Response.write(objRS("tota
Response.write(objRS("tota
TypeName
strTotalSent= objRS("total_sent")
response.write("strTotalSe
numTotalSent = objRS("total_sent")*1
response.write("numTotalSe
Also, I don't suppose that objRS.Fields("total_sent") .value works any better?
FtB
FtB
ASKER
Response.write(objRS("tota l_sent")) works.
Response.write(objRS("tota l_sent")*1 ) generates a type mismatch error.
The code:
strTotalSent= objRS("total_sent")
response.write("strTotalSe nt: " & 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.
Response.write(objRS("tota
The code:
strTotalSent= objRS("total_sent")
response.write("strTotalSe
gives the following error:
Variable uses an Automation type not supported in VBScript: 'TypeName'
objRS.Fields("total_sent")
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
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 = objRS("total_sent")
nSent = nSent + (s)
OR
s = objRS("total_sent")
nSent = nSent + cint(s)
s = cint(objRS("total_sent"))
nSent = nSent + s
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
FtB
>>s = cint(objRS("total_sent"))
>>nSent = nSent + s
Huh? That the same thing I just did...
>>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.Creat eObject("A DODB.Conne ction")
strConnectString = "Provider=Microsoft.Jet.OL EDB.4.0;"_
+ " Data Source= " & strDataPath & ";"_
+ " Mode=Share Deny None;User Id=admin;PASSWORD=;"
objConnection.ConnectionTi meout = 15
objConnection.CommandTimeo ut = 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.Rec ordSet")
objRS.Open strSQL,objConnection,3,3
response.write(objRS("intI D") & "<BR>") 'First record returns value of 1
response.write(objRS("intI D")*2 & "<BR>") 'this calculation returns a 2
response.write(objRS("intI D")+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
<!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.Creat
strConnectString = "Provider=Microsoft.Jet.OL
+ " Data Source= " & strDataPath & ";"_
+ " Mode=Share Deny None;User Id=admin;PASSWORD=;"
objConnection.ConnectionTi
objConnection.CommandTimeo
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(
objRS.Open strSQL,objConnection,3,3
response.write(objRS("intI
response.write(objRS("intI
response.write(objRS("intI
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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. 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
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
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.
:)
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.
:)
ASKER
Sorry folks, looks like I can't do that. Oh well.
Rules, rules rules...
Rules, rules rules...
Just remember it for next time! ;)
ASKER
I will. Thanks for your understanding.
FtB