Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
SELECT after INSERT does'nt return resultset in Stored P
Message
General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Miscellaneous
Thread ID:
00522072
Message ID:
00522105
Views:
14
>I have a stored procedure that inserts into a table (via the INSERT SQL statement).
>
>If I issue any SELECT statement issued before that,
>it gets returned to my ADO Recordset.
>
>But if the SELECT is issued after the INSERT (the SELECT is issued on a table that may or may not have anything to do with the table in the INSERT statement),
>it does not work ! The ado recordset does not get any results.
>
>I know the stored procedure itself worked and did not error out, because table A contains the results of the insert.
>
>Create procedure sp_test
>( @TEST Varchar(100), @TEST2 varchar(100) )
>as
> Select * from Y /* this will work, but I don't need this */
>
> insert into A select * from B
>
> Select * from Y /* this will not work, but I want this to work */
> Select * from A /* this will not work, but I want this to work */
>
>GO
>
>'******* VB Code
>Dim cn As New ADODB.Connection
>Dim cmd As New ADODB.Command
>Dim rs As New ADODB.Recordset
>
>cn.Provider = "sqloledb"
>cn.Properties("Data Source").Value = "MYSQLSERVERNAME"
>cn.Properties("Initial Catalog").Value = "MYDATABASENAME"
>cn.Properties("Integrated Security").Value = "SSPI"
>cn.Open
>
>cmd.ActiveConnection = cn
>cmd.CommandText = "sp_test"
>cmd.CommandType = adCmdStoredProc
>cmd.Parameters.Append cmd.CreateParameter("TEST", adVarChar, adParamInput, 100, "nurskill")
>cmd.Parameters.Append cmd.CreateParameter("TEST2", adVarChar, adParamInput, 100, "FK_HOSP")
>Set rs = cmd.Execute
>
>Debug.Print rs(0) ' nothing in the resultset !!
>
>I even tried this: the ADO calls stored proc A, which in turn calls a stored proc B, which does the INSERT. Then stored proc A returns the resultset. So that the StoredProc that does th INSERT is not the one that returns the resultset. It still does not reach the ADO recordset.
>
>I even tried creating an ADO Connection object WITHEVENTS so that I can wait till the Command Complete Event before checking hte recordset... still no luck.
>
>
>thanks
---------------------------------------------
Change your SP to include:

SET NOCOUNT ON

before your first select. You ar returning multiple resultsets back to your recordset. The first resultset is the number of records processed.

Hope this works for you.
Aristotle
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform