asked on

Data type mismatch error on numeric data from database

Hi all,
This has got me baffled to no end...

I have a numeric column, say TypeID in a table.
I write the query to retrieve the ID into the recordset.

I do a loop and compare the id against 1 -(number one)

It throws a data type mismatch and it goes off if i wrap both of them in a cint or cdbl function.

Any idea why it is giing an error.

BTW.. I'm using sql2k and IIS6

I did dome R&D and found this link..
http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D178043

There is a know issue for oracle db.. but any one knows of such a problem with sql??

Cheers!!

fritz_the_blank

I think that the reason is that the cint or cdbl function is expecting a type of string as a parameter, and if you pass something that has a numeric type, you will get an error.

Fritz the Blank

fritz_the_blank

Actually, I take that back--that doesn't seem to be the case as this shows:

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<SCRIPT LANGUAGE=vbscript>
sub getNumeric(strTextString)
      numConverted = cdbl(strTextString)
      msgbox numConverted
end sub
sub getNumeric2(strTextString)
      numConverted = cdbl(strTextString)
      numConverted=cint(numConverted)
      msgbox numConverted
end sub

</SCRIPT>

</HEAD>
<BODY>
<INPUT type="button" value="Button" id=button1 name=button1 onClick=getNumeric("1")>
<INPUT type="button" value="Button" id=button2 name=button2 onClick=getNumeric2("1")>
</BODY>
</HTML>

fritz_the_blank

would you please post a code sample of where this takes place?

SOLUTION

sybe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gary

Are you comparing against the actual recordset field. Could try assigning it to a variable and then compare that against 1

Göran Andersson

What is the exact data type in the database?

You can check what data type it ends up being in ASP:

vntValue=RecSet("field")
Response.Write TypeName(vntValue)

sybe

Good point Gary.

RS("fieldname") is an object of type "Record".
Try to use RS("fieldname").Value

ap_sajith

ASKER

To answer your questions..
The datatype in the database is numeric and it is an identity column.

sybe, I had tried the vartype on the returned value by assigning it to a variable and it returned type 14 ie, decimal. How is it that 1 is not treated as a decimal??

FYI.. The datatype is numeric and the column is an identity column and is a primary key.

Gary, I had tried assigning the value from the recordset onto a variable. Still gave a type mismatch.

The values from the database are returned as decimal (got that using the vartype function). the values returned are 1,2,3,4 etc.

The code is irrelavant here.. I shall post a sample code for your reference

Set objRS=objConn.Execute ("SELECT Project_ID,Project_Name FROM Projects")

While Not objRS.EOF
If objRS("Project_ID")=1 Then << Gives a type mismatch here!!!!
Response.Write objRS("Project_Name") & "<br>"
Else
Response.Write objRS("Project_Name") & "<br>"
End If
objRS.MoveNext
Wend
Set objRS=nothing

Fritz, I did try my round of testing before posting it here. I had done the same kind of analysis. I was hoping that you guys might have come across this bizzare issue!!

I pray to god that this is not a stupid security feature incorporated in win2003 (like the upload size limit restricted to 2megs by default and you have to scratch your head try and figure out as to why your uploads is not working all of a sudden. And finally when i figured out, i had to edit some config xml file to get it to allow files greater than 2 megs to be uploaded).

Did you guys go through the link that i posted above on the issue with oracle numeric fields?. I dont want to do a work around now... i wanna nail this stupid issue or it might come back to bite me later on.

Thanks for all your comments... keep them coming!!

Cheers!!

fritz_the_blank

To steal from someone else above, this won't fix it:

objRS("Project_ID").value=1

FtB

Anthony Perkins

Actually fritz may be on to something (bear with me and I will get there)

Perhaps I am stating the obvious, but in ASP all variables are variants, so even if the variable was Null you still would not get this error. Even if you were doing something like:

If CInt(X) = 1 Then

and X was Null you would not get a "Type Mismatch" but rather you would get "Invalid use of Null"

Now what happens if X is an object and it is Null, than you would get this error.

This is my point:
Strictly speaking objRS("Project_ID") <> objRS("Project_ID").Value, even though they are both variants the first is an object the second is a variant of type string. (As an aside, thank goodness .NET has doen away with all of this)

So even though you still would get an error either way as fritz pointed out the error, I suspect the error would be different in both cases.

The only other possibility (if it really happens on that line) is if the value of objRS("Project_ID") is not numeric.

