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..

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

LVL 21
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Actually, I take that back--that doesn't seem to be the case as this shows:

<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
sub getNumeric(strTextString)
      numConverted = cdbl(strTextString)
      msgbox numConverted
end sub
sub getNumeric2(strTextString)
      numConverted = cdbl(strTextString)
      msgbox numConverted
end sub


<INPUT type="button" value="Button" id=button1 name=button1 onClick=getNumeric("1")>
<INPUT type="button" value="Button" id=button2 name=button2 onClick=getNumeric2("1")>
would you please post a code sample of where this takes place?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

SELECT @@IDENTITY returns a numeric type with Vartype = 14 in SQL Server.
This Vartype was non-existing is some older versions of IIS - i believe it is supported now. Doing a TypeName() on it would give an error. Since I found out I always do a CLng() on the return value of SELECT @@IDENTITY.

There are several "numeric" types, and type 14 still seems to be a strange duck between them.
Are you comparing against the actual recordset field.  Could try assigning it to a variable  and then compare that against 1
Göran AnderssonCommented:
What is the exact data type in the database?

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

Response.Write TypeName(vntValue)
Good point Gary.

RS("fieldname") is an object of type "Record".
Try to use RS("fieldname").Value
ap_sajithAuthor Commented:
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>"
Response.Write objRS("Project_Name") & "<br>"
End If
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!!

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


Anthony PerkinsCommented:
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 PerkinsCommented:
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.

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

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)
just for getting confused, try these:

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

<%Response.write Vartype(objRS("Project_ID")+0)%>
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:
"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.

"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.
Göran AnderssonCommented:
I always use int for identifiers. The equivalent in VBScript is Long. There is no equivalent for numeric in VBScript.

Try to convert the value when you retrieve it:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ap_sajithAuthor Commented:

dim pid,ProjectID
response.write vartype(pid) >>Returns 2 -vbInteger
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.

Anthony PerkinsCommented:
>>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"


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 PerkinsCommented:
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"


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

Göran AnderssonCommented:
>> 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")

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

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


ap_sajithAuthor Commented:
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 - Gary - Fritz - Anthony

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.