SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Oct 2003
    Location
    Maine
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    trouble with form updating Access DB table

    I've been asked to create a web form to edit a couple of fields in one record in an Access database. The database is on the same server as the website.

    This is part of a simple CMS that someone developed a long time ago in traditional ASP with VB. I don't know VB well and am trying to get something working by adapting the existing code used in the CMS. I can get the form to display the current values in the record (there are only two fields that need to be edited), but I can't get the form to successfully update the record when the user clicks the submit button. I'm sure the solution is simple but I've googled up down and sideways and can't seem to get this to work. If anybody with a little patience has a minute to look at this and offer a solution, I'd be grateful.

    Here's a link to the page in question:

    http://www.penobscotbayinn.com/admin...ameDefault.asp (click on "edit rates page text") on the menu.

    If you submit the form you get a HTTP500 error.

    Thanks for your time. This seems like it must be a dirt simple procedure, but I'm not very conversant in VB.

    Here's the code:
    Code:
    # 1. <%@ Language=VBScript %>    
    #   2. <% option Explicit %>    
    #   3. <% 'Response.Buffer = true %>    
    #   4. <!--#include file="../adovbs.inc"-->    
    #   5. <!--#include file="../ConnectionString.asp"-->    
    #   6. <!--#include file="SqlFormattingFunctions.asp"-->    
    #   7.     
    #   8. <%    
    #   9. 'If Session("bolAuthenticated") = False Then    
    #  10.  ' Response.Redirect "AdminLogon.asp?"    
    #  11. 'End If    
    #  12.     
    #  13. dim  oConn, oRecSet,strConn,oCmd, LanguageChosen,Upload    
    #  14. dim action,Pid ,strSql,ID,CompareID,I,OldLogoName,uploadPath,StrConnNews    
    #  15. dim Title,FirstHeader,databaseTable,NumElements    
    #  16. ' Template for the administration of a database table     
    #  17. ' there are four sections     
    #  18. ' 1. The display of the information with a edit or delete option     
    #  19. ' 2. Add , this has the processing and the form     
    #  20. ' 3 Edit     
    #  21. ' 4 Delete     
    #  22. 'These are the items that you may change     
    #  23.     
    #  24.     
    #  25. ' we then set the tableName and the headers    
    #  26.     Title = "Edit Room Rates Page Text"    
    #  27.     FirstHeader = "Edit Room Rates Page Text"    
    #  28.     DatabaseTable = "rates_page_text"    
    #  29.     
    #  30.     
    #  31.     
    #  32.     
    #  33.     
    #  34. const Name = 1    
    #  35. const value = 2    
    #  36. dim element(3,15)    
    #  37. dim TextAreaRows(3), TextareaCols(3) ' rows and cols for three text areas    
    #  38. 'Now we set the field names and types     
    #  39. NumElements = 2 ' the array starts at 0 which will always be the ID  We then make NumElements  the last number used ( it must be exact)    
    #  40. element(Name,0) = "id"    
    #  41. element(Name,1) = "heading"     ' A Text Box        
    #  42. element(Name,2) = "text"        ' a text area    
    #  43. TextAreaRows(1) = 8    
    #  44. TextareaCols(1)=30    
    #  45.     
    #  46.     
    #  47.     
    #  48.     
    #  49. %>    
    #  50.     
    #  51. <html>    
    #  52. <head>    
    #  53.     <title><%=Title%></title>    
    #  54. <link rel="stylesheet" href="AdminStyleSheet.css" type="text/css">    
    #  55. </head>    
    #  56. <body>    
    #  57. <H1><%=FirstHeader%> </h1>    
    #  58. <%     
    #  59.     
    #  60.     
    #  61.     Action = "" ' clear it    
    #  62.     Action =  Request.QueryString("action")    
    #  63.     'Response.Write(action)    
    #  64.     
    #  65.     
    #  66.     
    #  67. '################           UPDATE       ########################################       
    #  68. '*************************************************************************    
    #  69. '                   Processing for  UPDATEing   from the form on this page     
    #  70. '************************************************************************    
    #  71. if  Action = "Update" then     
    #  72.             ' set up the database    
    #  73.         '**************************************    
    #  74.         'do the file upload  the destination folder must have everyone full permission    
    #  75.         Set Upload = Server.CreateObject("Persits.Upload.1")    
    #  76.         ' We must call Upload.Save or SaveToMemory before we can use Upload.Form!    
    #  77.         ' Most of the errors here are related to the permissions on the folder that gets the files    
    #  78.         Upload.Save uploadPath    
    #  79.     ' ******************************************            
    #  80.         CompareID =  Upload.Form("id")    
    #  81.       Set oConn = Server.CreateObject("ADODB.Connection")    
    #  82.         oConn.Open(strConn)    
    #  83.         'for each objItem in Request.Form     
    #  84.         '   Response.Write objItem & "=" & Request.Form(objItem) & "<Br>"    
    #  85.         'next       
    #  86.     
    #  87.         for I = 1 to NumElements            
    #  88.             element(value,I) =  Upload.Form(element(Name,I))    
    #  89.             'Response.Write element(Name,I) & Upload.Form(element(Name,I)) & element(value,I) &"<BR>"    
    #  90.         next     
    #  91.             
    #  92.             'If Not Upload.Files(element(Name,NumElements)) Is Nothing Then  ' make sure they are uploading a file before we do this    
    #  93.                 'element(value,NumElements) =Upload.Files(1).ExtractFileName    
    #  94.             'else    
    #  95.                 'element(value,NumElements)  = upload.Form("OldLogoName")    
    #  96.             'end if    
    #  97.         ' for known text areas    
    #  98.             element(value,2) =  insertDoubleApostrophe(element(value,2))    
    #  99.             
    # 100.                  ' Build a Sql string for inserting into the database     
    # 101.         strSQL = "Update  " & DatabaseTable &  " set " & element(Name,1)  & "='" & element(Value,1)& "'"     
    # 102.         for I = 2 to NumElements    
    # 103.             strSQL = strSQL & "," &  element(Name,2)  & "='" & element(Value,2)& "'"     
    # 104.         next    
    # 105.         strSQL = strSQL & "  where ID="& CompareID & ""    
    # 106.     
    # 107.         'Response.Write strSQL    
    # 108.         set oCmd = server.CreateObject("ADODB.command")    
    # 109.         set oCmd.ActiveConnection = oConn    
    # 110.         oCmd.CommandText = strSQL    
    # 111.         ocmd.CommandType = adcmdtext    
    # 112.         oCmd.Execute    
    # 113.         set oCmd = nothing    
    # 114.         %><A HREF="RoomRateTextEditor?Action=ReviewforEdit&id=1">Update complete</A> <br><%     
    # 115. end if   'Update    
    # 116.     
    # 117. '*************************************************************************    
    # 118. '               form for    the REVIEW of the contents for an update     
    # 119. '************************************************************************       
    # 120.     if  Action = "ReviewforEdit" then     
    # 121.         pid=Request.QueryString("id")    
    # 122.         ' set up the database    
    # 123.         Set oConn = Server.CreateObject("ADODB.Connection")    
    # 124.         'strConn is included    
    # 125.         oConn.Open(strConn)    
    # 126.         set oRecSet = server.CreateObject("ADODB.recordset")  ' page content header and body     
    # 127.             oRecSet.CursorType=adOpenStatic         'Using the ADOVBS include file    
    # 128.             oRecSet.LockType=adLockBatchOptimistic    
    # 129.             STRsql = "SELECT * FROM  " & DatabaseTable &  "  WHERE id ="& pid    
    # 130.             oRecSet.Open STRsql, oConn    
    # 131.                     
    # 132.     
    # 133.     'load the array             
    # 134.     for I = 1 to NumElements    
    # 135.      element(Value,I)=   oRecSet(element(Name,I))    
    # 136.     ' Response.Write  element(Value,I)    
    # 137.     Next    
    # 138.     %>    
    # 139.     
    # 140.         
    # 141. <html>    
    # 142. <head>    
    # 143.     <title><%=Title%></title>    
    # 144. </head>    
    # 145. <body>    
    # 146. <%'Response.Write Action  & "ACTION"%>    
    # 147. <form action="RoomRateTextEditor.asp?Action=Update" method=post  ENCTYPE="multipart/form-data"  name=form2>    
    # 148.     <table width="600">    
    # 149.       <input type=hidden name=ID value="<%= oRecSet(element(Name,0)).value %>" >    
    # 150.     <tr> <% ' TEXT BOX %>    
    # 151.         <td><%=element(Name,1)%>:</td>    
    # 152.         <td>     
    # 153.         <input type="text"    name="<%=element(Name,1)%>"  value="<%=element(Value,1)%>"  >    
    # 154.        </td>    
    # 155.     </tr>    
    # 156.     <tr><% ' TEXT AREA %>    
    # 157.         <td><%=element(Name,2)%>:</td>    
    # 158.         <td> <TEXTAREA    name="<%=element(Name,2) %>"  rows=<%=Textarearows(1)%> cols= <%=TextAreacols(1)%>    ><%=element(Value,2)%> </TEXTAREA>    
    # 159.     </td>    
    # 160.     </tr>     
    # 161.     <tr>    
    # 162.         <td>    
    # 163.             
    # 164.         
    # 165.     <tr><!--  keep this -->    
    # 166.         <td>    
    # 167.         <input type=submit value=Submit id=submit1 name=submit1>    
    # 168.     </td>    
    # 169.         </tr>    
    # 170.     </table>    
    # 171. </form>    
    # 172. </body>    
    # 173. </html>    
    # 174. <%    
    # 175.                 oRecSet.close    
    # 176.                 set oRecSet = nothing    
    # 177.     
    # 178.     end if          
    # 179. '       
    # 180.     
    # 181.     
    # 182.     
    # 183. %>

  2. #2
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this code is a mess.

    They are trying to use Persits upload component to capture form values; totally unnecessary.

    Just use request.form to capture values and field names then use sql to update the database.

  3. #3
    SitePoint Member
    Join Date
    Oct 2003
    Location
    Maine
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you point me to an example of how this is done? I'm not turning anything up on Google, and my VB experience is very minimal. I can handle the SQL query part...

    Should I just scrap all this old code I inherited and just start from scratch?

  4. #4
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    http://www5.brinkster.com/hiflyer/db/dbtest.asp

    Might be teaching your granny to suck eggs, but you might find it useful, especially Part 3.
    Ian Anderson
    www.siteguru.co.uk

  5. #5
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To get form values with ASP you can follow this example

    http://www.irt.org/script/5814.htm

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,029
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mister Ed View Post
    Can you point me to an example of how this is done? I'm not turning anything up on Google, and my VB experience is very minimal. I can handle the SQL query part...

    Should I just scrap all this old code I inherited and just start from scratch?
    That's a bit of a hard call without seeing the rest of the app but if it were me I would probably save the old one (just in case) and create a new version of the RoomRateEditor.asp. As webber123456 pointed out, they are are using a multipart form and an upload object for no apparent reason. They are also GETing RoomRateEditor.asp on submit and passing the actiion argument through the querystring which just doesn't make sense.

    This could be done in a much more simple fashion with just a regular form without the multipart... ie:
    Code:
    <form action="RoomRateTextEditor.asp" method="post"  name=form2>    
    <input type="hidden" name="Action" value="Update" />
    What webber123456 was suggesting you do to get the values of the submitted form is simply do the following:
    Code:
    RoomNo = request.form("RoomNo")
    Description = request.form("Description")
    
    ' ... and so on
    
    ' Then build your update query and execute it
    When you search for Classic ASP code examples, you'll run into a lot of garbage so I would suggest that you prefix your query with "vbscript" as in "vbscript submit form example" which will pull up lots of examples. Also search ASPin www.aspin.com. There's lots to find in there.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,029
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Actually, there's an error in my first paragraph
    They are also GETing RoomRateEditor.asp on submit and passing the actiion argument through the querystring which just doesn't make sense.
    It should be:
    They are also POSTing RoomRateEditor.asp on submit and passing the action argument through the querystring which just doesn't make sense. It probably works but it makes it difficult to follow the code on first examination.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development


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
  •