Solved

Update Statement using slightly different SQL code...

Posted on 2004-10-21
212 Views
Last Modified: 2010-04-17
Hey, Folks!

This is basic, but that's what makes it important.

Up till recently I always specified my ODBC connection, but now I'm using an ASA file which leads to some new territory as far as the SQL that I'm most accustomed to.

I want to write an update statement which would usually be:

conn.execute "update inv set Resource = '" & Replace(Request.Form("Resource"),"'","''") & "',"_
& "Comment = '" & Replace(Request.Form("Comment"),"'","''") & "' " _
& "where ID = " & Request.Form("ID") & ""

Now however, I'm setting thing up like this:

<%
set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open session("dbConn_ConnectionString")
set Request.Form = Server.CreateObject("ADODB.Recordset")
set rsList = Server.CreateObject("ADODB.Recordset")
sql = "update inv set Make = '" & Request.Form("Make") & "' where ID = " & Request.Form("ID") & ""
rsList.open sql,dbconn,3,33

Response.Write "OK"

%>

It doesn't work and I'm certain it's because I'm trying to merge the way I'm accustomed to writing an update statement with the way I need to now.

Can someone give me a hand and show me how I would now write the update statement given the way I've got things set up above?

Thanks!
%>
0
Question by:brucegust
    5 Comments
     
    LVL 6

    Expert Comment

    by:david_barker
    If I understand the problem, try
      DBConn.Execute sql
    instead of
      rsList.open sql,dbconn,3,33
    You want to execute a SQL statement, not return a RecordSet.


    0
     

    Author Comment

    by:brucegust
    You're right, that's my hang-up, however, I'm still not out of the woods. Here's what I put down after your suggestion:

    <%
    set DBConn = Server.CreateObject("ADODB.Connection")
    DBConn.Open session("dbConn_ConnectionString")
    sql = "update inv set Make = '" & Request.Form("Make") & "' where ID = " & Request.Form("ID") & ""
    DBConn.Execute sql

    Response.Write "OK"

    %>

    If a computer screen could laugh, it would be erupting into hysterics about now...
    0
     
    LVL 6

    Expert Comment

    by:david_barker
    Is your database updated correctly with the new code ?
    Are you expecting something else to happen after the UPDATE ?
    Let me know.
    0
     

    Author Comment

    by:brucegust
    Never mind, David, I think we're good to go after all.

    While I've got you on the line, this is the way I would run any SQL statement correct? Regardless if it's a delete or insert, the format above represents the way I would write that script, correct?
    0
     
    LVL 6

    Accepted Solution

    by:
    Yes, that is the correct what for INSERT/UPDATE/DELETE statements, basically any that do something to the data.
    Use a Recordset and the SELECT statement when you want to retrieve data.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    A short article about a problem I had getting the GPS LocationListener working.
    Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now