SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 45
  1. #1
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    to edit the item by taking id

    I have sql items table. which has id,name,company.

    But in asp form if i enter id ,i can update the name and company fields based on id.


    For this i have used update statement in my asp form.

    I didn't know how to write the code for that clearly.

    I have checked ADO Update in w3schools.org. But that concept is tottaly different than mine.

    can any one know the code for that.


    [asp/vbscript]

  2. #2
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    create your connection then do something like this:
    Code ASP:
    <%
    SQL = "UPDATE users SET email = '" & Request("email")  & "' WHERE id= " & Request("id") 
    conn.Execute( SQL )
    %>

  3. #3
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can anyone guide me in this area. Getting error message as syntax error at "="(mentioned red color)



    <%
    Dim SQL
    OpenSQLConnection

    If Request("Submit") = "Update" Then

    itemid = Request.Form("itemid")
    item=Replace(Request("item"),"'","''")
    unit=Replace(Request("company"),"'","''")
    SQL = "UPDATE items SET Itemid= '"&itemid&"', Item= '"&item&"', Company= '"&company&"' WHERE (Itemid = " & itemid & ")"

    Conn.Execute(SQL)

    End If

    SQL = "Select * from items where Itemid ="& Request("itemid")

    Set RS=Conn.Execute(SQL)

    RS.Close
    CloseSQLConnection
    %>
    I didn't understand what's wrong in thos code. If anyone can know plz let me know.


    Thanks in advance.

  4. #4
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the error is probably at the second equal sign - make sure Request("itemid") has a value and is not blank

  5. #5
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it has value.it is not blank..

  6. #6
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then there is no syntax error in this statement.

    Code ASP:
    SQL = "Select * from items where Itemid = " & Request("itemid")

    Are you sure this is the right line of code for the error message ?

  7. #7
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lucky20 View Post
    it has value.it is not blank..
    well maybe...try this:

    make sure a space exists after the second equal sign to ensure the value is separated from the equal sign.

  8. #8
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is the error message i have got.

    There is a space after equal sign.


    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.

  9. #9
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this to ensure Request("itemid") is numeric

    Code ASP:
    SQL = "Select * from items where Itemid = " & Request("itemid") + 0

  10. #10
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    In fact, apply the Request to a variable and test it for a valid value BEFORE trying to do the SQL.
    Code ASP:
    itemid = Request("itemid")
    If IsNumeric(itemid) Then
      SQL = "Select * from items where Itemid = " & itemid
      'Do the rest of your database code
    Else
      Response.Write "ItemID does not have a numeric value (" & itemid & ")"
    End if
    I'd even be more inclined to specify WHICH request option I want to get the data from, rather than just using Request. (There could be more than one of the options that has an itemid element, so you might be getting the wrong one) ...

    Request.Form("itemid") - User form has method="post"
    Request.Querystring("itemid") - User form has method="get" (or not defined), or the link to the page has ?itemid=value
    Request.Cookies("itemid") - Data comes from a cookie
    Ian Anderson
    www.siteguru.co.uk

  11. #11
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is my complete code.

    which is eitem.asp

    @ webber I have tried that way by adding zero .but there is no use
    @Ian
    I have used your code in my program .. but it is showing error now at
    <%=RS("itemid")%>


    Can any one check the code. Is there any thing wrong in my code..



    <%
    Dim SQL,RS

    OpenSQLConn

    If Request("Submit") = "Update" Then

    itemid = Request.Form("itemid")
    item=Replace(Request("item"),"'","''")
    company=Replace(Request("company"),"'","''")
    SQL = "UPDATE items SET Itemid= '"&itemid&"', Item= '"&item&"', Company= '"&company&"' WHERE (Itemid = " & itemid & ")"

    Conn.Execute(SQL)

    End If

    SQL = "Select * from items where Itemid ="& Request("itemid")

    Set RS=Conn.Execute(SQL)

    RS.Close
    CloseSQLConnection
    %>
    <html<
    <body>
    <div align="center">
    <form method="post" action="eitem.asp">
    <table>
    <tr>
    <td>Item Id</td>
    <td><%=RS("itemid")%></td> </tr> <tr>
    <td>Item </td>
    <td><%=RS("item")%></td></tr>
    <tr>
    <td>Company</td>
    <td><%=RS("company")%></td></tr>

    </table>

    <input type="submit" value="Update" name="update">
    <input type="submit" value="Back" Name="back">


    </form>
    </body>
    </html>



    Thanks in advance

  12. #12
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Actually you're not using my code - you're not even testing for itemid having a value nevermind if it is numeric.

    As to the error though - you're closing the recordset before trying to use it. Create variables and use them in the HTML.

    Code:
    rsitemid = RS("itemid")
    rsitem = RS("item")
    rscompany = RS("company")
    RS.Close
    CloseSQLConnection
    
    <table>
    <tr>
    <td>Item Id</td>
    <td><%=rsitemid%></td> </tr> <tr>
    <td>Item </td> 
    <td><%=rsitem%></td></tr>
    <tr>
    <td>Company</td>
    <td><%=rscompany%></td></tr>
    
    </table>
    Ian Anderson
    www.siteguru.co.uk

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by lucky20 View Post
    SQL = "UPDATE items SET Itemid= '"&itemid&"', Item= '"&item&"', Company= '"&company&"' WHERE (Itemid = " & itemid & ")"
    if you use the WHERE clause condition on itemid to select a single row to update, then you should ~not~ set the itemid value back to what it already was
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @ siteguru
    this is the error message I have got .

    after using your code..

    Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.


    @ r9
    I am really poor in programming..I didn't understand what you are saying. I am new to this ASP.

  15. #15
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can any one give complete code for this..
    I am confused after trying various examples..

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by lucky20 View Post
    @ r9
    I am really poor in programming..I didn't understand what you are saying.
    example: go down the street, find the house with number 42, change its windows and doors, and change its number to 42

    you don't have to change the number to what it already was



    fix your query by changing this --
    Code:
    UPDATE items 
       SET Itemid = '"&itemid&"'
         , Item = '"&item&"'
         , Company = '"&company&"' 
     WHERE ( Itemid = " & itemid  & ")"
    to this --
    Code:
    UPDATE items 
       SET Item = '"&item&"'
         , Company = '"&company&"' 
     WHERE Itemid = " & itemid
    notice no parentheses around the WHERE condition, and no quotes around the number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @r937

    This is the error i have got now

    Microsoft VBScript compilation (0x800A03EA)
    Syntax error
    SQL = "UPDATE items SET Item='"&item&"', company= '"&company&"' WHERE Itemid = " & itemid &

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    remove the trailing ampersand
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Removed ampersand..

    No use.

    error at <%=RS("Itemid")%>ADODB.Recordset (0x800A0CC1)
    Item cannot be found in the collection corresponding to the requested name or ordinal.

  20. #20
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If you're still talking about the UPDATE query, then that query type does not normally return data to a recordset, so RS("itemdid") etc. will not exist.

    Off Topic:

    Anyone get the impression we're going round in circles?
    Ian Anderson
    www.siteguru.co.uk

  21. #21
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru View Post
    Off Topic:

    Anyone get the impression we're going round in circles?
    nobody knows which combination of changes are being implemented.....so errors are compounding

  22. #22
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it..

    Now I don't have any errors.. Now my program looks like this..

    It is successfully executed but not updating the record..

    Can any one know why it is not updating the record..

    <%
    Dim SQL

    OpenSQLConn
    If Request("Submit") = "Update" Then

    itemid = Request.Form("itemid")
    item=Replace(Request("item"),"'","''")
    company=Replace(Request("company"),"'","''")
    SQL = "UPDATE items SET Itemid= '"&itemid&"', Item= '"&item&"', Company= '"&company&"' WHERE (Itemid = " & itemid & ")"

    Conn.Execute(SQL)

    SQL = "Select * from items where Itemid ="& Request("itemid")

    Set RS=Conn.Execute(SQL)

    Conn.Close
    Response.Redirect("main.asp")


    End If
    %>
    <html>
    <body>
    <form method="post" action="eitem.asp">
    <table>
    <tr>
    <td>Item Id</td>
    <td><input type="text" name="itemid"></td> </tr>
    <tr>
    <td>Item </td>
    <td><input type="text" name="item"></td></tr>
    <tr>
    <td>Company</td>
    <td><input type="text" name="company"></td></tr>
    </table>
    <input type="submit" value="Update" name="update">
    <input type="submit" value="Back" Name="back">

    </form>
    </body>
    </html>

  23. #23
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for helping me Webber,r937,siteguru

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if post #22 is your latest version, then you have completely ignored my advice, so there's no need to thank me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this:

    Code ASP:
    <%
    Dim SQL
     
    OpenSQLConn
    If Request.Form("update") = "Update" Then
     
    itemid = Request.Form("itemid")
    item=Replace(Request.Form("item"),"'","''")
    company=Replace(Request.Form("company"),"'","''")
    SQL = "UPDATE items SET Item= '"&item&"', Company= '"&company&"' WHERE (Itemid = " & itemid & ")"
     
    Conn.Execute(SQL)
     
    SQL = "Select * from items where Itemid = "& itemid + 0
     
    Set RS=Conn.Execute(SQL)
     
    Conn.Close
    Response.Redirect("main.asp")
     
    End If
    %>
    <html>
    <body>
    <form method="post" action="eitem.asp">
    <table>
    <tr>
    <td>Item Id</td>
    <td><input type="text" name="itemid"></td> </tr>
    <tr>
    <td>Item </td>
    <td><input type="text" name="item"></td></tr>
    <tr>
    <td>Company</td>
    <td><input type="text" name="company"></td></tr>
    </table>
    <input type="submit" value="Update" name="update">
    <input type="submit" value="Back" Name="back">
     
    </form>
    </body>
    </html>


Bookmarks

Posting Permissions

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