ASP/SQL Help

[Jator]

I'm trying to design a page that pulls info from an Access database into the ASP page. I've gotten earlier versions to work, such as this Pulls the first 300 records from the database. , but when I tried to create a form page to pull the info, it gives me the following error

<< [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. >>

Here is the page if you want to look at it. Just type in 66984 in the form box and hit submitt. All of this is based off a book I recently bough that gives examples. The example I am trying to go off of is This One which does work (put 3 in the form field and hit submitt).

Here is the code from my ASP page trying to pull the record based off the info from the Form page:
<%
varCPT1= request.form(&quot;CPT_Code&quot
response.write varCPT1 'for testing
dim ORSp
Set oRSp=server.createobject(&quot;ADODB.recordset&quot
sqltext=&quot;Select * from Med_2000 &quot;
sqltext=sqltext&amp; &quot; WHERE CPT=&quot; &amp; varCPT1 &amp; &quot;;&quot;
oRSp.open sqltext, &quot;dsn=Medicare&quot;
%>
Any help would be greatly appreciated.

Jay

 

[Mika]

Hi Jator,

Often when you get type mismatch errors in ASP, it is caused by the variable type being used.

What type of field is &quot;CPT&quot; in your access database? If it is some number type, then try adding this before you set your SQL string.

varCPT1 = cLng(varCPT1)

That will convert the variable into a Long Int. (or use cDbl or whatever else is necessary).

Good Luck!

Mika

 

[Mika]

Also, are there any other differences between your code and the sample code? What about the databases that are being hit?

 

[Train]

i had this same problem the other day (actually i have it all the time doing asp) its just a matter of making sure your database tpyes and asp types match up ok.

 

[Jator]

Mika/Train,

The cell type is Text in Access. It's got alpha based text as well as numeric. I'll try your suggestion.

Jay

 

[Jator]

Nope, didn't work. it's pulling off of a different Access database, but I know it works because the sql2.asp page work. Any other ideas?

Mika...do you use ICQ? If so, I would love to pin you down on it and ask you about 10 questions.

Jay

 

[Train]

if the cell is Text, you must surround it with single quotes in the SQL string, change your SQL line to this:

sqltext=sqltext&amp; &quot; WHERE CPT='&quot; &amp; varCPT1 &amp; &quot;';&quot;

 

[Jator]

Train, I could kiss you. Another question, how do I get 724.9 to show as $724.90 (or just 724.90). This is supposed to be in currancy, just doesn't look right without the 0 at the end.

Thanks.

Jay

 

[BoberFett]

I'm not too experienced with ASP, just enough to get by. Look for a Format function. In VB there's a function that formats numbers into whatever you want. There might even be a constant to put into currency format automatically.

Format(724.9, &quot;$###,###,##0.00&quot

Format(fAmount, constCurrency)

If that function exists, it should have a Constants list.

 

[Train]

Boberfett is right, most functions available in VB are available in VBScript(ASP), i think the one you need is Format() im not sure, might be FormatString() or there might even be another one designed for currency formats. check out http://msdn.microsoft.com for vbscript function listings

 

[Chad]

Use ColdFusion instead.

j/k (even though I prefer it).