Re: Return value from recordset

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 01/05/05 

Date: Wed, 5 Jan 2005 06:32:25 -0500

LBT wrote:
> Good day experts and seniors,

You forgot to tell us what type and version of database you are using. This
is ALWAYS relevant. Please don't leave it out of your future posts.

>
> I have a SQL INSERT statement at my ASP page executed using ADO
> recordset. Eg: Set oRs = rs("INSERT INTO myTable VALUES ('Testing',
> 1)", objConn)

First of all, you should not be using an expensive recordset object to run a
query that returns no records. You should use the connection's Execute
method for that, like this:

dim sSQL
sSQL = "INSERT INTO myTable VALUES ('Testing',1)"
objConn.Execute sSQL,,129
objConn.Close: Set objConn=Nothing

The 129 is a combination of two constants: adCmdText (1) which tells ADO
that you are executing a string containing a SQL statement (always tell ADO
what the command type is; don't make it guess), and adExecuteNoRecords
(128), which tells ADO not to bother constructing a recordset because you
don't expect to get records back from your query. 1+128=129

You can read more about this here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdobjconnection.asp
which links to here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthcnnexecute.asp

>
> My question here is I want to make sure that the INSERT statement is
> successfully run to insert the record to table.

The second argument of the Execute method (the one I left out in my first
example) can be a variable that will be passed ByRef, and will contain the
number of records affected by your query after it executes. So, this will do
what you want:

dim lRecs
lRecs = 0
dim sSQL
sSQL = "INSERT INTO myTable VALUES ('Testing',1)"
objConn.Execute sSQL,lRecs,129
Response.Write lRecs & " record(s) inserted"
objConn.Close: Set objConn=Nothing

Bob Barrows 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • Re: Return value from recordset
    ... You should use the connection's Execute ... > dim sSQL ... > that you are executing a string containing a SQL statement (always tell ADO ... which tells ADO not to bother constructing a recordset because you ...
    (microsoft.public.inetsdk.programming.scripting.vbscript)
  • Re: Problem with Deletes in code
    ... DoCmd.RunSQL to execute the delete query. ... 'All records in rsSourceProjects will be for the same project, ... problem with ADO interacting with the tables, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Vorgang ist =?ISO-8859-1?Q?f=FCr_geschlossenes_Objekt_?= =?ISO-8859-1?Q?nicht_zu
    ... ich versuche mit ADO auf eine MS-SQL 2005 Instanz zu zugreifen. ... Zeile positioniert (und ein adForwardOnly Resultset wie es Execute ... 1.: SELECT object_id ...
    (microsoft.public.de.sqlserver)
  • Re: foreign key ado syntax
    ... in my previous ado approach I was getting a "Values ..." ... Sub ToDatabase ... oBlockData .ToDatabase oDbController ... execute for each record(instead of doing a batch...which i've never done ...
    (microsoft.public.vb.database.ado)
  • Re: How do I make the ON DELETE CASCADE clause in the ALTER TABLE CON.
    ... I believe that cascading updates/deletes can only be included in your DDL ... Since the interfaces uses the native Access libraray, ... You have to execute the query from code on an ADO connection. ...
    (microsoft.public.access.modulesdaovba)