Update Statement using slightly different SQL code...

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!
%>
brucegustPHP DeveloperAsked:
Who is Participating?
 
david_barkerConnect With a Mentor Commented:
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
 
david_barkerCommented:
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
 
brucegustPHP DeveloperAuthor Commented:
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
 
david_barkerCommented:
Is your database updated correctly with the new code ?
Are you expecting something else to happen after the UPDATE ?
Let me know.
0
 
brucegustPHP DeveloperAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.