Passing checkbox value to Access 2k back end 2

[cruford]

I have this code to update my DB records:


ElseIf Len(Request.Form("cmdUpdate&quot) > 0 Then
'Update record
If Request.Form("chkFloppy&quot = "on" Then
strFloppy = "yes"
Else
strFloppy = "no"
End If
If Request.Form("chkNIC&quot = "on" Then
strNIC = "yes"
Else
strNIC = "no"
End If
If Request.Form("chkCDRom&quot = "on" Then
strCD = "yes"
Else
strCD = "no"
End If
If Request.Form("chkBurner&quot = "on" Then
strBurner = "yes"
Else
strBurner = "no"
End If
If Request.Form("chkModem&quot = "on" Then
strModem = "yes"
Else
strModem = "no"
End If
strSQL = "UPDATE tblComputers SET Status='" & Request.Form("cboStatus&quot & "', Department='" & Request.Form("txtDept&quot & _
"', Cube='" & Request.Form("txtCube&quot & "', FirstName='" & Request.Form("txtFirstName&quot & "', LastName='" & Request.Form("txtLastName&quot & _
"', CarePak='" & Request.Form("txtCarePak&quot & "', Vendor='" & Request.Form("cboVendor&quot & "', Model='" & Request.Form("txtModel&quot & _
"', Processor='" & Request.Form("txtProc&quot & "', ClockSpeed=" & Request.Form("txtSpeed&quot & ", BIOS='" & Request.Form("txtBIOS&quot & _
"', RAM=" & Request.Form("txtRAM&quot & ", HardDrive=" & Request.Form("txtHD&quot & ", VideoCard='" & Request.Form("txtVideo&quot & "', Modem='" & strModem & _
"', VideoCardMemory=" & Request.Form("txtVidMem&quot & ", MouseManufacturer='" & Request.Form("txtMouse&quot & "', Hub='" & Request.Form("txtHub&quot & "', Port='" & Request.Form("txtPort&quot & _
"', IPAddress='" & Request.Form("txtIP&quot & "', HostName='" & Request.Form("cboHostName&quot & "', OperatingSystem='" & Request.Form("cboOS&quot & _
"', OfficeApplication='" & Request.Form("cboOffApp&quot & "', PurchaseDate='" & Request.Form("txtPurchDate&quot & "', PurchasePrice='" & Request.Form("txtPurchPrice&quot & _
"', Notes='" & Request.Form("txtNotes&quot & "' WHERE SerialNumber='" & Request.Form("txtSerialNum&quot & "'"
strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\INetPub\

http://WWWRoot\dbname.mdb"

Response.Write strSQL 'Debugging to check SQL syntax
Set Conn = Server.CreateObject("ADODB.Connection&quot
Conn.Open strDSN
Conn.Execute strSQL,numRecords,1
If numRecords > 0 Then
Response.Write &quot;<H1>Record Updated</H1>&quot;
Else
Response.Write &quot;<H1>Error Updating Record</H1>&quot;
End If
Conn.Close
Set Conn = Nothing


The little section is blue is a &quot;Yes/No&quot; data field in my Access 2000 database. If I take the check boxes part out it updates the record, as soon as I put it back in it tells me SYNTAX error in SQL statement. Anyone have any work arounds for this?

 

[jemminger]

probably because a checkbox only passes its value when checked. if unchecked, your sql is going to look like

...value, Modem=, VideoCardMemory=value, MouseManufacturer=value,...

i would suggest yes/no radio buttons or select list with yes/no options.

=========================================================
while (!succeed) try();
-jeff

 

[stevedi]

I think that Access stores Yes values as -1 and No values as 0. Try substituting -1 and 0 for &quot;yes&quot; and &quot;no&quot;.

SteveDi

 

[cruford]

Jemm thanks for the response. I went and added:

Response.Write strSQL & &quot;<BR><BR>&quot;'Debugging to check SQL syntax
Response.Write strModem & &quot;<BR>&quot;
Response.Write strNIC & &quot;<BR>&quot;
Response.Write strCD & &quot;<BR>&quot;
Response.Write strBurner & &quot;<BR>&quot;
Response.Write strFloppy & &quot;<BR>&quot;

The values coming back are &quot;yes&quot; and &quot;no&quot; if its checked or not checked respectively. The SQL statement also looks correct. Here is the sample output from above:

UPDATE tblComputers SET Status='Active', Department='AR', Cube='G025', FirstName='Some', LastName='Person', CarePak='', Vendor='Gateway', Model='GP6-400C', Processor='Intel Celeron', ClockSpeed=400, BIOS='', CDROM='yes', CDBurner='no', RAM=128, HardDrive=4, VideoCard='Built-in ATI AGP', Modem='no', NetworkCard='yes', ADrive='yes', VideoCardMemory=8, MouseManufacturer='Microsoft', Hub='', Port='', IPAddress='DHCP', HostName='sperson', OperatingSystem='Windows 98', OfficeApplication='Office 2000 Small Business', PurchaseDate='07/13/1999', PurchasePrice='$881.00', Notes='' WHERE SerialNumber='0123456789'

Modem: no
NIC: yes
CDROM: yes
CD Burner: no
Floppy Drive: yes

Any other ideas?

 

[cruford]

Stevedi,

That worked perfect thanks. Thanks for both the responses.

 

[BDC2]

try using numeric values as stevedi has suggested, or try using true/false instead of 'yes'/'no'. BDC.