# SQL UPDATE Problem



## bordois (Jun 24, 2007)

*Basically I am wishing to update an existing record to include image filenames

I'm using response.write to print the SQL query on screen but can't seem to see why nothing is being submitted to the database.

Heres the code below;*

<%@ Language=VBScript %>

<%
' make VB declare all variables
option explicit
Dim conx ' connection object to the server
Dim sql
Dim sql2
Dim NumberOfRecords

NumberOfRecords=0

' First a variable is created to make a ADO connection to the database
set conx=server.CreateObject("adodb.connection")

' This is the OLEDB version of opening a database
conx.Provider="Microsoft.JET.OLEDB.4.0"
conx.Open Server.Mappath("database/righthere.mdb")

sql="UPDATE places SET "
sql=sql & "photo1='" & Request("photo1") & "',"
sql=sql & "photo2='" & Request("photo2") & "',"
sql=sql & "photo3='" & Request("photo3") & "',"
sql=sql & "photo4='" & Request("photo4") & "',"
sql=sql & " WHERE place_id='" & session("id_place") & "'"
on error resume next

conx.Execute(sql)

conx.close
set conx=Nothing
%>

*In the HTML section of the page is the following If statement to check for added records;*

<%
If (NumberOfRecords ="1") Then%>

<%Response.Write "Your Place of Interest was successfully added to the database.
This listing will be available once moderated.

"%>

<%Else%>

<%Response.Write "ERROR - your listing was not successful, please try again

" & sql%>

<%End if	
%>

*Here is the output onscreen once the page is processed;*

ERROR - your listing was not successful, please try again

UPDATE places SET photo1='volt2.jpg',photo2='',photo3='',photo4='', WHERE place_id='13'

*Any help would be much appreciated, I've spent too long trying different methods to get it working!*


----------



## MMJ (Oct 15, 2006)

I don't think there is a final comma.


----------



## bordois (Jun 24, 2007)

After the script decided to work it appears to be a one-off as with re-testing the image filenames are not being placed into the database.

Any ideas?


----------



## MMJ (Oct 15, 2006)

bordois said:


> After the script decided to work it appears to be a one-off as with re-testing the image filenames are not being placed into the database.
> 
> Any ideas?




The final comma before the WHERE command.


----------



## bordois (Jun 24, 2007)

I've removed the final comma but still no sign of the image filename in the database.

Just can't understand why it worked once and now it won't 


```
sql="UPDATE places SET "
  sql=sql & "photo1='" & Request("photo1") & "',"
  sql=sql & "photo2='" & Request("photo2") & "',"
  sql=sql & "photo3='" & Request("photo3") & "',"
  sql=sql & "photo4='" & Request("photo4") & "'"
  sql=sql & " WHERE place_id='" & session("id_place") & "'"
  on error resume next
```


----------



## MMJ (Oct 15, 2006)

You can try UPDATE *`*places*`*...

Doesn't ASP ahve a function that dumps the mysql error?


----------

