Recall & Update Records for Access Databases - Resolved

  • anyusernamewilldo
  • Novice
  • Novice
  • anyusernamewilldo
  • Posts: 22

Post 3+ Months Ago

about a week ago i posted a topic named "updating a record". Well I've cracked it and thought i'd share my findings.

I have an access database
i pull my record into a form so i can edit the existing info
submition runs the update / delete script leaving no duplicate record and the new data inserted



Here is the code that Displays your Records in a form so you can edit the information stored.
Each record has it's own update button that runs the code i've listed second


Code: [ Select ]
<%
' Declaring variables
Dim rs1, data_source1, no1
no1 = 0
data_source1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    Server.MapPath("downloadform.mdb")

' Creating Recordset Object and opening the database
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open "SELECT * FROM users ORDER BY dateposted DESC ", data_source1


' Looping through the records to show all of them
While Not rs1.EOF
    Response.Write "<form action='form_acdownloadupdate.asp' method='post'>"
    Response.Write "******************" & "<br>"
    Response.Write "<strong>Original Name :</strong> " & rs1("name") & "<br>"
    Response.Write "<strong>Name :</strong> <input type='text' size='50' name='name' value='" & rs1("name") & "'>" & "<br>"
    Response.Write "<strong>DatePosted :</strong> <input type='text' size='10' name='dateposted' value='" & rs1("dateposted") & "'>"
    Response.Write "<strong>File Size :</strong> <input type='text' size='10' name='comments' value='" & rs1("comments") & "'>" & "<br>"
    Response.Write "<strong>Link :</strong> <input type='text' size='60' name='topic' value='" & rs1("topic") & "'>" & "<br>"
    Response.Write "<strong>Genre :</strong> <input type='text' size='15' name='genre' value='" & rs1("genre") & "'>" & "<br>"
    Response.Write "<input type='hidden' name='id' value='" & rs1("id") & "'>"
    Response.Write "<input type='submit' value='Update Record'>" & " "
    Response.Write "</form>"
    no1 = no1 + 1
    rs1.MoveNext
Wend

' Done. Now close the Recordset
rs1.Close
Set rs1 = Nothing

Response.Write "<p>Latest : " & no1
%>
  1. <%
  2. ' Declaring variables
  3. Dim rs1, data_source1, no1
  4. no1 = 0
  5. data_source1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  6.     Server.MapPath("downloadform.mdb")
  7. ' Creating Recordset Object and opening the database
  8. Set rs1 = Server.CreateObject("ADODB.Recordset")
  9. rs1.Open "SELECT * FROM users ORDER BY dateposted DESC ", data_source1
  10. ' Looping through the records to show all of them
  11. While Not rs1.EOF
  12.     Response.Write "<form action='form_acdownloadupdate.asp' method='post'>"
  13.     Response.Write "******************" & "<br>"
  14.     Response.Write "<strong>Original Name :</strong> " & rs1("name") & "<br>"
  15.     Response.Write "<strong>Name :</strong> <input type='text' size='50' name='name' value='" & rs1("name") & "'>" & "<br>"
  16.     Response.Write "<strong>DatePosted :</strong> <input type='text' size='10' name='dateposted' value='" & rs1("dateposted") & "'>"
  17.     Response.Write "<strong>File Size :</strong> <input type='text' size='10' name='comments' value='" & rs1("comments") & "'>" & "<br>"
  18.     Response.Write "<strong>Link :</strong> <input type='text' size='60' name='topic' value='" & rs1("topic") & "'>" & "<br>"
  19.     Response.Write "<strong>Genre :</strong> <input type='text' size='15' name='genre' value='" & rs1("genre") & "'>" & "<br>"
  20.     Response.Write "<input type='hidden' name='id' value='" & rs1("id") & "'>"
  21.     Response.Write "<input type='submit' value='Update Record'>" & " "
  22.     Response.Write "</form>"
  23.     no1 = no1 + 1
  24.     rs1.MoveNext
  25. Wend
  26. ' Done. Now close the Recordset
  27. rs1.Close
  28. Set rs1 = Nothing
  29. Response.Write "<p>Latest : " & no1
  30. %>



Here is the code that inserts the updated record & deletes the original record:
Code: [ Select ]
<form action="form_acdownloadupdate.asp" method="post">

<%
' Declaring variables
Dim name, dateposted, topic, comments, genre, data_source, con, sql_insert

' A Function to check if some field entered by user is empty
Function ChkString(string)
    If string = "" Then string = " "
    ChkString = Replace(string, "'", "''")
End Function

' Receiving values from Form
name = ChkString(Request.Form("name"))
dateposted = ChkString(Request.Form("dateposted"))
topic = ChkString(Request.Form("topic"))
comments = ChkString(Request.Form("comments"))
genre = ChkString(Request.Form("genre"))
id = ChkString(Request.Form("id"))

' Set your Access database Filename here:
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("downloadform.mdb")

' Inserting the new updated Record fields
sql_insert = "insert into users (name, dateposted, topic, comments, genre ) values ('" & _
    name & "', '" & dateposted & "', '" & topic & "', '" & comments & "', '" & genre & "')"

' Deleting the Original Record
sql_delete = " delete from users where id = " & id


' Creating Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert
con.Execute sql_delete

' Done. Close the connection
con.Close
Set con = Nothing
%>

Update completed Successfully!
</form>
  1. <form action="form_acdownloadupdate.asp" method="post">
  2. <%
  3. ' Declaring variables
  4. Dim name, dateposted, topic, comments, genre, data_source, con, sql_insert
  5. ' A Function to check if some field entered by user is empty
  6. Function ChkString(string)
  7.     If string = "" Then string = " "
  8.     ChkString = Replace(string, "'", "''")
  9. End Function
  10. ' Receiving values from Form
  11. name = ChkString(Request.Form("name"))
  12. dateposted = ChkString(Request.Form("dateposted"))
  13. topic = ChkString(Request.Form("topic"))
  14. comments = ChkString(Request.Form("comments"))
  15. genre = ChkString(Request.Form("genre"))
  16. id = ChkString(Request.Form("id"))
  17. ' Set your Access database Filename here:
  18. data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  19. Server.MapPath("downloadform.mdb")
  20. ' Inserting the new updated Record fields
  21. sql_insert = "insert into users (name, dateposted, topic, comments, genre ) values ('" & _
  22.     name & "', '" & dateposted & "', '" & topic & "', '" & comments & "', '" & genre & "')"
  23. ' Deleting the Original Record
  24. sql_delete = " delete from users where id = " & id
  25. ' Creating Connection Object and opening the database
  26. Set con = Server.CreateObject("ADODB.Connection")
  27. con.Open data_source
  28. con.Execute sql_insert
  29. con.Execute sql_delete
  30. ' Done. Close the connection
  31. con.Close
  32. Set con = Nothing
  33. %>
  34. Update completed Successfully!
  35. </form>


- you might want to order your results around your site by date rather than the ID number otherwise your results may not stay in good order as an updated record will always have the highest ID number.


This really had me stuck for about a week and i'm really pleased to have solved it. I'm obviously fairly new to all this and this is a very rewarding acheivement to have obtained on my own. I hope this can help others out that may be stuck in a similar situation.

If any one has a more direct way to update without inserting a new record and removing the original please share! lol
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

Post Information

  • Total Posts in this topic: 1 post
  • Users browsing this forum: No registered users and 139 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.