Re: Multi Update with If Else Statement ... error

Tech-Archive recommends: Fix windows errors by optimizing your registry

bcap wrote:
Hi,

I am trying to create an update statement that works well until I try
to make a IF THEN ELSE decision to determine the status update. Below
is my code. I would sincerely appreciate any thoughts and/or
suggestions ...


It would help to know what the failure is. Error message? What is
telling you what you have isn't "working well"?


Snip>
sql_update = "UPDATE DetailRecs"

You cannot troubleshoot a sql statement without knowing what it is.

Response.Write sql_update
Response.End

comment out those two lines when finished debugging.
Run the page and look at the statement. does the error stand out? If
not, copy and paste it into the query execution environment for whatever
database you are using and try to run it. You maight get a more
informative error message.
Still stuck? Show us the statement.


Set rs = conn.Execute(sql_update)

Why in the world are you opening a recordset on a query that does not
return records?!? Simply do this:

conn.Execute sql_update,,129

The 129 tells ADO that the sql statement won't return records which
makes it skip the step of creating an implicit recordset to receive the
resultset.

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers (tokens):
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

SQL Server:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en



--
HTH,
Bob Barrows


.

Relevant Pages

  • Re: Using For...Each loops when referencing reports.
    ... SELECT tblTx.*, tblTestTx.* FROM tblTx INNER JOIN tblTestTx ON ... 199 is just the ID in your last SQL statement. ... This will now write the exact SQL statement used to open the recordset to ...
    (microsoft.public.access.modulesdaovba)
  • Re: Using For...Each loops when referencing reports.
    ... Looking at the SQL statement, I think you're trying to return the records ... from tblTx and tblTestTx where is equal in both tables. ... This will now write the exact SQL statement used to open the recordset to ...
    (microsoft.public.access.modulesdaovba)
  • Re: Using For...Each loops when referencing reports.
    ... SELECT tblTx.*, tblTestTx.* FROM tblTx INNER JOIN tblTestTx ON ... '[Print the SQL statement to the immediate window] ... recordset to the immediate window. ... something to do with how I'm using the recordset. ...
    (microsoft.public.access.modulesdaovba)
  • Re: XML Persistence scenario
    ... The main reasons for my request, ... This is why i'm wondering if an XML persisted recordset could be a starting ... >> My goal is to not use SQL statement, just put filters properties and use ...
    (microsoft.public.data.ado)
  • Re: redirect if record already exists in database
    ... >> Do a SELECT from the authorization table WHERE the user column is ... not a recordset object. ... Please consider explicitly listing the columns in your SQL statement ...
    (microsoft.public.inetserver.asp.general)