VBAPrincess
Programmer
I've searched this forum and others, and have tried what others have suggested, but I just cannot seem to get my code to work. I have a very simple table with four fields.
AdminNewsID (autonumber)
OwnerMsg (memo)
OrderMsg (memo)
EditDate (date/time)
I have a page that displays the content of the single record in the table and allows the user to edit either the OwnerMsg or the OrderMsg. However, every time I try to update, it returns a blank field.
I've tried a SQL Update statement, I've tried using a recordset and calling the Update method. I made sure I updated the memo field last. I've had it update properly once or twice, get all happy, then test a few more times only to have the value return blank again.
I have debugged by writing out the variable data, the SQL statements, and even though I've got all of the information, the record will not update. I do not get any errors but when I display the data, the fields are blank.
HELP!
Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!
AdminNewsID (autonumber)
OwnerMsg (memo)
OrderMsg (memo)
EditDate (date/time)
I have a page that displays the content of the single record in the table and allows the user to edit either the OwnerMsg or the OrderMsg. However, every time I try to update, it returns a blank field.
I've tried a SQL Update statement, I've tried using a recordset and calling the Update method. I made sure I updated the memo field last. I've had it update properly once or twice, get all happy, then test a few more times only to have the value return blank again.
I have debugged by writing out the variable data, the SQL statements, and even though I've got all of the information, the record will not update. I do not get any errors but when I display the data, the fields are blank.
HELP!
Code:
Sub Main()
Dim sField, sTitle
Dim SCRIPT_NAME
SCRIPT_NAME = Request.ServerVariables("SCRIPT_NAME")
Dim BACK_TO_LIST_TEXT
BACK_TO_LIST_TEXT = "<p>Click <a href=""" & SCRIPT_NAME & """>" _
& "here</a> to go back to record list.</p>"
' Declare our standard variables.
Dim rstDBEdit ' ADO objects
Dim strSQL ' To hold various SQL Strings
Dim iRecordId ' Used to keep track of the record in play
Dim sOwnerMsg, sOrderMsg
' Choose what to do by looking at the action parameter
Select Case LCase(Trim(Request.QueryString("action")))
Case "edit"
sField = Request.QueryString("ID")
if sField = "OwnerMsg" then
sTitle = "Owners Area:"
strSQL = "SELECT AdminNewsID, OwnerMsg FROM AdminNews WHERE AdminNewsID=1;"
else
sTitle = "Order Form:"
strSQL = "SELECT AdminNewsID, OrderMsg FROM AdminNews WHERE AdminNewsID=1;"
end if
Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
rstDBEdit.Open strSQL, oConn, 1, 2
If Not rstDBEdit.EOF Then
%>
<form action="<%= SCRIPT_NAME %>?ID=<%= sField %>&action=editsave" method=post>
<table><tr>
<td valign="top"><%= sTitle %></td>
<td><textarea name="Msg" id="Msg" cols="50" rows="5"><%= rstDBEdit.Fields(sField).Value %></textarea>
<br><input type="submit" value="Update Database"></td>
</tr></table>
</form>
<%
Else
Response.Write "Record not found!"
End If
rstDBEdit.Close
Set rstDBEdit = Nothing
Response.Write(BACK_TO_LIST_TEXT)
Case "editsave"
sField = Request.QueryString("ID")
' sTitle = Chr(34) & Replace(Request.Form("Msg"), "'", "''") & Chr(34)
sTitle = Replace(Request.Form("Msg"), "'", "''")
strSQL = "SELECT AdminNewsID, EditDate, OwnerMsg, OrderMsg FROM AdminNews"
' strSQL = "UPDATE AdminNews SET AdminNews." & Request.QueryString("ID") & "=" & sTitle & " WHERE AdminNewsID=1;"
response.Write("<br>" & strSQL)
' if CheckStringForSQL(strSQL) then
' oConn.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords
' end if
Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
rstDBEdit.CursorLocation = adUseClient
rstDBEdit.Open strSQL, oConn, 1, 2
if not rstDBEdit.EOF then
rstDBEdit("EditDate") = Now()
rstDBEdit.Fields(sField) = sTitle
rstDBEdit.Update
end if
if err.number <> 0 then
response.write(err.number & " - " & err.description)
else
Response.Write("<p>Record updated!</p>")
Response.Write(BACK_TO_LIST_TEXT)
end if
rstDBEdit.Close
Set rstDBEdit = Nothing
Case Else ' view
' Our default action... just lists the record in the DB
strSQL = "SELECT AdminNewsID, EditDate, OwnerMsg, OrderMsg FROM AdminNews;"
Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
rstDBEdit.Open strSQL, oConn, 1, 2
%>
<table width="95%" border="1" cellspacing="0" cellpadding="3" align="center" bordercolor="#333333" style='border-collapse:collapse;' id="myTable">
<tr>
<td class="menutextBold" align="center">Message Location</td>
<td class="menutextBold" align="center">Message</td>
<td class="menutextBold" align="center">Edit</td>
</tr>
<%
If Not rstDBEdit.EOF then
sOwnerMsg = rstDBEdit("OwnerMsg")
sOrderMsg = rstDBEDit("OrderMsg")
%>
<tr>
<td class="menutext">Owners Area</td>
<td class="menutext"><%= sOwnerMsg %></td>
<td class="menutext"><a href="<%= SCRIPT_NAME %>?ID=OwnerMsg&action=edit">Edit</a></td>
</tr>
<tr>
<td class="menutext">Order Form</td>
<td class="menutext"><%= sOrderMsg %></td>
<td class="menutext"><a href="<%= SCRIPT_NAME %>?ID=OrderMsg&action=edit">Edit</a></td>
</tr>
<%
end if %>
</table>
<%
rstDBEdit.Close
Set rstDBEdit = Nothing
End Select
End Sub
Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!