If is Numeric and you are using CInt than you could get an "Overflow" error message. But that is another story.

Anthony

Anthony Perkins

I just re-read that and aside from the fact that I cannot type, it is not very clear what I was trying to transmit, so feel free to ask me what I was attempting to say.

Anthony

fritz_the_blank

And to be clear--I stole this from sybe/Gary

FtB

sybe

yeah, i remember Vartype 14 is Decimal. But this is only supported in the more recent versions of VBScript. Maybe the support of Vartype 14 is still only partial. As I said, I remember that doing a TypeName() on a Vartype 14 gave an error (which was really an error in VBScript - any variable should have a typename). Now the support might be partial in the sense that it Decimal cannot be compared with non-decimal numbers.

It is also very interesting that there is no way to "make" a vartype 14 in VBScript. There is no conversion function that will create a "decimal" from some numeric value. Cint, Clng, Cdbl: you can create all kinds of vartypes, but not 14. (well you can't create a Byte() either, except through certain objects, but that's forgivable - VBScript just wasn't meant to work with binaries)

sybe

just for getting confused, try these:

<%Response.write objRS("Project_ID").Type%>

<%Response.write Vartype(objRS("Project_ID")+0)%>

sybe

And finally: i read the link that ap_sajith gave in his question.

The problem is described for Oracle, but it is clear that SQL Server also has that problem. The description on the webpage matches the problems described here exactly. I don't know why MS never found this for SQL Server (or admitted that SQL Server gives the same problems),

Two remarks are interesting:
1.
--------
"For numerics (or the older and equivalent decimal data type) if this conversion does not take place a type mismatch error occurs. "
- - apparently "decimal" is an outdated datatype.

2.
---------
"some ODBC drivers returns a data type of double-precision floating point other drivers more accurately returns numeric data type"

So it might solve the problem is you use another ODBC driver, although it is suggested that "more accurate" drivers do have the problem.

ASKER CERTIFIED SOLUTION

Göran Andersson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ap_sajith

ASKER

Hmmm...

dim pid,ProjectID
pid=-1
response.write vartype(pid) >>Returns 2 -vbInteger
ProjectID=objRS("Project_ID")
response.write vartype(ProjectID) >> Returns 14 - vbDecimal

I think i should just convert the data returned from the database to type integer.

As for objRS("Project_ID").value, Isnt 'value' a default?. Anyway, it doesnt help resolve the issue.

Maybe this is a microsoft conspiracy after all !!

I'll try this out when i'm back at work on a win2k machine as well..

Thank you all for your valuable input. I'll leave this here for a couple of days and close this question on saturday.

Cheers!!

Anthony Perkins

>>As for objRS("Project_ID").value, Isnt 'value' a default?<<
Yes, it is in classic ADO (thankfully .NET has done away with that concept). But if objRS("Project_ID") was Null then

If objRS("Project_ID") = 1 Then ' would result in a "Type mismatch"

while

If objRS("Project_ID").Value = 1 Then ' would result in "Invalid use of null"

Put another way:
VarType(objRS("Project_ID")) <> VarType(objRS("Project_ID").Value)

Anthony

Anthony Perkins

Actually I omitted the significant part. It should have read as follows:

If CInt(objRS("Project_ID")) = 1 Then ' would result in a "Type mismatch"

while

If CInt(objRS("Project_ID").Value) = 1 Then ' would result in "Invalid use of null"

Anthony

Göran Andersson

>> I think i should just convert the data returned from the database to type integer.

It's better to convert the value before you return it from the database:

Set objRS=objConn.Execute ("SELECT cast(Project_ID as int) as Project_ID,Project_Name FROM Projects")

Dexstar

ap_sajith:

What happens when you change this:
If objRS("Project_ID")=1 Then

To this:
If CStr(objRS("Project_ID"))=CStr("1") Then

?

Dex*

ap_sajith

ASKER

Sorry for taking my time with this... Thank you all for your suggestions.

I have Posted points (250 each) for gary,fritz and anthony at

http://oldlook.experts-exchange.com/questions/20804686/Points-for-GaryC123.html - Gary
http://oldlook.experts-exchange.com/questions/20804687/Points-for-fritz-the-blank.html - Fritz
http://oldlook.experts-exchange.com/questions/20804690/Points-for-acperkins.html - Anthony

Cheers!!