SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    new york
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Parsing strings issue to Access DB

    I hope someone can help me with this... I can't figure it out.

    I have this ASP script that does [UPDATE, DELETE, and ADD] records of an ACCESS database.

    The problem that I am having is with the textarea box. The UPDATE and the ADD works sometimes but not all the time. I think its an issue with certain characters.

    For example (the following text will not UPDATE or ADD):

    The 2004 PGA Grand Slam of Golf, the most exclusive event in Golf brings together the winners of the year's four major championships competing for a $1,000,000 purse. The two-day, 36-hole event -which has the most difficult qualification requirement in the game- features the winners of The Masters, U.S. Open, British Open and PGA Championship.

    This year, Hawaii’s Poipu Bay Golf Course and the Hyatt Regency Kauai Resort & Spa mark their 10th consecutive year in hosting the PGA Grand Slam of Golf November 22-24. For more info: http://www.pga.com/grandslam/2004/

    Looking closely at the above text, there are the following special characters: commas, foward slash, colon, hyphens, ampersands, apostrophes, periods, dollar signs, carriage returns.

    I assume I need some function that replaces the characters above fo rthe UPDATE and ADD to the database. Then another function that replaces the functions back when reading the database. I just don't know how to go about this and where in my script. I am not great with strings.

    My script is as follows:

    Code:
    <HTML>
    
    <BODY>
    
    <B>ADMIN</B>
    
    <%
    
    Actionvar=Request.QueryString("actionvar")
    
    Set conn = server.createobject("adodb.connection")
    
    DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
    
    DSNtemp=dsntemp & "DBQ=" & server.mappath("database_catalogue.mdb")
    
    conn.Open DSNtemp
    
    'If the QueryString has the Actionvar = add then generate
    
    'the page for adding items
    
    IF Actionvar="add" THEN
    
    IF Len(TRIM(request.form("flag"))) = 0 THEN
    
        %>
    
        Add<HR>
    
        <FORM METHOD="post" ACTION="../catalogue/admin.asp?Actionvar=add">
    
    <table border=1>
    
         <tr><td><B>ProgramID:</B></td><td><INPUT TYPE="number" NAME="programID" size=90><BR></td></tr>
    
         <tr><td><B>Image:</B></td><td> <INPUT TYPE="text" NAME="image" size=90><BR></td></tr>
    
         <tr><td><B>Program:</B></td><td> <INPUT TYPE="text" NAME="program" size=90><BR></td></tr>
    
         <tr><td><B>Episodes:</B></td><td> <INPUT TYPE="text" NAME="episodes" size=90><BR></td></tr>
    
         <tr><td><B>HD:</B></td><td> <INPUT TYPE="text" NAME="hd" size=90><BR></td></tr>
    
         <tr><td><B>content:</B></td><td> <textarea TYPE="memo" NAME="content" rows="10" cols="70"></textarea><BR></td></tr>
    
         <tr><td>&nbsp;</td><td>
    
        <INPUT TYPE="hidden" NAME="flag" VALUE="2"><br>
    
        <INPUT TYPE="submit" VALUE="ADD">
    
        </FORM></td></tr></table>
    
     
    
        <%
    
    ELSEIF Request.Form("flag")="2" THEN
    
        SQLstmt = "INSERT INTO database_catalogue (programID,image,program,episodes,hd,content)"
    
        SQLstmt = SQLstmt & " VALUES ('" & request.form("programID") & "','" & request.form("image") & "','" & request.form("program") & "','" & request.form("episodes") & "','" & request.form("hd") & "','" & request.form("content") & "')"
    
        %>
    
        <HR>
    
        SQL statement: <%=SQLstmt%>
    
        <HR>
    
        <%
    
        conn.execute(SQLstmt)
    
        Response.Write "Operation Complete<BR><A HREF=""admin.asp"">Home</A>"
    
    END IF
    
    'If the QueryString has the Actionvar = update then generate
    
    'the page for updating items
    
    ELSEIF Actionvar="update" THEN
    
    IF Len(TRIM(Request.Form("flag"))) = 0 THEN
    
        SQLstmt = "SELECT * FROM database_catalogue WHERE ID=" & Request.QueryString("Recid")
    
        %>
    
        <HR>
    
        SQL statement: <%=SQLstmt%>
    
        <HR>
    
        <%
    
        Set rs = conn.Execute(SQLstmt)
    
        IF NOT RS.EOF THEN 
    
        %>
    
         Update<HR>
    
         <FORM METHOD="post" ACTION="../catalogue/admin.asp?Actionvar=update">
    
         <table border=1>
    
         <tr><td><B>ProgramID:</B></td><td><INPUT TYPE="number" NAME="programID" VALUE="<%=rs("programID")%>" size=90><BR></td></tr>
    
         <tr><td><B>Image:</B></td><td> <INPUT TYPE="text" NAME="image" VALUE="<%=rs("image")%>" size=90><BR></td></tr>
    
         <tr><td><B>Program:</B></td><td> <INPUT TYPE="text" NAME="program" VALUE="<%=rs("program")%>" size=90><BR></td></tr>
    
         <tr><td><B>Episodes:</B></td><td> <INPUT TYPE="text" NAME="episodes" VALUE="<%=rs("episodes")%>" size=90><BR></td></tr>
    
         <tr><td><B>HD:</B></td><td> <INPUT TYPE="text" NAME="hd" VALUE="<%=rs("hd")%>" size=90><BR></td></tr>
    
         <tr><td><B>content:</B></td><td> <textarea TYPE="memo" NAME="content" rows="10" cols="70"><%=rs("content")%></textarea><BR></td></tr>
    
         <tr><td>&nbsp;</td><td><INPUT TYPE="hidden" NAME="flag" VALUE="2">
    
         <INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("ID")%>"><br>
    
         <INPUT TYPE="submit" VALUE="Update">
    
         </FORM></td></tr></table>
    
         <% 
    
         rs.MoveNext
    
         rs.Close
    
        END IF
    
    ELSEIF Request.Form("flag")="2" THEN
    
        SQLstmt = "UPDATE database_catalogue SET "
    
        SQLstmt = SQLstmt & "programID='" & TRIM(Request.Form("programID")) & "', "
    
        SQLstmt = SQLstmt & "image='" & TRIM(Request.Form("image")) & "', "
    
        SQLstmt = SQLstmt & "program='" & TRIM(Request.Form("program")) & "', "
    
        SQLstmt = SQLstmt & "episodes='" & TRIM(Request.Form("episodes")) & "', "
    
        SQLstmt = SQLstmt & "hd='" & TRIM(Request.Form("hd")) & "', "
    
        SQLstmt = SQLstmt & "content='" & TRIM(Request.Form("content")) & "' "
    
        SQLstmt = SQLstmt & " WHERE ID=" & TRIM(Request.Form("Recordid")) & ";"
    
        %>
    
        <HR>
    
        SQL statement: <%=SQLstmt%>
    
        <HR>
    
        <%
    
        Conn.Execute (SQLstmt)
    
        Response.Write "Operation Complete<br><A HREF=""admin.asp"">Home</A>"
    
    END IF
    
    'If the QueryString has the Actionvar = delete then delete the item
    
    'and generate an "Operation Complete" page
    
    ELSEIF Actionvar="delete" THEN
    
    SQLstmt = "DELETE * FROM database_catalogue WHERE ID=" & TRIM(Request.QueryString("Recid"))
    
    %>
    
    <HR>
    
    SQL statement: <%=SQLstmt%>
    
    <HR>
    
    <%
    
    conn.execute(SQLstmt)
    
    Response.Write "Operation Complete<BR><A HREF=""admin.asp"">Home</A>"
    
    'If the QueryString Actionvar isn't set to anything, generate the list of items
    
    ELSE
    
    SQLstmt = "SELECT * FROM database_catalogue"
    
    %>
    
    <HR>
    
    SQL statement: <%=SQLstmt%>
    
    <HR>
    
    <%
    
    Set rs = conn.Execute(SQLstmt)
    
    %>
    
    <TABLE BORDER=1>
    
    <TR>
    
    <TD><CENTER><B>ProgramID</B></CENTER></TD>
    
    <TD>&nbsp;</TD>
    
    <TD><CENTER><B>Image</B></CENTER></TD>
    
    <TD>&nbsp;</TD>
    
    <TD><CENTER><B>Program</B></CENTER></TD>
    
    <TD>&nbsp;</TD>
    
    <TD><CENTER><B>Episodes</B></CENTER></TD>
    
    <TD>&nbsp;</TD>
    
    <TD><CENTER><B>HD</B></CENTER></TD>
    
    <TD>&nbsp;</TD>
    
    <TD><CENTER><B>content</B></CENTER></TD>
    
    <TD COLSPAN=2><CENTER><B>Modify</B></CENTER></TD>
    
    <%
    
    x=0
    
    DO WHILE NOT rs.EOF
    
        x=x+1
    
        Recid = rs("ID")
    
        programID = rs("programID")
    
        image = rs("image")
    
        program = rs("program")
    
        episodes = rs("episodes")
    
        hd = rs("hd")
    
        content = rs("content")
    
        %>
    
        <TR>
    
        <TD><%=x%>) <%=programID%></TD>
    
        <TD>&nbsp;</TD>
    
        <TD ALIGN=RIGHT><%=image%></TD>
    
        <TD>&nbsp;</TD>
    
        <TD ALIGN=RIGHT><%=program%></TD>
    
        <TD>&nbsp;</TD>
    
        <TD ALIGN=RIGHT><%=episodes%></TD>
    
        <TD>&nbsp;</TD>
    
        <TD ALIGN=RIGHT><%=hd%></TD>
    
        <TD>&nbsp;</TD>
    
        <TD><%=content%></TD>
    
        <TD><CENTER><A HREF="admin.asp?Actionvar=delete&Recid=<%=Recid %>">Delete</A></CENTER></TD>
    
        <TD><A HREF="admin.asp?Actionvar=update&Recid=<%=Recid %>">Update</A></TD>
    
        <%
    
        rs.MoveNext
    
    LOOP
    
    RS.Close
    
    Response.Write "</TABLE>"
    
    Response.Write "<HR><A HREF='admin.asp?actionvar=add'>Add a Record</A>"
    
    END IF
    
    conn.Close
    
    Set conn = nothing
    
    Set SQLstmt = nothing
    
    %>
    
    </BODY>
    
    </HTML>

  2. #2
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    When you say it will NOT UPDATE or ADD, do you get an error message, or does the data just not show up in your database?

    What is the datatype of the field you are inserting the data in to?

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    new york
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mongoloid - thank you for helping me. The textarea goes into a datatype memo in the access database.

    I just fixed the problem except for one part.

    I just made the following change everything works great. Change as follows:

    Code:
    SQLstmt = SQLstmt & "content='" & TRIM(replace(Request.Form("content"),chr(39),chr(39)&chr(39))) & "' "
    One thing though, the carriage returns. How do I deal with those.

    I assume another replace would be needed BUT how do I do two replaces on the same string?

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    new york
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Problem solved.

    I changed the code to the following and everything is working great:

    Code:
    SQLstmt = SQLstmt & "content='" & TRIM(replace(replace(Request.Form("content"),chr(39),chr(39)&chr(39)),"<br>",vbcrlf)) & "' "


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •