Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Unanswered: ASP/SQL problem -- save always sets value to TRUE

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Moving thread to SQL Forum...

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

  3. #3
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Might be a trigger on the table? have you checked for an update trigger that could be doing this?

  4. #4
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Problem Solved...

    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.

  5. #5
    Join Date
    Dec 2003
    Posts
    454
    It should work with radio button too.

  6. #6
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    It SHOULD work...

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •