changing a checkbox value in my database by using UPDATE

[zwanz P via AccessMonster.com]

I got the following code on my form page:

<%
Dim Connection
Dim Recordset
Dim SQL
Dim admincheck
Dim admincheck2
Dim uitgebreidcheck
Dim uitgebreidcheck2

Set Recordset = Server.CreateObject("ADODB.Recordset")
Set Connection = Server.CreateObject("ADODB.Connection")

Call Connection.Open("DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("HelpdeskI&A.mdb"))

SQL = "SELECT * FROM tbl_klanten WHERE naam = '" & Request.Form("edtcontactpersoon") & "' "
Call Recordset.Open(SQL, Connection)

admincheck = CBool(Recordset("admin"))
uitgebreidcheck = CBool(Recordset("uitgebreid"))


If admincheck = True Then
admincheck2 = 1
Else
admincheck2 = 0
end if

If uitgebreidcheck = True Then
uitgebreidcheck2 = 1
Else
uitgebreidcheck2 = 0
end if

%>


There are 2 checkboxes:

<input type="checkbox" name="edtuitgebreid" value="<%=CBool(uitgebreidcheck2)%>"<%if CBool(uitgebreidcheck2) Then Response.Write "checked" Else Response.Write "unchecked"%>>

and

<input type="checkbox" name="edtadmin" value="<%=CBool(admincheck2)%>"<%if CBool(admincheck2) Then Response.Write "checked" Else Response.Write "unchecked"%>>


When i submit my form, the following asp code will be launched:


<%
Dim Connection
Dim Recordset
Dim SQL
Dim admincheck
Dim uitgebreidcheck

Set Recordset = Server.CreateObject("ADODB.Recordset")
Set Connection = Server.CreateObject("ADODB.Connection")


Call Connection.Open("DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("HelpdeskI&A.mdb"))

admincheck = CBool(Request.Form("edtadmin"))
uitgebreidcheck = CBool(Request.Form("edtuitgebreid"))

SQL = "UPDATE tbl_klanten SET username = '" _
& Request ("edtusername") & "', " & _
"admin = '" & ("admincheck") & "', " & _
"uitgebreid = '" & uitgebreidcheck & "', " & _
"password = '" & Request ("edtpassword") & "' WHERE " & _
"tbl_klanten.naam = '" & Request ("edtcontactpersoon") & "'"


Call Recordset.Open(SQL, Connection)


%>


Unfortunately something goes wrong, i get the following error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access-stuurprogramma] Gegevenstypen komen niet overeen in criteriumexpressie.
/Website/beheer_gebruiker_aanpassen_query.asp, line 30

POST Data:
edtcontactpersoon=Prijs&edtusername=P670850&edtpassword=5t3bmtrp&edtuitgebreid=True&edtadmin=True&verzenden=Verstuur

(in english = TYPE mismatch )
Something goes wrong with the checkboxes and their values (1 or true or..) so they cannot be updated in the database but i dont know what ;(. In the database both columns are (yes/no) checks

Any help is appreciated

 

[Rick B]

Very very long post. I did not read all of it. Way too much information.

But, one thing I notice is that a checkbox/true false/boolean field will
contain the value of -1 for true, not 1 and 0 for false.

Hope that helps,

Rick B

I got the following code on my form page:

<%
Dim Connection
Dim Recordset
Dim SQL
Dim admincheck
Dim admincheck2
Dim uitgebreidcheck
Dim uitgebreidcheck2

Set Recordset = Server.CreateObject("ADODB.Recordset")
Set Connection = Server.CreateObject("ADODB.Connection")

Call Connection.Open("DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("HelpdeskI&A.mdb"))

SQL = "SELECT * FROM tbl_klanten WHERE naam = '" &

Request.Form("edtcontactpersoon") & "' "

Call Recordset.Open(SQL, Connection)

admincheck = CBool(Recordset("admin"))
uitgebreidcheck = CBool(Recordset("uitgebreid"))


If admincheck = True Then
admincheck2 = 1
Else
admincheck2 = 0
end if

If uitgebreidcheck = True Then
uitgebreidcheck2 = 1
Else
uitgebreidcheck2 = 0
end if

%>


There are 2 checkboxes:

<input type="checkbox" name="edtuitgebreid"

and

<input type="checkbox" name="edtadmin" value="<%=CBool(admincheck2)%>"<%if

CBool(admincheck2) Then Response.Write "checked" Else Response.Write
"unchecked"%>>

When i submit my form, the following asp code will be launched:


<%
Dim Connection
Dim Recordset
Dim SQL
Dim admincheck
Dim uitgebreidcheck

Set Recordset = Server.CreateObject("ADODB.Recordset")
Set Connection = Server.CreateObject("ADODB.Connection")


Call Connection.Open("DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("HelpdeskI&A.mdb"))

admincheck = CBool(Request.Form("edtadmin"))
uitgebreidcheck = CBool(Request.Form("edtuitgebreid"))

SQL = "UPDATE tbl_klanten SET username = '" _
& Request ("edtusername") & "', " & _
"admin = '" & ("admincheck") & "', " & _
"uitgebreid = '" & uitgebreidcheck & "', " & _
"password = '" & Request ("edtpassword") & "' WHERE " & _
"tbl_klanten.naam = '" & Request ("edtcontactpersoon") & "'"


Call Recordset.Open(SQL, Connection)


%>


Unfortunately something goes wrong, i get the following error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access-stuurprogramma] Gegevenstypen komen niet overeen in criteriumexpressie.
/Website/beheer_gebruiker_aanpassen_query.asp, line 30

POST Data:
edtcontactpersoon=Prijs&edtusername=P670850&edtpassword=5t3bmtrp&edtuitgebre
id=True&edtadmin=True&verzenden=Verstuur

(in english = TYPE mismatch )
Something goes wrong with the checkboxes and their values (1 or true or..)

so they cannot be updated in the database but i dont know what ;(. In the
database both columns are (yes/no) checks

 

[Randy Balbuena]

I got the following code on my form page:

<%
Dim Connection
Dim Recordset
Dim SQL
Dim admincheck
Dim admincheck2
Dim uitgebreidcheck
Dim uitgebreidcheck2

Set Recordset = Server.CreateObject("ADODB.Recordset")
Set Connection = Server.CreateObject("ADODB.Connection")

Call Connection.Open("DRIVER={Microsoft Access Driver
(*.mdb)};" & _
"DBQ=" & Server.MapPath("HelpdeskI&A.mdb"))

SQL = "SELECT * FROM tbl_klanten WHERE naam = '" &
Request.Form("edtcontactpersoon") & "' "
Call Recordset.Open(SQL, Connection)

admincheck = CBool(Recordset("admin"))
uitgebreidcheck = CBool(Recordset("uitgebreid"))


If admincheck = True Then
admincheck2 = 1
Else
admincheck2 = 0
end if

If uitgebreidcheck = True Then
uitgebreidcheck2 = 1
Else
uitgebreidcheck2 = 0
end if

%>


There are 2 checkboxes:

<input type="checkbox" name="edtuitgebreid"
value="<%=CBool(uitgebreidcheck2)%>"<%if CBool(uitgebreidcheck2) Then
Response.Write "checked" Else Response.Write "unchecked"%>>

and

<input type="checkbox" name="edtadmin" value="<%=CBool(admincheck2)%>"<%if
CBool(admincheck2) Then Response.Write "checked" Else Response.Write
"unchecked"%>>


When i submit my form, the following asp code will be launched:


<%
Dim Connection
Dim Recordset
Dim SQL
Dim admincheck
Dim uitgebreidcheck

Set Recordset = Server.CreateObject("ADODB.Recordset")
Set Connection = Server.CreateObject("ADODB.Connection")


Call Connection.Open("DRIVER={Microsoft Access Driver (*.mdb)};" &
_
"DBQ=" & Server.MapPath("HelpdeskI&A.mdb"))

admincheck = CBool(Request.Form("edtadmin"))
uitgebreidcheck = CBool(Request.Form("edtuitgebreid"))

SQL = "UPDATE tbl_klanten SET username = '" _
& Request ("edtusername") & "', " & _
"admin = '" & ("admincheck") & "', " & _
"uitgebreid = '" & uitgebreidcheck & "', " & _
"password = '" & Request ("edtpassword") & "' WHERE " & _
"tbl_klanten.naam = '" & Request ("edtcontactpersoon") & "'"


Call Recordset.Open(SQL, Connection)


%>


Unfortunately something goes wrong, i get the following error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access-stuurprogramma] Gegevenstypen komen niet
overeen in criteriumexpressie.
/Website/beheer_gebruiker_aanpassen_query.asp, line 30

POST Data:
edtcontactpersoon=Prijs&edtusername=P670850&edtpassword=5t3bmtrp&edtuitgebreid=True&edtadmin=True&verzenden=Verstuur

(in english = TYPE mismatch )
Something goes wrong with the checkboxes and their values (1 or true or..)
so they cannot be updated in the database but i dont know what ;(. In the
database both columns are (yes/no) checks

Any help is appreciated

Zwanz,

You should process checkboxes values as -1 for True, 0 for False, or Null
for Gray or unselected.