Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Update Statement using slightly different SQL code...

Posted on 2004-10-21
5
Medium Priority
?
224 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
Comment
Question by:brucegust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:david_barker
ID: 12374232
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
ID: 12374360
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
ID: 12374453
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
ID: 12374726
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:
david_barker earned 1000 total points
ID: 12378353
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Make the most of your online learning experience.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Progress

610 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