using cdbl works when dsn-less but throws error with dsn

[cr84net2]

Is there a problem using cdbl with a dsn? I have a Select Case statment tht works fine on iis with a dsnless connection but throws this error once it is online with a dsn:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Invalid use of Null

/listings.asp, line 158

This is the Select Case Statement:

Case 3
Sql="SELECT ID, thumbpic, proptype, propaddress, propprice, city, salestatus, propdesc, propdisdat, propactive FROM listings WHERE propactive=" & "'Yes'" & " AND CDBL(propprice) BETWEEN " & CDBL(sbprice) & " AND " & CDBL(stprice) & " ORDER BY ID Desc"

I know a number field in the db would have been better but I am dealing with the hand I was dealt.

Any ideas why this is happening? Thanks in advance

 

[ChrisHirst]

are your variables sbprice & stprice initialised and set with a value?




Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[cr84net2]

yes, like this:

stprice=(Trim(Request.form("tprice")))
sbprice= (Trim(Request.form("bprice")))

Sorry, should have included that

 

[ChrisHirst]

And are you checking for them having a value after being retrieved from the form collection?

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[cr84net2]

Yes and both have values

I also just tried a dsn-less connection online and am receiving this error:

Microsoft JET Database Engine error '80040e14'

Invalid use of Null

So my first post stating that the error has something to do with switching to a dsn is incorrect. This is an issue between the iis server on my box and the online server.

 

[ChrisHirst]

response.write the two variable casts to see WHICH is causing the error message

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[cr84net2]

I did a response.write of the sql statement and it shows proper values for sbprice and stprice. It just displays CDBL(propprice) so I am not sure how to check the field from the db since it throws an error when I try to run the recordset. This appears that the CDBL(propprice) is the culprit except that I am certain there is a value in the db and it works fine on my box. Thanks for your help.

 

[ChrisHirst]

The CDBL(propprice) code is being executed by the SQL server NOT in ASP so will show as SQL code in the statement.


What happens when you take the written out string and paste it into Access as a query?



Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[cr84net2]

I am so sorry to have wasted your time. I just found out that the clients staff input several properties and neglected to input the prices in 3 of them. This obviously caused the error. I am terribly sorry to have wasted your time but it did illuminate a potential error being thrown that has now been solved. I am very very sorry but I had no way of knowing this had been done. I really appreciate all of your help.

 

[ChrisHirst]

Not a problem.

So long as you learn something along the way, it has been a useful exercise.


Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.