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

    Unanswered: ASP can't post FALSE value to MS SQL 2000...

    I'm having an odd problem and I almost posted it to the MS SQL forum, but I think it may be a VBS cript/ASP problem, so here goes.

    I'm using ASP 3, VB Script, MS SQL 2000, IIS 5, and I authored the system in Dreamweaver MX.

    I'm creating an employee management system and one of the fields is a dropdown (a SELECT tag) that says whether the employee is active or not. The form field and database fields are creatively named "IsActive".

    The dropdown menu on the page is correctly reading the database: if the DB says False, then the dropdown says False. If the DB says True, then so does the dropdown.

    However, EVERY time I save the page, the database changes the IsActive value to True! I select False from the dropdown, hit Save, and the DB changes to True! I'm completely baffled by this behavior!

    ----------------------------------------------------
    Database Details:
    the Table name is "Tutors" and the field name is "IsActive." The field data type is "bit," the length is "1," and it does not accept Nulls. The default value is "(0)" and there are no other user definable paramters.

    ----------------------------------------------------
    ASP Details:
    Here's the update record code (the IsActive fields are at the end of the strings):

    ' *** Update Record: set variables
    If (CStr(Request("MM_update")) = "tutorsInfo" And CStr(Request("MM_recordId")) <> "") Then

    MM_editConnection = MM_myConnection_STRING
    MM_editTable = "Tutors"
    MM_editColumn = "CustomerTutorID"
    MM_recordId = "" + Request.Form("MM_recordId") + ""
    MM_fieldsStr = "Email2|value|AltPhoneType|value|ServiceItemName|v alue|ServiceItemListID|value|TimeModified|value|Mo dified|value|FirstName|value|MiddleName|value|Last Name|value|Salutation|value|Phone|value|AltPhone|v alue|Email|value|Fax|value|Addr1|value|Addr2|value |Addr3|value|City|value|State|value|PostalCode|val ue|Comments|value|NameOnCheck|value|SSNumber|value |Contact|value|AltContact|value|IsActive|value"
    MM_columnsStr = "Email2|',none,''|AltPhoneType|',none,''|ServiceIt emName|',none,''|ServiceItemListID|',none,''|TimeM odified|',none,''|Modified|none,1,0|FirstName|',no ne,''|MiddleName|',none,''|LastName|',none,''|Salu tation|',none,''|Phone|',none,''|AltPhone|',none,' '|Email|',none,''|Fax|',none,''|Addr1|',none,''|Ad dr2|',none,''|Addr3|',none,''|City|',none,''|State |',none,''|PostalCode|',none,''|Comments|',none,'' |NameOnCheck|',none,''|SSNumber|',none,''|Contact| ',none,''|AltContact|',none,''|IsActive|none,1,0"

    --------------------------------------------
    Here's the HTML/ASP for the dropdown:

    <select name="IsActive" size="1" id="IsActive">
    <option value="True" <%If (Not isNull((RecsetTutors1.Fields.Item("IsActive").Valu e))) Then If ("True" = CStr((RecsetTutors1.Fields.Item("IsActive").Value) )) Then Response.Write("SELECTED") : Response.Write("")%>>True</option>
    <option value="False" <%If (Not isNull((RecsetTutors1.Fields.Item("IsActive").Valu e))) Then If ("False" = CStr((RecsetTutors1.Fields.Item("IsActive").Value) )) Then Response.Write("SELECTED") : Response.Write("")%>>False</option>
    <%
    While (NOT RecsetTutors1.EOF)
    %>
    <option value="<%=(RecsetTutors1.Fields.Item("IsActive").V alue)%>" <%If (Not isNull((RecsetTutors1.Fields.Item("IsActive").Valu e))) Then If (CStr(RecsetTutors1.Fields.Item("IsActive").Value) = CStr((RecsetTutors1.Fields.Item("IsActive").Value) )) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(RecsetTutors1.Fields.Item("IsActive").Value)% ></option>

    --------------------------------------------------
    Note: I think the "While (NOT RecsetTutors1.EOF)" code was provided by Dreamweaver to write the appropriate response into the system. It is necessary to keep the page working, so I can't cut it.

    ALSO: I know this is not the most elegant code around. I also know I should switch to radio buttons to handle this type of data.... but I'm not going to. It's faster for me to validate a dropdown than radio buttons, and this is a private Admin page that has two users. I'm not going to waste a lot of time on a hidden page that is only seen by two people.

    Thanks in advance for the help! I'm really baffled by this!

    Mike Mitchell

  2. #2
    Join Date
    Sep 2003
    Posts
    60

    Re: ASP can't post FALSE value to MS SQL 2000...

    It looks like your option values should be 1 or 0. But to make sure - two things I'd immediately do to determine where the problem is: View Source on the resulting web page to make sure this code renders the option values correctly...

    <option value='1'>True</option>
    <option value='0'>False</option>

    Secondly, in your code, after the SQL is generated to UPDATE the record (but BEFORE it is executed) enter some debug code...

    Response.Write "[" & request.form("IsActive") & "]"
    Response.end

    Code generators - Argh!

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

    Sorry about the True and False...

    Bill,

    Thanks for the help. The "True" and "False" in the code were basically typos. I originally had 1 and 0 then changed them in a desperate attempt to correct the error. It didn't work and I just forgot to change them back. I've done so since.

    Thanks for the Debug Code, though. I inserted it and it returns the appropriate value. A 1 for True and a 0 for False.

    So, does this mean I have a database error? I can change the DB manually (through Enterprise Manager), but every time I save the record, it switches to "True," no matter what data is entered.

    Any more suggestions would be GREATLY appreciated!

  4. #4
    Join Date
    Sep 2003
    Posts
    60

    Re: Sorry about the True and False...

    Hmmm... Now try changing the debug statement to:
    Code:
    Response.Write "[" & sSQLStmt & "]"
    Response.end
    ...where sSQLStmt is the variable holding the Update SQL just before it is executed. I suspect that, since we know request.form("IsActive") is correct, then your SQL statement must be the problem.

    Can you post the code that builds the UPDATE statement (or procedure) or the code that does the update?

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

    Here's most of the page...

    Bill,

    Thanks again for the help. I ran the new debugging code and it didn't return anything! Very interesting. you must be right about the error being in my SQL. What's odd is that all the other data posts correctly.

    Here's the VERY long, complex page the SQL is on. I've deleted some junk from the page to make it easier to read, but if you want all the code I could e-mail it to you.

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

    The Code is too large to fit in a single message, so I've added it in two following messages.

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

    The Code, part 1

    <!--#include virtual="tutors/Connections/myConnection.asp" -->

    <%
    ' *** Edit Operations: declare variables

    Dim MM_editAction
    Dim MM_abortEdit
    Dim MM_editQuery
    Dim MM_editCmd

    Dim MM_editConnection
    Dim MM_editTable
    Dim MM_editRedirectUrl
    Dim MM_editColumn
    Dim MM_recordId

    Dim MM_fieldsStr
    Dim MM_columnsStr
    Dim MM_fields
    Dim MM_columns
    Dim MM_typeArray
    Dim MM_formVal
    Dim MM_delim
    Dim MM_altVal
    Dim MM_emptyVal
    Dim MM_i

    ' SECOND SET OF VARIABLES
    Dim MM_editAction2
    Dim MM_abortEdit2
    Dim MM_editQuery2
    Dim MM_editCmd2

    Dim MM_editConnection2
    Dim MM_editTable2
    Dim MM_editRedirectUrl2
    Dim MM_editColumn2
    Dim MM_recordId2

    Dim MM_fieldsStr2
    Dim MM_columnsStr2
    Dim MM_fields2
    Dim MM_columns2
    Dim MM_typeArray2
    Dim MM_formVal2
    Dim MM_delim2
    Dim MM_altVal2
    Dim MM_emptyVal2
    Dim MM_i2

    MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
    If (Request.QueryString <> "") Then
    MM_editAction = MM_editAction & "?" & Request.QueryString
    End If



    ' boolean to abort record edit
    MM_abortEdit = false

    ' query string to execute
    MM_editQuery = ""
    %>

    <%
    ' *** Update Record: set variables
    If (CStr(Request("MM_update")) = "tutorsInfo" And CStr(Request("MM_recordId")) <> "") Then

    MM_editConnection = MM_myConnection_STRING
    MM_editTable = "Tutors"
    MM_editColumn = "CustomerTutorID"
    MM_recordId = "" + Request.Form("MM_recordId") + ""
    MM_fieldsStr = "Email2|value|AltPhoneType|value|ServiceItemName|v alue|ServiceItemListID|value|TimeModified|value|Mo dified|value|FirstName|value|MiddleName|value|Last Name|value|Salutation|value|Phone|value|AltPhone|v alue|Email|value|Fax|value|Addr1|value|Addr2|value |Addr3|value|City|value|State|value|PostalCode|val ue|Comments|value|NameOnCheck|value|SSNumber|value |Contact|value|AltContact|value|IsActive|value"
    MM_columnsStr = "Email2|',none,''|AltPhoneType|',none,''|ServiceIt emName|',none,''|ServiceItemListID|',none,''|TimeM odified|',none,''|Modified|none,1,0|FirstName|',no ne,''|MiddleName|',none,''|LastName|',none,''|Salu tation|',none,''|Phone|',none,''|AltPhone|',none,' '|Email|',none,''|Fax|',none,''|Addr1|',none,''|Ad dr2|',none,''|Addr3|',none,''|City|',none,''|State |',none,''|PostalCode|',none,''|Comments|',none,'' |NameOnCheck|',none,''|SSNumber|',none,''|Contact| ',none,''|AltContact|',none,''|IsActive|none,1,0"

    ' Debugging code
    ' This returns the variable stored in the SQL statement
    Response.Write "[" & sSQLStmt & "]"
    Response.end


    ' START CODE FOR SECOND INSERT -----------------------------
    MM_editTable2 = "SystemUsers"
    MM_editColumn2 = "CustomerTutorID"
    MM_recordId2 = "" + Request.Form("MM_recordId") + ""
    MM_fieldsStr2 = "UserName|value|Password|value|UserType|value"
    MM_columnsStr2 = "UserName|',none,''|Password|',none,''|UserType|', none,''"
    ' END CODE FOR SECOND INSERT -----------------------------

    ' 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
    ' START CODE FOR SECOND INSERT -----------------------------
    ' create the MM_fields2 and MM_columns2 arrays
    MM_fields2 = Split(MM_fieldsStr2, "|")
    MM_columns2 = Split(MM_columnsStr2, "|")

    ' set the form values for the password
    For MM_i2 = LBound(MM_fields2) To UBound(MM_fields2) Step 2
    MM_fields2(MM_i2+1) = CStr(Request.Form(MM_fields2(MM_i2)))
    Next
    ' END CODE FOR SECOND INSERT -----------------------------


    ' append the query string to the redirect URL
    If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
    MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
    MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
    End If

    End If
    %>
    <%
    ' *** 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

    ' START PASSWORD UPDATE CODE -------------------------------
    ' create the sql update statement for the PASSWORD
    MM_editQuery2 = "update " & MM_editTable2 & " set "
    For MM_i2 = LBound(MM_fields2) To UBound(MM_fields2) Step 2
    MM_formVal2 = MM_fields2(MM_i2+1)
    MM_typeArray2 = Split(MM_columns2(MM_i2+1),",")
    MM_delim2 = MM_typeArray2(0)
    If (MM_delim2 = "none") Then MM_delim2 = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal2 = "none") Then MM_altVal2 = ""
    MM_emptyVal2 = MM_typeArray2(2)
    If (MM_emptyVal2 = "none") Then MM_emptyVal2 = ""
    If (MM_formVal2 = "") Then
    MM_formVal2 = MM_emptyVal2
    Else
    If (MM_altVal2 <> "") Then
    MM_formVal2 = MM_altVal2
    ElseIf (MM_delim2 = "'") Then ' escape quotes
    MM_formVal2 = "'" & Replace(MM_formVal2,"'","''") & "'"
    Else
    MM_formVal2 = MM_delim2 + MM_formVal2 + MM_delim2
    End If
    End If
    If (MM_i2 <> LBound(MM_fields2)) Then
    MM_editQuery2 = MM_editQuery2 & ","
    End If
    MM_editQuery2 = MM_editQuery2 & MM_columns2(MM_i2) & " = " & MM_formVal2
    Next
    MM_editQuery2 = MM_editQuery2 & " where " & MM_editColumn2 & " = " & MM_recordId2
    ' END PASSWORD UPDATE CODE -------------------------------

    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
    ' Code for second insert execution
    MM_editCmd.CommandText = MM_editQuery2
    MM_editCmd.Execute
    ' End of code for second insert execution
    MM_editCmd.ActiveConnection.Close

    End If

    End If
    %>


    <%
    Dim RecsetPassword__MMColParam
    RecsetPassword__MMColParam = "1"
    If (Request.QueryString("CustomerTutorID") <> "") Then
    RecsetPassword__MMColParam = Request.QueryString("CustomerTutorID")
    End If
    %>
    <%
    Dim RecsetPassword
    Dim RecsetPassword_numRows

    Set RecsetPassword = Server.CreateObject("ADODB.Recordset")
    RecsetPassword.ActiveConnection = MM_myConnection_STRING
    RecsetPassword.Source = "SELECT * FROM SystemUsers WHERE CustomerTutorID = " + Replace(RecsetPassword__MMColParam, "'", "''") + ""
    RecsetPassword.CursorType = 0
    RecsetPassword.CursorLocation = 2
    RecsetPassword.LockType = 1
    RecsetPassword.Open()

    RecsetPassword_numRows = 0
    %>
    <%
    Dim RecsetTutors1__MMColParam
    RecsetTutors1__MMColParam = "1"
    If (Request.QueryString("CustomerTutorID") <> "") Then
    RecsetTutors1__MMColParam = Request.QueryString("CustomerTutorID")
    End If
    %>
    <%
    Dim RecsetTutors1
    Dim RecsetTutors1_numRows

    Set RecsetTutors1 = Server.CreateObject("ADODB.Recordset")
    RecsetTutors1.ActiveConnection = MM_myConnection_STRING
    RecsetTutors1.Source = "SELECT * FROM Tutors WHERE CustomerTutorID = " + Replace(RecsetTutors1__MMColParam, "'", "''") + ""
    RecsetTutors1.CursorType = 0
    RecsetTutors1.CursorLocation = 2
    RecsetTutors1.LockType = 1
    RecsetTutors1.Open()

    RecsetTutors1_numRows = 0
    %>
    <%
    Dim RecsetServiceItems
    Dim RecsetServiceItems_numRows

    Set RecsetServiceItems = Server.CreateObject("ADODB.Recordset")
    RecsetServiceItems.ActiveConnection = MM_myConnection_STRING
    RecsetServiceItems.Source = "SELECT * FROM ServiceItems ORDER BY Name ASC"
    RecsetServiceItems.CursorType = 0
    RecsetServiceItems.CursorLocation = 2
    RecsetServiceItems.LockType = 1
    RecsetServiceItems.Open()

    RecsetServiceItems_numRows = 0
    %>

    <script language="JavaScript" type="text/JavaScript">
    <!--
    function MM_findObj(n, d) { //v4.01
    var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
    if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
    for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
    if(!x && d.getElementById) x=d.getElementById(n); return x;
    }


    //-->

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

    The Code, Part Two

    </script>
    <form ACTION="<%=MM_editAction%>" METHOD="POST" name="tutorsInfo" id="tutorsInfo">
    <table width="480" border="0" cellspacing="0" cellpadding="3">
    <tr>
    <td colspan="4" class="formHeaderCell">User Name and Password</td>
    </tr>
    <tr bgcolor="#D9CDDC">
    <td class="formRequiredField">User Name</td>
    <td><input name="UserName" type="text" id="UserName2" value="<%=(RecsetPassword.Fields.Item("UserName"). Value)%>" size="15"></td>
    <td class="formRequiredField">Password</td>
    <td><input name="Password" type="text" id="Password2" value="<%=(RecsetPassword.Fields.Item("Password"). Value)%>" size="15"></td>
    </tr>

    </select> <span class="formLabels">Set to &quot;T&quot; to authorize.</span></td>
    <td class="formRequiredField">Is Active:</td>
    <td><select name="IsActive" size="1" id="IsActive">
    <option value="1" <%If (Not isNull((RecsetTutors1.Fields.Item("IsActive").Valu e))) Then If ("True" = CStr((RecsetTutors1.Fields.Item("IsActive").Value) )) Then Response.Write("SELECTED") : Response.Write("")%>>True</option>
    <option value="0" <%If (Not isNull((RecsetTutors1.Fields.Item("IsActive").Valu e))) Then If ("False" = CStr((RecsetTutors1.Fields.Item("IsActive").Value) )) Then Response.Write("SELECTED") : Response.Write("")%>>False</option>


    <%
    While (NOT RecsetTutors1.EOF)
    %>
    <option value="<%=(RecsetTutors1.Fields.Item("IsActive").V alue)%>" <%If (Not isNull((RecsetTutors1.Fields.Item("IsActive").Valu e))) Then If (CStr(RecsetTutors1.Fields.Item("IsActive").Value) = CStr((RecsetTutors1.Fields.Item("IsActive").Value) )) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(RecsetTutors1.Fields.Item("IsActive").Value)% ></option>
    <%
    RecsetTutors1.MoveNext()
    Wend
    If (RecsetTutors1.CursorType > 0) Then
    RecsetTutors1.MoveFirst
    Else
    RecsetTutors1.Requery
    End If
    %>
    </select></td>
    </tr>
    <th colspan="2"><input name="Submit" type="submit" class="butFancy" value="Save Step 1"></th>
    <th colspan="2" align="right"><div align="right">
    <input name="Submit2" type="reset" class="butFancy" value="Clear Form">
    </div></th>
    </tr>

    </table>

    </form>
    <%
    RecsetPassword.Close()
    Set RecsetPassword = Nothing
    %>
    <%
    RecsetTutors1.Close()
    Set RecsetTutors1 = Nothing
    %>
    <%
    RecsetServiceItems.Close()
    Set RecsetServiceItems = Nothing
    %>

  8. #8
    Join Date
    Sep 2003
    Posts
    60

    Re: The Code, Part Two

    ...A very good advertisement for NOT using Dreamweaver MX. Anyway I think you have two elements on the page called IsActive and one needs to be removed.

    Remove that debug statement and move it down below the statement:
    ' END PASSWORD UPDATE CODE -------------------------------

    And change the debug statement to look like this:
    Code:
    ' Debugging code
    ' This returns the variable stored in the SQL statement
    Response.Write "[" & MM_editQuery & "]"
    Response.end
    I think you'll see IsActive in there twice because its in the field array twice.

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

    I'll check into it...

    Bill,

    Thanks again for the help. I'll update the debugging code and I'll check, but I don't think that's the problem. I'm pretty sure the second IsActive is nested in an if statement that only gets read if BOF or EOF is true. I cut some code from the page before posting it because it was too long to post.

    Nevertheless, I'll check and make sure it's correct, though, because something's obviously not working.

    Also, Dreamweaver MX didn't create this mess, I did. Dreamweaver does a very good job of creating SIMPLE data-driven pages . It creates the Recordset, pulls data, posts data, and creates repeating regions very quickly and cleanly.

    What you're seeing is a page that has been heavily modified to pull data from two separate Tables, post back to those Tables, and has portions of content that toggle on and off based on the user's access level. In short, I've built a lot of stuff on top of the basic DW MX page.

    Thanks again -- I'll check it tonight when I get home (it's a consulting project that's separate from my day job) and see if this avenue yields results.

Posting Permissions

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