Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: help needed

  1. #1
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    help needed

    can someone please help with update statement?

    Code:
    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.

  • #2
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,037
    Thanks
    0
    Thanked 250 Times in 246 Posts
    Update tablename Set field1='value', field2='value' where id=2

  • #3
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:
    Code:
    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:
    Code:
    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.
    Code:
    <table>
    <tr>
    <td>field1:</td>
    <td><%=rs("field1")%></td>
    <td><input type=text name="field1' value=""></td>
    </tr>
    </table>

  • #4
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,037
    Thanks
    0
    Thanked 250 Times in 246 Posts
    Code:
    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 for more info on why you need to escape single quotes in SQL statements.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •