the simplest of tasks...always the hardest.

[jason9898]

I have a sql server 2000 table with the following.
ID Choice
1 1
2 0
3 1

In dreamweaver, using asp vb script language, I created a recordset of this table, the a dynamic table of the data (repeat region) to display this data on my web page.

I want to add a radio group that displays true or false for everyrecord in the db. The table data is dynamic and can change, I am not sure how to approach this with the DW or ASP tools? Is it possible?

I simply want to have an update query that the users can click on the radio button for each record and click submit updating the choice field (bit) 1 or 0.

Please any help would be great. Seems like it should be a standard feature in DW?

 

[Sheco]

Perhaps it would help you to think about how the HTML output of your ASP should be... then work backwards to get there.

If your HTML should be like this:

<form method="post">
  ID# 1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="radio" name="r1" value="1" checked> True
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="radio" name="r1" value="0"> False
  <BR>
  ID# 2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="radio" name="r2" value="1"> True
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="radio" name="r2" value="0" checked> False
  <BR>
  ID# 3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="radio" name="r3" value="1" checked> True
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="radio" name="r3" value="0"> False
  <BR>
  <br>
  <input type="submit">
<form>

Then that would tell you some things about how to code your ASP.

 

[jason9898]

The problem is the table data is dynamic. I cannot rely on hard coding the radio buttons. It needs to be driven the same way as the repeat region works...

 

[Sheco]

I understand that the data itself is dynamic, but HTML can be described as a pattern. And you can write code to reproduce the pattern. This is what I mean by working backwards from the HTML.

For example, if that HTML that I posted above was correct for you, then you could work backwards to write code like this:

<form method="post">
<%
'*** code for opening ADO conection ommitted
 
Set adoRS = adoConn.Execute "SELECT ID, Choice FROM SomeTable ORDER BY ID"

IF adoRS.State <> 1 THEN
  Response.Write "Data error!"
  Response.End
END IF

Do While Not adoRS.EOF
%>
  ID# <%= adoRS(0)%> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="radio" name="r<%= adoRS(0)%>" value="1" <%If adoRS(1) Then Response.Write " checked "%>> True
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="radio" name="r<%= adoRS(0)%>" value="0" <%If Not adoRS(1) Then Response.Write " checked "%>> False
  <BR>
<%
  adoRS.MoveNext
Loop

adoRS.Close
Set adoRS = Nothing
adoConn.Close
Set adoConn = Nothing
%>
  <BR>
  <br>
  <input type="submit">
</form>

 

[jason9898]

Sheco,
Very very helpful example. I did get it to execute properly. The problem I am having is getting it to appear cleanly in a nice table. If I insert a table and place the ASP code in it, it repeats the whole table for every record.

I cannot get the repeat region to operate correctly either?

Can you help elaborate with the psuedo code above.

 

[Sheco]

Do you want each row in your recordset to have a corresponding row in an HTML table?

If so then you can do something like this:

<table border="1">  <!-- Beginning of HTML table -->
  <tr>
    <td colspan="2">
      This is the table heading
    </td>
  </tr>
<!-- One TR for each record -->
<%
Do While Not adoRS.EOF
%>
  <tr>
    <td>
      ID# <%= adoRS(0)%>
    </td>    
    <td>
  <input type="radio" name="r<%= adoRS(0)%>" value="1" <%If adoRS(1) Then Response.Write " checked "%>> True
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="radio" name="r<%= adoRS(0)%>" value="0" <%If Not adoRS(1) Then Response.Write " checked "%>> False
    </td>
  </tr>
<%
  adoRS.MoveNext
Loop
%>

</table> <!-- End of HTML Table -->

Notice how the HTML table starts before the loop and ends after the loop... Each time through the loop you write a new <TR>

 

[jason9898]

I can only get it to populate certain radio buttons from the db. Only two appear, when their are 16 in total with 1 and 0's for everything.
I followed you code exactly, i set the set value equal to the database field and still nothing.

the loop seems to work perfectly for everything else???

Radio buttons it will not populate though, only two of them???

frustated and need help...
appreciate all the help sheco

 

[jason9898]

i think i alomst got it...
This code will display the checked fields based on the database and allow me to update the values. the only problem is the table looks like it repeats for every record and i also have a submit button for every record...

I need to have only one submit button at the bottom of the page?

Can you help me edit this or suggest a better way with the above code?

<%LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/test.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

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(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")) = "form1" And CStr(Request("MM_recordId")) <> "") Then

MM_editConnection = MM_test_STRING
MM_editTable = "dbo.Picks"
MM_editColumn = "GameID"
MM_recordId = "" + Request.Form("MM_recordId") + ""
MM_editRedirectUrl = "jason.asp"
MM_fieldsStr = "RadioGroup1|value"
MM_columnsStr = "Pick_HA|none,none,NULL"

' 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

' 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

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
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_test_STRING
Recordset1.Source = "SELECT * FROM dbo.Picks"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "

http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<%

IF Recordset1.State <> 1 THEN
Response.Write "Data error!"
Response.End
END IF

Do While Not Recordset1.EOF
%>
<form name="form1" method="POST" action="<%=MM_editAction%>">
<table width="801" border="1">
<tr>
<td width="4">&nbsp;</td>
<td width="141"><%=(Recordset1.Fields.Item("GameID").Value)%></td>
<td width="243"><%=(Recordset1.Fields.Item("Pick_HA").Value)%></td>
<td width="385"><p>
<label>
<input <%If (CStr(Abs((Recordset1.Fields.Item("Pick_HA").Value))) = CStr("1")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="RadioGroup1" value="1">
Radio</label>
<input <%If (CStr(Abs((Recordset1.Fields.Item("Pick_HA").Value))) = CStr("0")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="RadioGroup1" value="0">
<label>Radio</label>
<br>
<label> </label>
<br>
</p></td>
</tr>
</table>
<input type="hidden" name="MM_update" value="form1">
<input type="hidden" name="MM_recordId" value="<%= Recordset1.Fields.Item("GameID").Value %>">
<br>
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>

<%
Recordset1.MoveNext
Loop
Recordset1.Close()
Set Recordset1 = Nothing
%>