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

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!

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!

Hmmm... Now try changing the debug statement to:
...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?

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.

<!--#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;
}


//-->

</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
%>

...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:
I think you'll see IsActive in there twice because its in the field array twice.

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.