type mismatch when comparing two numeric values from SQL query 1

[cdck]

I am pulling a numeric field (rtTot) from a SQL table in a query. In the process of a loop on my output page, I am attempting to compare what the value was during the preceding loop to what it is during the current loop, and create output based on which is larger. However, I get the error:

"Microsoft VBScript runtime error '800a000d'
Type mismatch
/Quality_Documents/AVLdet.asp, line 118 "

Below is the code; line 118 is the line which i have marked here with >>>>>
[tt]
rtCount = 0
rtTot = 0

set rstemp= Con.Execute(rateSQL)

response.write("<TR><TD colspan=2><CENTER><TABLE cellpadding=2>")

DO UNTIL rstemp.eof

NewTot = rstemp(5)

>>>>>If rtCount = 0 Then rtDiff = "" Else If rtTot > NewTot Then rtDiff = "(+)</CENTER></TD></TR>" Else If rtTot < NewTot Then rtDiff = "(-)</CENTER></TD></TR>" Else rtDiff = "(.)</CENTER></TD></TR>" End If

response.write(rtDiff)

rtStart = rstemp(0)
rtEnd = rstemp(1)
rtOD = rstemp(2)
rtQP = rstemp(3)
rtR = rstemp(4)
rtTot = rstemp(5)


response.write("<TR><TD>" & rtStart & " - " & rtEnd & "</TD><TD><CENTER>" & rtOD & "</CENTER></TD><TD><CENTER>" & rtQP & "</CENTER></TD><TD><CENTER>" & rtR & "</CENTER></TD><TD><CENTER>" & rtTot & "</CENTER></TD><TD><CENTER>")

rtCount = rtCount +1
rstemp.movenext
Loop
[/tt]

I have tried removing the section of the if statement that looks at rtCount, with no effect. I also temporarily put in a statement having it output the values of rtTot and NewTot, so that I could verify that values did exist for both, which was true.

Based on the error, I would believe that I am trying to do a mathematical comparison to values which are not numeric, yet I have confirmed that they are numeric fields in the SQL data. Can anyone help me determine where to look next for my error?

Cheryl dc Kern

 

[markdmac]

use TypeName to determine what your script thinks it has. here is an example:

a = "123"
b = 123

WScript.Echo TypeName(a)
WScript.Echo TypeName(b)

output for the above is:
String
Integer

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[cdck]

I get this error when i try to do that:

Variable uses an Automation type not supported in VBScript: 'TypeName'

This is how I tired to use it:

WScript.Echo TypeName(NewTot)
WScript.Echo TypeName (rtCount)

Cheryl dc Kern

 

[markdmac]

Context is correct. See documentation here:

http://www.devguru.com/technologies/vbscript/17439.asp.

Did you execute via Wscript.Exe or CScript? Or from within a web page?


I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[tsuji]

[0] >yet I have confirmed that they are numeric fields in the SQL data.
I hope you are right.

[1] >NewTot = rstemp(5)
Replace it by this.
[tt]
NewTot=rstemp(5).value
on error resume next
NewTot=cdbl(NewTot)
if err.number<>0 then
response.write hex(err.number) & "<br />" & err.description" & "<br />" & _
"NewTot: " & NewTot
response.end
end if
on error goto 0
[/tt]
[2] Though it is not a source of error not to specify the .value (and that the conversion from fields type to its default .value is automatic), adding .value systematically, you would have one factor less to worry about. The same for all the assignment of value thereafter:
[tt]
rtStart = rstemp(0)[blue].value[/blue]
'etc etc
rtTot = rstemp(5)[blue].value[/blue]
[/tt]
[2.1] If you ever work with js, one admissible language in place of vbs, .value is absolutely necessary.

 

[tsuji]

addenda
[3] I am not sure what db you are connected to. It suffices to note that most db supports fields of data types that are outside of automation types vbs supports. As an example of fixed length numeric data type, such as decimal(10,2) etc etc. Those types are not automation types supported by vbs. Hence, there is one more reason to convert the field value before proceeding to further manipulation. I mean the line of this kind.
[tt] NewTot=cdbl(NewTot)[/tt]
Same note applies to those lines assigning values to variables in [2].

 

[cdck]

tsuji:

Thank you so much for your help; the code you offered did the trick, and my results look good!

1/30/2009 - 4/30/2009 0 5 0 12 (-)
1/2/2009 - 3/25/2009 0 5 9 35 (-)
1/2/2008 - 5/1/2008 8 2 10 70

markdmac:

I apologize for not being clear; the code is embedded in a web page, with the output being in html.

Thanks again, all.


Cheryl dc Kern