Create form to update server tables

snteran · December 17th, 2011, 04:39 PM

Need help with displaying current data from tables and then submit update.

Current form:

Code:

strSQL = "select server.server_name, server.status, review.serverowner from server, review where Server.SERVER_NAME = review.SERVER_NAME and Server.SERVER_NAME = '" & strComputer &"'" (I use a query.string here that does bring over the correct server_name)
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn

Using above info, I populate a form. I then use another record set to give options for update options for form using a dropdown box created from a db table:

Code:

Set objRS4 = Server.CreateObject("ADODB.Recordset")
strSQL4 = "SELECT * FROM Options WHERE STable='server' AND SColumn='Status'" 
objRS4.Open strSQL4, objConn

Here is the form section for the Status field.

Code:

<td><select class=clsBodyText NAME="status"><% For p = 2 to objRS4.Fields.Count - 1
Response.Write "<option VALUE='" & objRS4.Fields(s) & "' >" & objRS4.Fields(s) & "</option>"
Next%></select></TD></tr><%
objRS4.Close
Set objRS4 = Nothing
%>

The end result is not working. The select statement is populating with the information from the option table and not what is currently in the server table. I'm guessing I need to see first if there is something in the server table, if not use options from the options table. Just not sure how that would look so that it works. I try but not working at all.
Any suggestions/Advice would be greatly appreciated.

Please let me know if more information is needed.
Cheers

snteran · December 18th, 2011, 02:07 AM

So I have been able to populate the form with the existing data and create a drop down for the other options. However, now when I process the update, it does not complete.

Working code for drop down:

Code:

<td>Status:</td>
		<td><select class=clsBodyText NAME="Status"><%
		For s = 2 to objRS4.Fields.Count - 1
		If objRS4.Fields(s) = objRS("status") Then
		Response.Write "<option size=""40"" selected=""selected"" value='"  & objRS4.Fields(s) & "'>" & objRS4.Fields(s) & "</option>" 
		Else
		If objRS4.Fields(s) <> "" Then		
Response.Write "<option VALUE='" & objRS4.Fields(s) & "'>" & objRS4.Fields(s) & "</option>"
End If
End If
Next
Response.Write "</select></TD>"
objRS4.Close
Set ObjRS4 = Nothing
%>

Now my update code from the form:

Code:

 Dim objRS, objConn, objRS2, objConn2    

   Set objConn = Server.CreateObject("ADODB.Connection")
	   objConn.ConnectionString = sConnection
	   objConn.Open
       Set objRS = Server.CreateObject("ADODB.Recordset")
       objRS.Open "Server", objConn, , adLockOptimistic , adCmdTable

  objRS("Status") = Request.Form("Status")
 objRS.Update
       objRS.Close	   
       Set objRS = Nothing	 
      objConn.Close
    Set objConn = Nothing

Thanks for any help,