Solved

Data type mismatch error on numeric data from database

Posted on 2003-10-29
22
4,092 Views
Last Modified: 2010-08-05
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!!
0
Comment
Question by:ap_sajith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +4
22 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9643863
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
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9643912
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>
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9643935
would you please post a code sample of where this takes place?
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!

 
LVL 28

Assisted Solution

by:sybe
sybe earned 250 total points
ID: 9644091
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.
0
 
LVL 58

Expert Comment

by:Gary
ID: 9644092
Are you comparing against the actual recordset field.  Could try assigning it to a variable  and then compare that against 1
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 9644122
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)
0
 
LVL 28

Expert Comment

by:sybe
ID: 9644141
Good point Gary.

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

Author Comment

by:ap_sajith
ID: 9645364
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!!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9645465
To steal from someone else above, this won't fix it:

 objRS("Project_ID").value=1

FtB
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9645640
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

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9645662
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
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9645671
And to be clear--I stole this from sybe/Gary

FtB
0
 
LVL 28

Expert Comment

by:sybe
ID: 9646373
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)
0
 
LVL 28

Expert Comment

by:sybe
ID: 9646562
just for getting confused, try these:

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

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

Expert Comment

by:sybe
ID: 9646632
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.
0
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 250 total points
ID: 9646776
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")
0
 
LVL 21

Author Comment

by:ap_sajith
ID: 9648216
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!!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9650697
>>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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9650716
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
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 9651010
>> 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")
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9653795
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*
0
 
LVL 21

Author Comment

by:ap_sajith
ID: 9793158
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/Web/Web_Languages/ASP/Q_20804686.html - Gary
http://oldlook.experts-exchange.com/Web/Web_Languages/ASP/Q_20804687.html - Fritz
http://oldlook.experts-exchange.com/Web/Web_Languages/ASP/Q_20804690.html - Anthony

Cheers!!
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question