Access memo field won't update

[VBAPrincess]

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!

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!

 

[xwb]

Where do you set oConn, the connection to your database? What does your connection string look like?

 

[VBAPrincess]

It's in an include file called conn.asp

<%
	Dim oConn 
	Set oConn = Server.CreateObject("ADODB.Connection")
	oConn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..") & "\access_db\donuts.mdb;"

%>

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[ChrisHirst]

There is a long standing problem in ASP vBScript with ODBC/ADO that affects memo fields.

http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=80

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[VBAPrincess]

Chris, thanks for the link but I did look at that URL before posting my question and it did not help. The code on this page is similar to other pages in the site which also utilize memo fields. Who knows? I've now changed the fields to text (255) and have gotten it to update. I created two text fields for each memo field and I'm just breaking any content longer than 250 char into the two fields so that the client can have a max of 500. I had to change two other pages to pull data from both OwnerMsg and OwnerMsg2 to concatenate longer messages together, but at least it all works.

Thanks!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[ChrisHirst]

I solved any problems I had with reading memo fields by using the GetRows method so that the recordset is only accessed once to create the array.


Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum