data type mismatch problem

[Guest]

Hello:

I have an sql query that works just fine when I run it directly in Access but when I run it on a web site, it gives me an error "Data type mismatch in criteria expression".

I am working with transactions and this is an update of a table within a transaction.

Here is the code:

con.execute "UPDATE tblWells SET API_Number='" & Request.Form("API_Number") & "', Well_Name='" & Request.Form("Well_Name") & "', Drill='" & Request.Form("Drill") & "', Reenter='" & Request.Form("Reenter") & "', horizontal='" & Request.Form("horizontal")& "', Duration='" & Request.Form("Duration") & "', OilWell='" & Request.Form("OilWell") & "', GasWell='" & Request.Form("GasWell") & "', Other='" & Request.Form("Other") & "', SingleZone='" & Request.Form("SingleZone") & "', MultipleZone='" & Request.Form("MultipleZone")& "', NumAcresLease='" & Request.Form("NumAcresLease") & "', LeaseSerialNumber='" & Request.Form("LeaseSerialNumber") & "', LeaseName='" & Request.Form("LeaseName") & "', WellNumber='" & Request.Form("WellNumber") & "', state='" & Request.Form("state") & "', county='" & Request.Form("county") & "', wellheadElevation='" & Request.Form("wellheadElevation") & "', groundElevation='" & Request.Form("groundElevation") & "', ProposedDepth='" & Request.Form("ProposedDepth") & "', DistanceTownPostOffice='" & Request.Form("DistanceTownPostOffice") & "', DirectionTownPostOffice='" & Request.Form("DirectionTownPostOffice") & "', Distance_Nearest_Property_Line='" & Request.Form("Distance_Nearest_Property_Line") & "', DistanceNearestWell='" & Request.Form("DistanceNearestWell") & "', UnitSpacing='" & Request.Form("UnitSpacing") & "' WHERE WellID=1"

I can't figure why the query would work fine in Access and not on the webpage.

Thanks.

[Guest]

Okay, I have worked further. I have isolated the fields that are causing trouble and they are checkbox fields.

I have been pulling my hair out all day trying to figure out how to set access and asp so that the on/off, true/false or whatever is passed correctly.

Can anyone help?

Dave

[Pogina]

Hello! Zavidp,

You may want to see if the information you are passing accept null, or passing a text in a value field.
I would limit my query one line at a time until you can find the culprit.

Good luck,

Pogina

[Newsgroup_User]

A Checkbox does not exist as a form value if it empty which is why your SQL
script is failing. What you need to do is create a variable and then assign
it with the value. Assuming you are using ASP then taking one element on
your SQL statement

SingleZone='" & Request.Form("SingleZone")

you would change it to

SingleZone = strSingleZone

Above the actual start of your SQL statement you would define your variables
and set their empty values, and then assign them to the form values

Dim strSingleZone = 0

if Request.Form("SingleZone") <> "" then
strSingleZone = Request.Form("SingleZone")
End if


--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"zavidp" <webforumsuser@macromedia.com> wrote in message
news:eo9554$dd0$1@forums.macromedia.com...
> Okay, I have worked further. I have isolated the fields that are causing
> trouble and they are checkbox fields.
>
> I have been pulling my hair out all day trying to figure out how to set
> access
> and asp so that the on/off, true/false or whatever is passed correctly.
>
> Can anyone help?
>
> Dave
>