Member Avatar for goldeagle2005

Hey all,

Heres the code of an ASP I'm working on...

------------------------------------begin ASP-------------------------------------

<%@ Language=VBScript%>
<%
artist=Request("artist")
album=Request("album")

Set con1 = server.CreateObject ("ADODB.Connection")
Set rs1 = server.CreateObject ("ADODB.Recordset")


con1.Provider="Microsoft.Jet.OLEDB.4.0"
con1.Open (server.MapPath ("\databasecore\mediaguide.mdb"))

sql = "UPDATE artistinfo SET UserAlbumReview='"&TRIM(Request.Form("AlbumReview"))&"' where artist ='"&artist&"' and album =

'"&album&"'"
rs1.Open sql,con1
%>

<html>

<head>
<title>Add Album Review</title>
<meta name="Microsoft Theme" content="profile 111, default">
</head>

<body>
<form name=review method="POST" action="review.asp?done=1">
<p align="center">
<textarea rows="8" name="AlbumReview" cols="31"></textarea></p>
<p align="center">
&nbsp;<input type="submit" value="Submit"><input type="reset" value="Reset"></p>
</form>
&nbsp;</body></html>

--------------------------------------------end ASP---------------------------------


What I want is that when a user enters text in the textarea, the same should be inserted in the field named UserAlbumReview

in the database. However, this isnt happening. Nothing is inserted in the database. However, if I bubstitue a phrase in the

SQL query instead of the Request.Form statement, the string is inserted , if I change the SQL query to

sql = "UPDATE artistinfo SET UserAlbumReview='Test Review' where artist ='"&artist&"' and album = '"&album&"'"

then the phrase "Test Review" is inserted in the database. The values for artist and album are passed as parameters. Can any of you guys please help me? Thanks in advance.

  • 2 Contributors
  • 1 Reply
  • 193 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by senexom

Dani AI

Generated

Short diagnosis and practical fixes for (building on ’s debugging tip)

Most likely causes: the UPDATE runs unconditionally before the POST arrives, the page doesn’t preserve the artist/album values across the form POST, and the SQL can be broken by an apostrophe in the review text. Also, an UPDATE should be executed, not opened into a Recordset. Fix these three things and the insert will work.

Concrete steps to apply

  • Only run the UPDATE after the form is submitted (e.g. check a query flag or the request method).
  • Make sure artist and album are sent with the POST (hidden inputs are simplest).
  • Escape single quotes or, preferably, use a parameterized Command to avoid SQL errors and injection.
  • Use the Connection’s Execute method for UPDATE statements and add simple error checking.

Example snippets (place before the form HTML and after you’ve set artist/album server-side):

<input type="hidden" name="artist" value="<%= Server.HTMLEncode(artist) %>">
<input type="hidden" name="album"  value="<%= Server.HTMLEncode(album) %>">
' check submission, sanitize, then run the update
If Request.QueryString("done") = "1" Then
  review = Replace(Trim(Request.Form("AlbumReview")), "'", "''")
  artist = Replace(Trim(Request.Form("artist")), "'", "''")
  album  = Replace(Trim(Request.Form("album")),  "'", "''")
  con1.Execute "UPDATE artistinfo SET UserAlbumReview='" & review & "' WHERE artist='" & artist & "' AND album='" & album & "'"
End If

Extra tips

  • As suggested, echo the final values (and the SQL) while debugging so you can see what’s actually being passed.
  • Wrap DB calls with basic error handling (check Err.Number after Execute), always close connections, and set objects to Nothing.
  • For production, switch to an ADODB.Command with parameters to avoid manual escaping and SQL injection.
Member Avatar for senexom

For starters you need troubleshoot your variables...

When my sql is not working properly I like to write it out to screen before it executes to see what are the actual values being passed.

Response.Write(sql) and comment out the
' rs1.Open sql,con1

Also I'm not sure Request("myTextbox") is a proper way of retriving form variables in ASP. I always use Request.Form() or Request.QueryString()

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.