Announcement

Collapse
No announcement yet.

Formatting Access Database Memo Fields

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
    #1

    Formatting Access Database Memo Fields

    Might be a bit off topic bit it is to do with a site that has an actinic store.

    I am populating an Access database field through an online form and wonder if anyone knows how to format one of the memo fields so that it is viewed correctly as paragraphs. You can take a look at what I mean at http://www.boxergirl.co.uk/girls2.asp?ID=6.

    Cheers
    Fitness for life!www.fitness-focus.co.uk


    DIFN - Doing nothing is not an option

    The Supplement Warehouse - Bodybuilding & Fitness Supplements
    Tags: None
    #2
    re formatting text

    HI there, here is a simple way to format the text in you memo field as it is being pulled from the database..

    Put the function at the top of your asp page

    Function formatMessage( theMessage)
    Dim strMessage As String
    strMessage = theMessage
    strMessage = Replace(strMessage, Chr(13), " ")
    strMessage = Replace(strMessage, Chr(10), "</P><P>")
    strMessage = Replace(strMessage, Chr(10), "<P>")
    formatMessage = strMessage
    End Function


    In your table cell where the memo field is to be displayed the code would be something like:-

    Response.Write formatMessage(rst.Fields("memoField"))

    rst.Fields("memoField") is the recordset containing the paragraph.

    Hope this helps..
    Last edited by peterB; 15-Nov-2004, 03:17 PM. Reason: mised out a bit

    Comment

      #3
      Tried it but it gave the following error

      Microsoft VBScript compilation error '800a0401'

      Expected end of statement

      /girls2.asp, line 5

      Dim strMessage As String
      ---------------^


      Listed below is the source code.

      <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
      <!--#include file="Connections/bg1.asp" -->
      <%
      Function formatMessage( theMessage)
      Dim strMessage As String
      strMessage = theMessage
      strMessage = Replace(strMessage, Chr(13), " ")
      strMessage = Replace(strMessage, Chr(10), "</P><P>")
      strMessage = Replace(strMessage, Chr(10), "<P>")
      formatMessage = strMessage
      End Function
      %>
      <%
      Dim rsgirls__MMColParam
      rsgirls__MMColParam = "1"
      If (Request.QueryString("ID") <> "") Then
      rsgirls__MMColParam = Request.QueryString("ID")
      End If
      %>

      <%
      Dim rsgirls
      Dim rsgirls_numRows

      Set rsgirls = Server.CreateObject("ADODB.Recordset")
      rsgirls.ActiveConnection = MM_bg1_STRING
      rsgirls.Source = "SELECT * FROM register WHERE ID = " + Replace(rsgirls__MMColParam, "'", "''") + ""
      rsgirls.CursorType = 0
      rsgirls.CursorLocation = 2
      rsgirls.LockType = 3
      rsgirls.Open()

      rsgirls_numRows = 0
      %>

      <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><!-- InstanceBegin template="/Templates/1.dwt" codeOutsideHTMLIsLocked="false" -->
      <head>
      <!-- InstanceBeginEditable name="doctitle" -->
      <title>Boxer Girl</title>
      <!-- InstanceEndEditable --><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
      <link href="site.css" rel="stylesheet" type="text/css">

      <!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
      </head>

      <body bgcolor="#FFFFFF">
      <div align="center">
      <center>
      <table border="0" cellpadding="0" cellspacing="0" width="749">
      <tr>
      <td colspan="3" bgcolor="#000000"><img border="0" src="images/clear_dot.gif" width="1" height="2"></td>
      </tr>
      <tr>
      <td bgcolor="#000000" valign="bottom" width="2"><img border="0" src="images/clear_dot.gif" width="2" height="1"></td>
      <td>
      <table width="745" border="0" cellpadding="0" cellspacing="0" bgcolor="#c4373a">
      <tr>
      <td><img src="images/1/logo.gif" width="745" height="179"></td>
      </tr>
      </table>

      <table border="0" cellpadding="0" cellspacing="0" width="100%" background="..images/1/nav_bgd.gif">
      <tr>
      <td valign="top" background="images/1/nav_bgd.gif">
      <p align="center"><a href="index.htm"><img src="images/1/nav_top_home.gif" alt="home" width="115" height="34" border="0"></a><a href="contact.htm"><img src="images/1/nav_top_faq.gif" alt="about us" width="115" height="34" border="0"><img src="images/1/nav_top_contact.gif" alt="contact us" width="115" height="34" border="0"></a></td>
      </tr>
      </table>
      <table border="0" cellpadding="0" cellspacing="0" width="100%">
      <tr>
      <td width="100%"><img border="0" src="images/clear_dot.gif" width="1" height="2"></td>
      </tr>
      </table>
      <table border="0" cellpadding="0" cellspacing="0" width="100%">
      <tr>
      <td width="150" valign="top" bgcolor="#C4373A" colspan="3"><table width="150" border="0" cellspacing="0" cellpadding="0">
      <tr>
      <td valign="top"><p><img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="index.htm">Home<br>
      </a><img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="register.asp">Register</a><br>
      <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="girls1.asp">The Girls</a><br>
      <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="gym1.htm">Gyms</a><br>
      <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="beginners.htm">For Beginners</a><br>
      <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="gossip/default.asp">Gossip</a><br>
      <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="/data/acatalog">Shopping!</a><br>
      <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="contact.htm">Contact Us</a> <br>
      <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="links.htm">Useful Links</a> </p>
      </p>
      <p>&nbsp;</p></td>
      </tr>
      </table></td>
      <td width="15" valign="bottom" rowspan="2"><img border="0" src="images/clear_dot.gif" width="15" height="1"></td>
      <td width="100%" valign="top"><!-- InstanceBeginEditable name="main" -->
      <table width="90%" border="0" align="center" cellpadding="5" cellspacing="0" class="table">
      <tr>
      <td width="25%">Name</td>
      <td width="75%"><%=(rsgirls.Fields.Item("name").Value)%></td>
      </tr>
      <tr>
      <td width="25%">Date of birth </td>
      <td width="75%"><%=(rsgirls.Fields.Item("dob").Value)%></td>
      </tr>
      <tr>
      <td width="25%">Location</td>
      <td width="75%"><%=(rsgirls.Fields.Item("location").Value)%></td>
      </tr>
      <tr>
      <td>Fighting weight </td>
      <td><%=(rsgirls.Fields.Item("weight").Value)%></td>
      </tr>
      <tr>
      <td width="25%">Height</td>
      <td width="75%"><%=(rsgirls.Fields.Item("height").Value)%></td>
      </tr>
      <tr>
      <td width="25%">Fight record </td>
      <td width="75%"><%=(rsgirls.Fields.Item("bouts").Value)%></td>
      </tr>
      <tr>
      <td width="25%">I am looking for </td>
      <td width="75%"><%=(rsgirls.Fields.Item("lookingfor").Value)%></td>
      </tr>
      <tr>
      <td width="25%">Contact number </td>
      <td width="75%"><%=(rsgirls.Fields.Item("clubphone").Value)%></td>
      </tr>
      <tr>
      <td width="25%">Email Address</td>
      <td width="75%"><a href="mailto:<%=(rsgirls.Fields.Item("email").Value)%>">Click here to email me </a></td>
      </tr>
      <tr valign="top">
      <td colspan="2"><p align="center"><a href="http://www.boxergirl.co.uk/images/<%=(rsgirls.Fields.Item("pic2").Value)%>"><img src="http://www.boxergirl.co.uk/images/<%=(rsgirls.Fields.Item("pic1").Value)%>" hspace="10" vspace="10" border="0"></a></p> </td>
      </tr>
      <tr valign="top">
      <td colspan="2"><%=Response.Write formatMessage(rsgirls.Fields("anythingelse"))

      rsgirls("anythingelse")%></td>
      </tr>
      </table>
      <p>&nbsp;</p>
      <!-- InstanceEndEditable --></td>
      <td width="15" valign="bottom" rowspan="2"><img border="0" src="images/clear_dot.gif" width="15" height="1"></td>
      </tr>
      <tr>
      <td width="5" valign="bottom" bgcolor="#C4373A"><img border="0" src="images/clear_dot.gif" width="5" height="1">
      </td>
      <td width="130" valign="bottom" bgcolor="#C4373A">&nbsp;</td>
      <td width="5" valign="bottom" bgcolor="#C4373A"><img border="0" src="images/clear_dot.gif" width="5" height="1">
      </td>
      <td width="100%" valign="top"><div align="center">
      <p>&nbsp;</p>
      </div></td>
      </tr>
      </table>
      </td>
      <td bgcolor="#000000" valign="bottom" width="2"><img border="0" src="images/clear_dot.gif" width="2" height="1"></td>
      </tr>
      <tr>
      <td colspan="3" bgcolor="#000000"><img border="0" src="images/clear_dot.gif" width="1" height="2"></td>
      </tr>
      </table>
      </center>
      </div>
      </body>
      <!-- InstanceEnd --></html>
      <%
      rsgirls.Close()
      Set rsgirls = Nothing
      %>
      Fitness for life!www.fitness-focus.co.uk


      DIFN - Doing nothing is not an option

      The Supplement Warehouse - Bodybuilding & Fitness Supplements

      Comment

        #4
        oops sorry

        Sorry, remove the "As String" I copied the code from my .net page

        it should be

        Function formatMessage( theMessage)
        Dim strMessage
        strMessage = theMessage
        strMessage = Replace(strMessage, Chr(13), " ")
        strMessage = Replace(strMessage, Chr(10), "</P><P>")
        strMessage = Replace(strMessage, Chr(10), "<P>")
        formatMessage = strMessage
        End Function

        Also remove the bit in red, this isn't needed.
        <td colspan="2"><%=Response.Write formatMessage(rsgirls.Fields("anythingelse"))

        rsgirls("anythingelse")%></td>


        Hope it works this time..
        Last edited by peterB; 15-Nov-2004, 04:25 PM. Reason: extra bit

        Comment

          #5
          Still no joy. The error message is now

          Microsoft VBScript compilation error '800a03ee'

          Expected ')'

          /girls2.asp, line 133

          Response.Write(Response.Write formatMessage(rsgirls.Fields("anythingelse")))
          ------------------------------^


          Code as follows:

          <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
          <!--#include file="Connections/bg1.asp" -->
          <%
          Function formatMessage( theMessage)
          Dim strMessage
          strMessage = theMessage
          strMessage = Replace(strMessage, Chr(13), " ")
          strMessage = Replace(strMessage, Chr(10), "</P><P>")
          strMessage = Replace(strMessage, Chr(10), "<P>")
          formatMessage = strMessage
          End Function
          %>
          <%
          Dim rsgirls__MMColParam
          rsgirls__MMColParam = "1"
          If (Request.QueryString("ID") <> "") Then
          rsgirls__MMColParam = Request.QueryString("ID")
          End If
          %>

          <%
          Dim rsgirls
          Dim rsgirls_numRows

          Set rsgirls = Server.CreateObject("ADODB.Recordset")
          rsgirls.ActiveConnection = MM_bg1_STRING
          rsgirls.Source = "SELECT * FROM register WHERE ID = " + Replace(rsgirls__MMColParam, "'", "''") + ""
          rsgirls.CursorType = 0
          rsgirls.CursorLocation = 2
          rsgirls.LockType = 3
          rsgirls.Open()

          rsgirls_numRows = 0
          %>

          <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><!-- InstanceBegin template="/Templates/1.dwt" codeOutsideHTMLIsLocked="false" -->
          <head>
          <!-- InstanceBeginEditable name="doctitle" -->
          <title>Boxer Girl</title>
          <!-- InstanceEndEditable --><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
          <link href="site.css" rel="stylesheet" type="text/css">

          <!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
          </head>

          <body bgcolor="#FFFFFF">
          <div align="center">
          <center>
          <table border="0" cellpadding="0" cellspacing="0" width="749">
          <tr>
          <td colspan="3" bgcolor="#000000"><img border="0" src="images/clear_dot.gif" width="1" height="2"></td>
          </tr>
          <tr>
          <td bgcolor="#000000" valign="bottom" width="2"><img border="0" src="images/clear_dot.gif" width="2" height="1"></td>
          <td>
          <table width="745" border="0" cellpadding="0" cellspacing="0" bgcolor="#c4373a">
          <tr>
          <td><img src="images/1/logo.gif" width="745" height="179"></td>
          </tr>
          </table>

          <table border="0" cellpadding="0" cellspacing="0" width="100%" background="..images/1/nav_bgd.gif">
          <tr>
          <td valign="top" background="images/1/nav_bgd.gif">
          <p align="center"><a href="index.htm"><img src="images/1/nav_top_home.gif" alt="home" width="115" height="34" border="0"></a><a href="contact.htm"><img src="images/1/nav_top_faq.gif" alt="about us" width="115" height="34" border="0"><img src="images/1/nav_top_contact.gif" alt="contact us" width="115" height="34" border="0"></a></td>
          </tr>
          </table>
          <table border="0" cellpadding="0" cellspacing="0" width="100%">
          <tr>
          <td width="100%"><img border="0" src="images/clear_dot.gif" width="1" height="2"></td>
          </tr>
          </table>
          <table border="0" cellpadding="0" cellspacing="0" width="100%">
          <tr>
          <td width="150" valign="top" bgcolor="#C4373A" colspan="3"><table width="150" border="0" cellspacing="0" cellpadding="0">
          <tr>
          <td valign="top"><p><img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="index.htm">Home<br>
          </a><img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="register.asp">Register</a><br>
          <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="girls1.asp">The Girls</a><br>
          <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="gym1.htm">Gyms</a><br>
          <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="beginners.htm">For Beginners</a><br>
          <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="gossip/default.asp">Gossip</a><br>
          <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="/data/acatalog">Shopping!</a><br>
          <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="contact.htm">Contact Us</a> <br>
          <img border="0" src="images/1/arrow.gif" width="12" height="12"><a class="left" href="links.htm">Useful Links</a> </p>
          </p>
          <p>&nbsp;</p></td>
          </tr>
          </table></td>
          <td width="15" valign="bottom" rowspan="2"><img border="0" src="images/clear_dot.gif" width="15" height="1"></td>
          <td width="100%" valign="top"><!-- InstanceBeginEditable name="main" -->
          <table width="90%" border="0" align="center" cellpadding="5" cellspacing="0" class="table">
          <tr>
          <td width="25%">Name</td>
          <td width="75%"><%=(rsgirls.Fields.Item("name").Value)%></td>
          </tr>
          <tr>
          <td width="25%">Date of birth </td>
          <td width="75%"><%=(rsgirls.Fields.Item("dob").Value)%></td>
          </tr>
          <tr>
          <td width="25%">Location</td>
          <td width="75%"><%=(rsgirls.Fields.Item("location").Value)%></td>
          </tr>
          <tr>
          <td>Fighting weight </td>
          <td><%=(rsgirls.Fields.Item("weight").Value)%></td>
          </tr>
          <tr>
          <td width="25%">Height</td>
          <td width="75%"><%=(rsgirls.Fields.Item("height").Value)%></td>
          </tr>
          <tr>
          <td width="25%">Fight record </td>
          <td width="75%"><%=(rsgirls.Fields.Item("bouts").Value)%></td>
          </tr>
          <tr>
          <td width="25%">I am looking for </td>
          <td width="75%"><%=(rsgirls.Fields.Item("lookingfor").Value)%></td>
          </tr>
          <tr>
          <td width="25%">Contact number </td>
          <td width="75%"><%=(rsgirls.Fields.Item("clubphone").Value)%></td>
          </tr>
          <tr>
          <td width="25%">Email Address</td>
          <td width="75%"><a href="mailto:<%=(rsgirls.Fields.Item("email").Value)%>">Click here to email me </a></td>
          </tr>
          <tr valign="top">
          <td colspan="2"><p align="center"><a href="http://www.boxergirl.co.uk/images/<%=(rsgirls.Fields.Item("pic2").Value)%>"><img src="http://www.boxergirl.co.uk/images/<%=(rsgirls.Fields.Item("pic1").Value)%>" hspace="10" vspace="10" border="0"></a></p> </td>
          </tr>
          <tr valign="top">
          <td colspan="2"><%=Response.Write formatMessage(rsgirls.Fields("anythingelse"))%></td></tr>
          </table>
          <p>&nbsp;</p>
          <!-- InstanceEndEditable --></td>
          <td width="15" valign="bottom" rowspan="2"><img border="0" src="images/clear_dot.gif" width="15" height="1"></td>
          </tr>
          <tr>
          <td width="5" valign="bottom" bgcolor="#C4373A"><img border="0" src="images/clear_dot.gif" width="5" height="1">
          </td>
          <td width="130" valign="bottom" bgcolor="#C4373A">&nbsp;</td>
          <td width="5" valign="bottom" bgcolor="#C4373A"><img border="0" src="images/clear_dot.gif" width="5" height="1">
          </td>
          <td width="100%" valign="top"><div align="center">
          <p>&nbsp;</p>
          </div></td>
          </tr>
          </table>
          </td>
          <td bgcolor="#000000" valign="bottom" width="2"><img border="0" src="images/clear_dot.gif" width="2" height="1"></td>
          </tr>
          <tr>
          <td colspan="3" bgcolor="#000000"><img border="0" src="images/clear_dot.gif" width="1" height="2"></td>
          </tr>
          </table>
          </center>
          </div>
          </body>
          <!-- InstanceEnd --></html>
          <%
          rsgirls.Close()
          Set rsgirls = Nothing
          %>
          Fitness for life!www.fitness-focus.co.uk


          DIFN - Doing nothing is not an option

          The Supplement Warehouse - Bodybuilding & Fitness Supplements

          Comment

            #6
            <%=Response.Write formatMessage(rsgirls.Fields("anythingelse"))%>

            Try removing the "=" sign this is not needed,

            <%Response.Write formatMessage(rsgirls.Fields("anythingelse"))%>

            This does work...

            Peter

            Comment

              #7
              Whilst the page now works it doesn't format the memo field. Take a look at http://www.boxergirl.co.uk/girls1.asp and click on one of the boxers to see what I mean.
              Fitness for life!www.fitness-focus.co.uk


              DIFN - Doing nothing is not an option

              The Supplement Warehouse - Bodybuilding & Fitness Supplements

              Comment

                #8
                puzzled

                Owen
                I am not sure why it is not working, are you sure the paragraphs actually have line breaks and cariage returns?.
                Here is a small peice of code I have written that does work.
                I copied the text about Laura, formatted it in Notepad then pasted it into my database and it does work..

                Try creating a asp page (just call it boxing.asp) from this code and running it on your server to see what happens.
                -------------------------------------------------------------------
                <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
                <%
                OPTION EXPLICIT
                %>
                <html>
                <head>
                <title>boxing</title>
                <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
                <%
                Dim ConnObj
                Dim title
                Dim rsGirls
                title = "boxing"

                Set ConnObj = Server.CreateObject("ADODB.Connection")
                Set rsGirls = Server.CreateObject("ADODB.RecordSet")

                '//change the dsn to yours
                ConnObj.Open "dsnProjectBlogger","adOpenKeyset","adLockOptimistic"

                function formatMessage(Message)
                Dim strMessage
                strMessage = Message
                strMessage = Replace(strMessage, CHR(13), "")
                strMessage = Replace(strMessage, CHR(10), "</P><P>")
                strMessage = Replace(strMessage, CHR(10), "<P>")
                formatMessage = strMessage
                end function
                %>
                </head>
                <%
                '//tblMessages will need to be changed to your table
                Set rsGirls = ConnObj.Execute("SELECT title, message FROM tblMessages WHERE title = '"&title&"' ")
                %>
                <body>
                <table width="760" border="1" cellspacing="0" cellpadding="0">
                <tr>
                <td><%Response.Write formatMessage(rsgirls.Fields("message"))%>
                </td>
                </tr>
                </table>
                </body>
                </html>

                --------------------------------------------------------------------

                Comment

                Previous template Next
                Working...
                X