How can I retrieve and update database data in the same form?

[Rikkimaru]

Hello Everyone.

I'm two weeks old to ASP and doing a little project to learn.
My challenge is that I need to retrieve some information from a database. This information will be displayed inside text boxes.
These text boxes will be within a form where the user can edit the information and click a "submit" button to update.
The form's action is back to itself (action="update.asp"), so that you will see the changes that you have made and can make more if necessary.
The form pulls the database data well enough, but when I change text in the textboxes and hit submit, nothing is entered and thus the values entered into the database are "null".
The Database has alot of columns, so, I'll abbreviate the code, but let me know if you need more and thanks in advance to the kind and wise soul(s)who can help.

<%
'This is the connection to the SQL Database.

Dim CONNECTIONSTRING

CONNECTIONSTRING = "PROVIDER=SQLOLEDB;DATABASE=Test;DATA SOURCE=MYPC;Connect Timeout = 120;Extended Properties=""Trusted_Connection=Yes"""

Set SQLCONNECTION = Server.CreateObject("ADODB.Connection")
SQLCONNECTION.Open(CONNECTIONSTRING)

'The Session Objects are generated after login and used to specify
'users so that the correct record can be altered

DIM getinfo

displaydbinfo = "SELECT * FROM Persons WHERE (MailAddress = '"&Session("id")&"') AND (Password = '"&Session("pass")&"')"

Set getinfo=SQLCONNECTION.execute(displaydbinfo)
%>

<form action="updatetest.asp" method="post">
<table border="0" align="left" width=700>

<%
'Here is where I start the loop to get results from the recordset.
'Do I really NEED to use the loop since I know that there will only 
'be one result?

DO while not getinfo.eof
%>

<%
'Here's the problem...I set the name of the textbox to be Salutitle, 
'the displayed value should be whatever is pulled out of the database
' with the recordset which works well enough. 
%>

<tr>
<td align="left"><b>Salutation:</b></td>
<td align="left">
<input type="text" name="SaluTitle" value="<%=getinfo("SaluTitle")%>">
<%
'The SaluTitle variable was global but I changed it and created the 
'variable within the loop in case the "Request.Form("SaluTitle")"
'variables generated in the recordset were local to the loop.
'Response.Write(whatever) is just my way of trying to see what's 
'going on. 

Dim SaluTitle
SaluTitle=Request.Form("SaluTitle")
Response.Write(SaluTitle)
%>
</td>
</tr>

<%'Many more rows and columns following the same format above%>

<%
'This is the beginning of the UPDATE section which will pull the 
'inserted values and update the database with the new values. Why 
'doesn't it work?

DIM updatedbinfo

updatedbinfo = "Update Persons SET CellPhoneNumber = '"&CellPhoneNumber&"', Department = '"&Department&"', FirstName = '"&FirstName&"', JobTitle = '"&JobTitle&"', MailAddress = '"&MailAddress&"', LastName = '"&LastName&"' , Location = '"&Location&"', Manager = '"&Manager&"', MiddleInitial = '"&MiddleInitial&"', OfficeFAXPhoneNumber = '"&OfficeFAXPhoneNumber&"', OfficePhoneNumber = '"&OfficePhoneNumber&"', PagerPhoneNumber = '"&PagerPhoneNumber&"', AssistantDescription = '"&AssistantDescription&"', Assistant = '"&Assistant&"', SaluTitle = '"&SaluTitle&"', Password = '"&Password&"' WHERE (MailAddress = '"&Session("id")&"') AND (Password = '"&Session("pass")&"')"

Set updateinfo=SQLCONNECTION.execute(updatedbinfo)

'Continuing the loop and moving to the next recordset (that isn't there)

getinfo.movenext 
LOOP
%>

</table>
</form>

 

[nicklieb]

you need to cast the variables eg

CellPhoneNumer = request.form("CellPhoneNumber")

[code]

thats why you get null values in the database

I would only update the database is the form is posted to 

using this 

[code]
if Request.ServerVariables("REQUEST_METHOD") = "post" then

' do your update

end if

 

[TheConeHead]

Make sure you are getting the values from the form:

Dim CellPhoneNumber2
CellPhoneNumber2 = Request.Form("CellPhoneNumberFormName")

 

[Sheco]

Here, try this page instead. It only calls the UPDATE when the page has actually been updated and it uses the Request.Form collection to get the values to be updated.

'This is the connection to the SQL Database.
Dim CONNECTIONSTRING, mySQL
CONNECTIONSTRING = "PROVIDER=SQLOLEDB;DATABASE=Test;" _
                 & "DATA SOURCE=MYPC;" _
                 & "Connect Timeout = 120;" _
                 & "Trusted_Connection=Yes"

Set SQLCONNECTION = Server.CreateObject("ADODB.Connection")
SQLCONNECTION.Open(CONNECTIONSTRING)

IF (SQLCONNECTION.State <> 1) THEN
  Response.Write "Database Connection Failed!"
  Response.End 
END IF

'Check to see whether this page is loading for the first time 
'or rather if it has been submitted to itself.
IF (Len(Request.Form("Submit")) > 0) THEN
  'The form was submitted to itself because there is a 
  'value for the Submit button in the Request collection.
	
  'We only do the update when the form is submitted to itself.
  mySQL = "Update Persons SET " _
        & "CellPhoneNumber = '" & Request.Form("CellPhoneNumber") & "', " _
        & "Department = '" & Request.Form("Department") & "', " _
        & "FirstName = '" & Request.Form("FirstName") & "', " _
        & "JobTitle = '" & Request.Form("JobTitle") & "', " _
        & "MailAddress = '" & Request.Form("MailAddress") & "', " _
        & "LastName = '" & Request.Form("LastName") & "', " _
        & "Location = '" & Request.Form("Location") & "', " _
        & "Manager = '" & Request.Form("Manager") & "', " _
        & "MiddleInitial = '" & Request.Form("MiddleInitial") & "', " _
        & "OfficeFAXPhoneNumber = '" & Request.Form("OfficeFAXPhoneNumber") & "', " _
        & "OfficePhoneNumber = '" & Request.Form("OfficePhoneNumber") & "', " _
        & "PagerPhoneNumber = '" & Request.Form("PagerPhoneNumber") & "', " _
        & "AssistantDescription = '" & Request.Form("AssistantDescription") & "', " _
        & "Assistant = '" & Request.Form("ssistan") & "', " _
        & "SaluTitle = '" & Request.Form("SaluTitle") & "', " _
        & "Password = '" & Request.Form("Password") & "' " _
        & "WHERE " _
        & "(MailAddress = '" & Session("id") & "') AND " _
        & "(Password = '" & Session("pass") & "')"
  
  SQLCONNECTION.execute mySQL
END IF


'The Session Objects are generated after login and used to specify
'users so that the correct record can be altered
mySQL = "SELECT * FROM Persons " _
      & "WHERE " _
      & "(MailAddress = '" & Session("id") & "') AND " _
      & "(Password = '" & Session("pass") & "')"

DIM getinfo
Set getinfo=SQLCONNECTION.execute(mySQL)

IF (getinfo.State <> 1) THEN
  Response.Write "Database Query Error"
  Response.End 
END IF
%>
<html>
<head>
  <title>
    Test Page
  </title>
</head>
<body>
  <form action="updatetest.asp" method="post">
    <table border="0" align="left" width=700>
<%
IF getinfo.EOF THEN
  'The SELECT query did not return a row!
%>
      <tr>
        <td colspan="2">
          No record matches query.
         <br>
         <%= strSQL%>
      </td>
    </tr>
<%
ELSE
  'Here we just flip through all the fields and use the field name
  'as the name for the textboxes.  You might want to hardcode this
  'for each field instead of doing it this way because sometimes a
  'database field name is not the most descriptive thing for an 
  'end user to figure out.
  Dim oFld    'ADO Field object
  For Each oFld in getinfo.Fields
%>
    <tr>
      <td align="left">
        <b><%= oFld.Name %></b>: 
      </td>
      <td align="left">
        <input type="text" name="<%= oFld.Name %>" value="<%= <%= oFld.Value %>%>">
      </td>
    </tr>
<%	
  Next
%>
    <tr>
      <td colspan="2" align="center">
        <input type="submit" name="submit" value="Submit">
      </td>
    </tr>
<%
END IF
%>		
  </table>
  </form>
</body>
</html>

 

[Rikkimaru]

Thanks for the help guys. I'll try some of the stuff you mentioned.