lbt,ÝŠÇ.²È¨žÉ¢rº,¡û\¢

Good day experts and seniors,

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)

My question here is I want to make sure that the INSERT statement is
successfully run to insert the record to table. What could I do to achieve
this? Another way is to select the specific record again from table (use
recordcount to ensure that the record is there) but this would definately
cause unnecessary overhead.

Did recordset return any value that indicates that the INSERT command is
successful? Or any better way provided would be much appreciated.

Thanks

bob barrows [mvp]

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.

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


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"

lbt,ÝŠÇ.²È¨žÉ¢rº,¡û\¢

Good day experts and seniors,

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)

My question here is I want to make sure that the INSERT statement is
successfully run to insert the record to table. What could I do to achieve
this? Another way is to select the specific record again from table (use
recordcount to ensure that the record is there) but this would definately
cause unnecessary overhead.

Did recordset return any value that indicates that the INSERT command is
successful? Or any better way provided would be much appreciated.

Thanks

lbt,ÝŠÇ.²È¨žÉ¢rº,¡û\¢

Thanks a lot Bob

bob barrows [mvp]

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.

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


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"

lbt,ÝŠÇ.²È¨žÉ¢rº,¡û\¢

Thanks a lot Bob