INSERT INTO then return RecordSet from COM 
Author Message
 INSERT INTO then return RecordSet from COM

I need to be able to call a storedproc with parameters, which will insert a

The problem is, I cannot seem to get my COM to return the recordset. It
inserts fine, but doesn't return the recordset. However, if I change the COM
to specify a storedproc that's just a query with parameters and only returns
records (no INSERT INTO in the storedproc, just SELECT..), the COM works
great and I get back a recordset.

Here's what I have:

==StoredProc==


AS


GO

==COM==

Public Function InsertReturnUser(pstrName As String, pstrEmail As String) As
ADODB.Recordset
    Dim cm As ADODB.Command
    OpenConnection  '--function which sets the connection object
    Set cm = New ADODB.Command
    cm.ActiveConnection = mobjConnection
    cm.CommandType = adCmdStoredProc
    cm.CommandText = "sp_insertreturn_user"
    cm.Parameters.Append cm.CreateParameter("name", adVarChar, adParamInput,
50, pstrName)
    cm.Parameters.Append cm.CreateParameter("email", adVarChar,
adParamInput, 50, pstrEmail)
    Set InsertReturnUser = cm.Execute  '--this should assign the resulting
recordset
    Set cm = Nothing
End Function

OK, as a test, if I just call the storedproc from the SQL QueryAnalyzer like
this:
    sp_insertreturn_user 'bobo', 'foo'
I get a record returned:
ID  Name  Email
--  ----  -----
23  bobo  foo

...where ID keeps incrementing every time I call it. ID is an Identity
column.

Now, if I instance my COM component and make the method call like this:
    Dim rs As ADODB.Recordset
    Set rs = obj.InsertReturnUser("bobo", "foo")
I should get back a recordset object with the new record (id, name, email).

But I don't. I get back an error. If I check the table, yes, the new record
has been inserted.

Any ideas why this works from the QueryAnalyzer, but isn't working from COM?

TIA,

Scott A. Keen
MCP x2



Mon, 13 Oct 2003 23:10:15 GMT  
 INSERT INTO then return RecordSet from COM
Quote:
>>But I don't. I get back an error.

Would it be too much ask to know what the error message is?



