Updating a Record

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

Post 3+ Months Ago

Hi all, I have an access database and wish to update the records via a form. I can recall the records to a form just fine but when i click update I am receiving errors in my Update query.

Here is the update query,
Whats wrong with this? Thanks in Advance to any pointers.
Code: [ Select ]
<%

sql_insert = "UPDATE users SET "
 sql=sql & "name='" & Request.Form("name") & "',"
 sql=sql & "dateposted='" & Request.Form("dateposted") & "',"
 sql=sql & "topic='" & Request.Form("topic") & "',"
 sql=sql & "comments='" & Request.Form("comments") & "',"
 sql=sql & "genre='" & Request.Form("genre") & "',"
 sql=sql & " WHERE id='" & id & "'"

%>
  1. <%
  2. sql_insert = "UPDATE users SET "
  3.  sql=sql & "name='" & Request.Form("name") & "',"
  4.  sql=sql & "dateposted='" & Request.Form("dateposted") & "',"
  5.  sql=sql & "topic='" & Request.Form("topic") & "',"
  6.  sql=sql & "comments='" & Request.Form("comments") & "',"
  7.  sql=sql & "genre='" & Request.Form("genre") & "',"
  8.  sql=sql & " WHERE id='" & id & "'"
  9. %>



Any help greatly appreaciated.
Im totaly stuck, I've run out of good tutorials to read to assist me.
  • anyusernamewilldo
  • Novice
  • Novice
  • anyusernamewilldo
  • Posts: 22

Post 3+ Months Ago

Ok i've notice about 40 Views (probably all me :D) and no replies. - So forget the code above, - Can anyone tell me the correct way to write an Update query please?

even if someone could tell me the paths i need to research into? EG: nested strings, Dynamic SQL ect.

Im trying to:
pull a record into a form (no problems here)
edit the data in the form and then click update to 'overwrite' the record pulled into form

I need some pointers on the update bit.
How do I tell the update query to use the data in the form

If someone could show me an example of an update query that is using data from a form i couldn't thank you enough.

I've found many tutorials but they all seem to use static text preset into the sql code.
i can't find anything thats using variables

Thanks in advance to anyone who can help me out.
thanks




Any help greatly appreaciated.
Im totaly stuck, I've run out of good tutorials to read to assist me.[/quote]
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Perhaps you can share the error you're receiving? Your query looks fine, which is why I ask.
  • anyusernamewilldo
  • Novice
  • Novice
  • anyusernamewilldo
  • Posts: 22

Post 3+ Months Ago

AH thanks for taking a look guys... At the moment im getting Internal Server Error 500 :(

Heres my update code:

Code: [ Select ]
<%
' Declaring variables
Dim id, 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

' Creating Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("downloadform.mdb")


' Receiving values from Form and performing record update

con.Execute sql_insert

sql_insert = UPDATE users SET 
sql=sql & "name='" & Request.Form("name") & "',"
sql=sql & "dateposted='" & Request.Form("dateposted") & "',"
sql=sql & "topic='" & Request.Form("topic") & "',"
sql=sql & "comments='" & Request.Form("comments") & "',"
sql=sql & "genre='" & Request.Form("genre") & "',"
sql=sql & " WHERE id='" & id & "'"


' Please work and close the connection
con.Close
Set con = Nothing
%>
  1. <%
  2. ' Declaring variables
  3. Dim id, name, dateposted, topic, comments, genre, data_source, con, sql_insert
  4. ' A Function to check if some field entered by user is empty
  5. Function ChkString(string)
  6.     If string = "" Then string = " "
  7.     ChkString = Replace(string, "'", "''")
  8. End Function
  9. ' Creating Connection Object and opening the database
  10. Set con = Server.CreateObject("ADODB.Connection")
  11. con.Open data_source
  12. data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  13. Server.MapPath("downloadform.mdb")
  14. ' Receiving values from Form and performing record update
  15. con.Execute sql_insert
  16. sql_insert = UPDATE users SET 
  17. sql=sql & "name='" & Request.Form("name") & "',"
  18. sql=sql & "dateposted='" & Request.Form("dateposted") & "',"
  19. sql=sql & "topic='" & Request.Form("topic") & "',"
  20. sql=sql & "comments='" & Request.Form("comments") & "',"
  21. sql=sql & "genre='" & Request.Form("genre") & "',"
  22. sql=sql & " WHERE id='" & id & "'"
  23. ' Please work and close the connection
  24. con.Close
  25. Set con = Nothing
  26. %>
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

I would see it's more to do with how you're connecting and mapping to that access file. An incorrect query syntax shouldn't throw a 500.
  • anyusernamewilldo
  • Novice
  • Novice
  • anyusernamewilldo
  • Posts: 22

Post 3+ Months Ago

I've tidied it up a bit and messed around with it some more but im still getting HTTP 500 Internal server error messages

Any pointers?


Code: [ Select ]
<%
' Declaring variables
Dim name, id, dateposted, topic, comments, genre, data_source, rs1, sql_insert

data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    Server.MapPath("downloadform.mdb")

' Creating Connection Object and opening the database
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.open "SELECT * FROM users ", data_source


sql_insert = UPDATE users SET 
sql=sql & "name='" & Request.Form("name") & "',"
sql=sql & "dateposted='" & Request.Form("dateposted") & "',"
sql=sql & "topic='" & Request.Form("topic") & "',"
sql=sql & "comments='" & Request.Form("comments") & "',"
sql=sql & "genre='" & Request.Form("genre") & "',"
sql=sql & " WHERE id='" & id & "'"


rs1.Execute sql_insert


' Done. Close the connection

rs1.Close
Set rs1 = Nothing

%>
  1. <%
  2. ' Declaring variables
  3. Dim name, id, dateposted, topic, comments, genre, data_source, rs1, sql_insert
  4. data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  5.     Server.MapPath("downloadform.mdb")
  6. ' Creating Connection Object and opening the database
  7. Set rs1 = Server.CreateObject("ADODB.Recordset")
  8. rs1.open "SELECT * FROM users ", data_source
  9. sql_insert = UPDATE users SET 
  10. sql=sql & "name='" & Request.Form("name") & "',"
  11. sql=sql & "dateposted='" & Request.Form("dateposted") & "',"
  12. sql=sql & "topic='" & Request.Form("topic") & "',"
  13. sql=sql & "comments='" & Request.Form("comments") & "',"
  14. sql=sql & "genre='" & Request.Form("genre") & "',"
  15. sql=sql & " WHERE id='" & id & "'"
  16. rs1.Execute sql_insert
  17. ' Done. Close the connection
  18. rs1.Close
  19. Set rs1 = Nothing
  20. %>

Post Information

  • Total Posts in this topic: 6 posts
  • Users browsing this forum: No registered users and 55 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
 
cron
 

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