Database: MS SQL 2000
OS: Windows Server 2000
ASP version: 3.0
Authoring Tool: Dreamweaver MX

I'm flummoxed. Baffled. Confused.

I'm working on an employee tracking app (timesheets, etc.). On our employee page of our Website, I have a form that tracks name, phone number, etc. There is also a field called IsActive. There is a corresponding column in the database with the same name. This lets us know if it's okay to assign work to this person.

If I edit the field manually in the DB, it works fine, but every time I save the form on the Web page, it changes the value to TRUE. This happens even when I haven't made any changes to this field.

I have created a test page that ONLY updates this one field, so there are no other factors confusing the issue.

Here's some details:

-----------------------------------------------

In the Database table:
Column Name: IsActive
Data Type: Bit
Length: 1
Allow Nulls: (not checked)
Default Value: (0)

-----------------------------------------------

Here's the ASP update code (excluding the connection string and stuff like that):

' *** Update Record: set variables

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then

MM_editConnection = MM_myConnection_STRING
MM_editTable = "Tutors"
MM_editColumn = "TutorID"
MM_recordId = "" + Request.Form("MM_recordId") + ""
MM_editRedirectUrl = ""
MM_fieldsStr = "IsActive|value"
MM_columnsStr = "IsActive|none,1,0"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")

' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next


' *** Update Record: construct a sql update statement and execute it

If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "") Then

' create the sql update statement
MM_editQuery = "update " & MM_editTable & " set "
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_editQuery = MM_editQuery & ","
End If
MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
Next
MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId

If (Not MM_abortEdit) Then
' execute the update
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If

-----------------------------------------------
-----------------------------------------------
-----------------------------------------------

I suspect the problem lies in these lines of code (or in my DB settings):

MM_fieldsStr = "IsActive|value"
MM_columnsStr = "IsActive|none,1,0"


I have tried changing the variables to other settings, but these have not worked (all but the first set return errors):
-1,0
True,False
Yes,No
Y,N

I've also noticed that if I change the order to 0,1, the record is always saved as FALSE.

Any ideas on what the problem is and how to fix it?

All help is greatly appreciated -- I'm really at my wit's end on this one.

I guess this is more of an SQL question, so I'm reposting over in that forum.

Might be a trigger on the table? have you checked for an update trigger that could be doing this?

I solved this a long time ago and posted the answer in the SQL forum, but I guess I should answer it here, too.

It turns out that the problem was coming from the radio button. I switched the input method to a check box and it started working correctly.

It should work with radio button too.

Yes, you are right. It SHOULD work... but it didn't. I worked on it for a very long time, and even had people here working on it (more over in the SQL Forum). Maybe it's something funny in the way Dreamweaver MX writes code (which I modified), or maybe it's a weird SQL issue. But, the reality is, I changed the input type and it started working. Nothing else did.

I just wanted to post the solution I found in case someone else came looking for an answer in a similar situation.