help needed

[needhelp26]

can someone please help with update statement?



sql = "Update tablename "
If request.form("field1") <> "" Then
sql = sql & "Set field1 = " & chr(34) & request.form("field1") & chr(34) & ","
End If


If request.form("field2") <> "" Then
sql = sql & "Set field2 = " & chr(34) & request.form("field2") & chr(34) & ","
End If
If right(sql,1) = "," Then
sql = Left(sql, len(sql)-1)
sql = sql & " where id = " & Request.form("id")



response.write result:

Update tablename Set field1 = "value", Set field2 = "value" where id = 2

I am getting a syntax error in Update statement. I have to eliminate the extra Set commands but don't know how to do. Can someone please help with the syntax. Thanks.

[glenngv]

Update tablename Set field1='value', field2='value' where id=2

[needhelp26]

How do I do within the if statement? Because I do have empty fields which I am trying to update and that is why I am doing something like this:

sql = "Update tablename "
if request.form("field1") <> "" Then
sql = sql & "Set field1 = " & chr(34) & request.form("field1") & chr(34) & ","
End If

In order to get the value within single quote, this should be statement:

sql = "Update tablename "
if request.form("field1") <> "" Then
sql = sql & "Set field1 = " & chr(34) & request.form('field1') & chr(34) & ","
End If


But it gives an error pointing to the single quote.

coding how empty fields look like:
' This page is taking to the another page which as the above update statement. This page is like review and update page.

<table>
<tr>
<td>field1:</td>
<td><%=rs("field1")%></td>
<td><input type=text name="field1' value=""></td>
</tr>
</table>

[glenngv]

dim strField1, strField2, strSetClause
strField1 = request.form("field1")
strField2 = request.form("field2")
strSetClause = ""
if strField1 <> "" Then
strSetClause = "field1='" & replace(strField1, "'", "''") & "'"
End If
if strField2 <> "" Then
if (strSetClause = "") then
strSetClause = "field2='" & replace(strField2, "'", "''") & "'"
else
strSetClause = strSetClause & ", " & "field2='" & replace(strField2, "'", "''") & "'"
end if
End If
sql = "UPDATE tablename SET " & strSetClause & " where id=" & Request.form("id")
response.write "sql: " & sql

The replace function is for escaping single quote that may be present in the fields and has nothing to do with the error you're encountering. See this sticky (http://www.codingforums.com/showthread.php?t=9843) for more info on why you need to escape single quotes in SQL statements.