Solved

Data type mismatch error on numeric data from database

Posted on 2003-10-29
22
4,071 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
  • 5
  • 5
  • 4
  • +4
22 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
Comment Utility
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
Comment Utility
would you please post a code sample of where this takes place?
0
 
LVL 28

Assisted Solution

by:sybe
sybe earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
And to be clear--I stole this from sybe/Gary

FtB
0
 
LVL 28

Expert Comment

by:sybe
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now