Results 1 to 7 of 7
  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)

    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.


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


    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

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

  2. #2
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    hey

    I am looking at the code,

    can you output (response.write) in places i left a * please for better / easier debugging

    *response.write("Before Split MM_fields = " & MM_fields)
    *response.write("Before Split MM_columns = " & MM_columns)

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

    *for x = 1 to ubound(MM_fields)
    *response.write("After Split MM_fields = " & MM_fields)
    *next
    *for x = 1 to ubound(MM_columns)
    *response.write("After Split MM_columns = " & MM_columns)
    *next


    ' 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

    *for x = 1 to ubound(MM_fields)
    *response.write("After Form Values MM_fields = " & MM_fields)
    *next
    *for x = 1 to ubound(MM_columns)
    *response.write("After Form Values MM_columns = " & MM_columns)
    *next

    Hopefully I or you even may catch the error
    Beyond Limitation

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

    Type Mismatch Problem...

    Whew. Took me a few minutes to figure out what you needed me to do. Finally think I got my head around it. Very good idea, by the way, of looking at all the parameters to see what's going on. Clever.

    It works up to this point:

    for x = 1 to ubound(MM_fields)
    response.write("After Split MM_fields = " & MM_fields)
    next
    for x = 1 to ubound(MM_columns)
    response.write("<br>After Split MM_columns = " & MM_columns)
    next
    response.write("<hr>")

    When I run this, it returns this error:

    Error Type:
    Microsoft VBScript runtime (0x800A000D)
    Type mismatch
    /tutors/tutors/_testIsActive2.asp, line 69

    Line 69 refers to MM_fields

    I don't think it's reading the array properly. I did read the strings and they work returning:

    After Split MM_fields = IsActive|value
    After Split MM_columns = IsActive|none,1,0

    Any ideas?

  4. #4
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    TYpo on my end as well


    *for x = 0 to ubound(MM_fields)
    *response.write("After Split MM_fields = " & MM_fields(x) & "<BR>")
    *next
    *for x = 0 to ubound(MM_columns)
    *response.write("After Split MM_columns = " & MM_columns(x) & "<BR>")
    *next


    ' 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

    *for x = 0 to ubound(MM_fields)
    *response.write("After Form Values MM_fields = " & MM_fields(x) & "<BR>")
    *next
    *for x = 0 to ubound(MM_columns)
    *response.write("After Form Values MM_columns = " & MM_columns(x) & "<BR>")
    *next



    Please use that and paste the output again - if an error occurs - just check the array - i might have another typo that i don't see now
    Beyond Limitation

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

    Output from debug code

    Hey man, thanks again. I ran the code and this is the output:

    Before Split MM_fields =
    Before Split MM_columns =

    After Split MM_fields = IsActive|value
    After Split MM_columns = IsActive|none,1,0

    After Split MM_fields = IsActive
    After Split MM_fields = value
    After Split MM_columns = IsActive
    After Split MM_columns = none,1,0
    After Form Values MM_fields = IsActive
    After Form Values MM_fields = False
    After Form Values MM_columns = IsActive
    After Form Values MM_columns = none,1,0


    Gotta run to work now: I'll try to figure out what it means in a little bit.

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

    Still confused...

    Okay, I've examined the error codes and I'm stil not 100% sure what's going on. It looks like the value gets changed (or ignored) when it get submitted.

    Any ideas?

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

    SOLUTION FOUND!

    All I had to do was change the form field to a checkbox, and it works perfectly.

    Thanks for all the help!

Posting Permissions

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