Quote:
> I need to be able to call a storedproc with parameters, which will insert
a
> record into a table, then return the record as a recordset (based on the

> The problem is, I cannot seem to get my COM to return the recordset. It
> inserts fine, but doesn't return the recordset. However, if I change the
COM
> to specify a storedproc that's just a query with parameters and only
returns
> records (no INSERT INTO in the storedproc, just SELECT..), the COM works
> great and I get back a recordset.

> Here's what I have:

> ==StoredProc==

> CREATE PROCEDURE [sp_insertreturn_user]

> AS


> GO

> ==COM==

> Public Function InsertReturnUser(pstrName As String, pstrEmail As String)
As
> ADODB.Recordset
>     Dim cm As ADODB.Command
>     OpenConnection  '--function which sets the connection object
>     Set cm = New ADODB.Command
>     cm.ActiveConnection = mobjConnection
>     cm.CommandType = adCmdStoredProc
>     cm.CommandText = "sp_insertreturn_user"
>     cm.Parameters.Append cm.CreateParameter("name", adVarChar,
adParamInput,
> 50, pstrName)
>     cm.Parameters.Append cm.CreateParameter("email", adVarChar,
> adParamInput, 50, pstrEmail)
>     Set InsertReturnUser = cm.Execute  '--this should assign the resulting
> recordset
>     Set cm = Nothing
> End Function

> OK, as a test, if I just call the storedproc from the SQL QueryAnalyzer
like
> this:
>     sp_insertreturn_user 'bobo', 'foo'
> I get a record returned:
> ID  Name  Email
> --  ----  -----
> 23  bobo  foo

> ...where ID keeps incrementing every time I call it. ID is an Identity
> column.

> Now, if I instance my COM component and make the method call like this:
>     Dim rs As ADODB.Recordset
>     Set rs = obj.InsertReturnUser("bobo", "foo")
> I should get back a recordset object with the new record (id, name,
email).

> But I don't. I get back an error. If I check the table, yes, the new
record
> has been inserted.

> Any ideas why this works from the QueryAnalyzer, but isn't working from
COM?

> TIA,

> Scott A. Keen
> MCP x2



Mon, 13 Oct 2003 23:26:23 GMT  
 INSERT INTO then return RecordSet from COM
Scott,
I bilieve that the sp retrns two recordsets. examine
InsertReturnUser.Nextrecordset. It should have the inserted record.
option 2. put SET NOCOUNT ON on sp.

CREATE PROCEDURE [sp_insertreturn_user]

 AS
SET NOCOUNT ON


 GO

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hirantha S Hettiarachchi
Please reply to the newsgroup only, not by email.
Code That can be copy and pasted into Query Analyzer is appreciated.



Quote:
> I need to be able to call a storedproc with parameters, which will insert
a
> record into a table, then return the record as a recordset (based on the

> The problem is, I cannot seem to get my COM to return the recordset. It
> inserts fine, but doesn't return the recordset. However, if I change the
COM
> to specify a storedproc that's just a query with parameters and only
returns
> records (no INSERT INTO in the storedproc, just SELECT..), the COM works
> great and I get back a recordset.

> Here's what I have:

> ==StoredProc==

> CREATE PROCEDURE [sp_insertreturn_user]

> AS


> GO

> ==COM==

> Public Function InsertReturnUser(pstrName As String, pstrEmail As String)
As
> ADODB.Recordset
>     Dim cm As ADODB.Command
>     OpenConnection  '--function which sets the connection object
>     Set cm = New ADODB.Command
>     cm.ActiveConnection = mobjConnection
>     cm.CommandType = adCmdStoredProc
>     cm.CommandText = "sp_insertreturn_user"
>     cm.Parameters.Append cm.CreateParameter("name", adVarChar,
adParamInput,
> 50, pstrName)
>     cm.Parameters.Append cm.CreateParameter("email", adVarChar,
> adParamInput, 50, pstrEmail)
>     Set InsertReturnUser = cm.Execute  '--this should assign the resulting
> recordset
>     Set cm = Nothing
> End Function

> OK, as a test, if I just call the storedproc from the SQL QueryAnalyzer
like
> this:
>     sp_insertreturn_user 'bobo', 'foo'
> I get a record returned:
> ID  Name  Email
> --  ----  -----
> 23  bobo  foo

> ...where ID keeps incrementing every time I call it. ID is an Identity
> column.

> Now, if I instance my COM component and make the method call like this:
>     Dim rs As ADODB.Recordset
>     Set rs = obj.InsertReturnUser("bobo", "foo")
> I should get back a recordset object with the new record (id, name,
email).

> But I don't. I get back an error. If I check the table, yes, the new
record
> has been inserted.

> Any ideas why this works from the QueryAnalyzer, but isn't working from
COM?

> TIA,

> Scott A. Keen
> MCP x2



Mon, 13 Oct 2003 23:40:14 GMT  
 INSERT INTO then return RecordSet from COM
Thanks for writing. The error occurs where I try to reference the rs object
like this:
    Do While Not rs.EOF

and I get an error message
Run-time error '3704':
Operation is not allowed when the object is closed.

The next poster gave me a good solution.



Mon, 13 Oct 2003 23:36:13 GMT  
 INSERT INTO then return RecordSet from COM
Well, I've sort of answered my own question... but I don't like the answer,
and hope someone has a better solution.

It seems I have to add ".NextRecordSet" to pick up the recordset in the
second SQL statement in my storedproc. Like this:

     Dim rs As ADODB.Recordset
     Set rs = obj.InsertReturnUser("bobo", "foo").NextRecordSet

This works, but man, that's not really usable. What if I have many more SQL
statements in the stored proc?

Hope there's a better solution. I'd be willing to rewrite the COM or the
storedproc!

I just want to call my COM and have it return a RecordSet from a storedproc,
regardless if the storedproc is doing an INSERT...SELECT, or just a SELECT,
or a DELETE...INSERT...SELECT, or has a 15 INSERTs 20 SELECTs and one final
SELECT to return the recordset. I suppose I can always count on the last
SELECT statement in the stored proc is the recordset I want returned from
COM.

Any suggestions what to try next?

Scott
MCP x2



Mon, 13 Oct 2003 23:32:07 GMT  
 INSERT INTO then return RecordSet from COM
Hirantha,

Thanks for writing.

I just figured out Option 1 myself, but your Option 2 suggestion is exactly
what I was looking for.

Thanks very much.

Scott



Mon, 13 Oct 2003 23:37:19 GMT  
 INSERT INTO then return RecordSet from COM
put
SET NOCOUNT ON  on sp. then  the result would be in first recordset

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hirantha S Hettiarachchi
Please reply to the newsgroup only, not by email.
Code That can be copy and pasted into Query Analyzer is appreciated.



Quote:
> Well, I've sort of answered my own question... but I don't like the
answer,
> and hope someone has a better solution.

> It seems I have to add ".NextRecordSet" to pick up the recordset in the
> second SQL statement in my storedproc. Like this:

>      Dim rs As ADODB.Recordset
>      Set rs = obj.InsertReturnUser("bobo", "foo").NextRecordSet

> This works, but man, that's not really usable. What if I have many more
SQL
> statements in the stored proc?

> Hope there's a better solution. I'd be willing to rewrite the COM or the
> storedproc!

> I just want to call my COM and have it return a RecordSet from a
storedproc,
> regardless if the storedproc is doing an INSERT...SELECT, or just a
SELECT,
> or a DELETE...INSERT...SELECT, or has a 15 INSERTs 20 SELECTs and one
final
> SELECT to return the recordset. I suppose I can always count on the last
> SELECT statement in the stored proc is the recordset I want returned from
> COM.

> Any suggestions what to try next?

> Scott
> MCP x2



Mon, 13 Oct 2003 23:46:30 GMT  
 INSERT INTO then return RecordSet from COM
http://www.aspfaq.com/faq/sp.asp



Quote:
> Well, I've sort of answered my own question... but I don't like the
answer,
> and hope someone has a better solution.

> It seems I have to add ".NextRecordSet" to pick up the recordset in the
> second SQL statement in my storedproc. Like this:

>      Dim rs As ADODB.Recordset
>      Set rs = obj.InsertReturnUser("bobo", "foo").NextRecordSet

> This works, but man, that's not really usable. What if I have many more
SQL
> statements in the stored proc?

> Hope there's a better solution. I'd be willing to rewrite the COM or the
> storedproc!

> I just want to call my COM and have it return a RecordSet from a
storedproc,
> regardless if the storedproc is doing an INSERT...SELECT, or just a
SELECT,
> or a DELETE...INSERT...SELECT, or has a 15 INSERTs 20 SELECTs and one
final
> SELECT to return the recordset. I suppose I can always count on the last
> SELECT statement in the stored proc is the recordset I want returned from
> COM.

> Any suggestions what to try next?

> Scott
> MCP x2



Tue, 14 Oct 2003 00:16:12 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. ADODB Recordset does not return primary key after insert

2. Empty recordset returned when selecting on a recently inserted record

3. Returned recordset is closed when executing stored procedure with Insert Into statement

4. No Recordset returned with INSERT and SELECT

5. HowTo return a recordset to ASP from the COM DLL

6. Return RecordSet from COM

7. How to return a recordset from a COM object managed by MTS

8. HowTo return a recordset to ASP from the COM DLL

9. HowTo return a recordset to ASP from the COM DLL

10. ADO RecordCount is not working when Recordset is returned by COM Component

11. HowTo return a recordset to ASP from the COM DLL

12. Returning Recordset and Return Value from Command Object

 

 
Powered by phpBB® Forum Software