Problem with getting a recordset back from a stored procedure

I have a stored procedure (code below, MSSQL Server 7.0) and when I use it in query analyzer it works great in that it creates the new entry and returns the value but when I call the stored procedure in VB 6.0 I have some problems.  It makes the new entry but as far as it returning a recordset it doesnt.  I know it makes the new entries b/c I look at Query Analyzer just before I get the record "DefectID" and it does show that it was created, So the problem is reading the last statement SELECT.  Any ideas on this?

Thanks


CREATE PROCEDURE [NewDefectPreEntry]
      @nContractID smallint = 0,      
      @sPOWorkOrderNumber Varchar(50),
      @nMaxValue smallint = 0,
      @Date smalldatetime

AS

      SELECT  @nContractID = ContractID
      FROM Contract      
      WHERE POWorkOrderNumber = @sPOWorkOrderNumber
      
      SELECT @nMaxValue = Max(DefectID) FROM DefectEntry

      INSERT INTO DefectEntry (DefectID, DateEntered, ClientID, ContractID)
      VALUES (@nMaxValue + 1, @Date, 1, @nContractID )

      SELECT DefectID, DateEntered, ClientID, ContractID
      FROM DefectEntry
      WHERE DefectID =( @nMaxValue + 1 )


Private Sub Command2_Click()
    Dim oCommand As New ADODB.Command
    Dim oRec As New ADODB.Recordset
   
    With oCommand
        .ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Datalect Client Support;Data Source=EA_SYSTEM"
        .CommandType = adCmdStoredProc
        .CommandText = "NewDefectPreEntry"
        .Parameters.Refresh
        .Parameters("@sPOWorkOrderNumber").Value = "111A9384DED43455"
        .Parameters("@Date").Value = Date
        Set oRec = .Execute
    End With
   
    Dim nValue As Long
   
    nValue = oRec("ClientID")
   
    Set oCommand = Nothing
End Sub
eric07Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

halapayaCommented:
try without the parameter object...
objconn.execute "ProcName " & CID & ",'"
 & WODNO & "'," & MaxVal & Date

I always use objcon.execute method without any probelm , when there are no return values from the stored procedure.. even if u use connecton objects, execute method u can get the recordset on a select statement.
0
eric07Author Commented:
I dont think it has anything to do with the .parameter object b/c I use it with other stored procedures that return data and it works fine..
I think it has something to do with the stored procedure itself.


Thanks
0
danchrCommented:
I have had this problem before.  I have determined its cause to be the SQL Server returning rows effected in between each t-sql statement in the stored proc.  ADO interperates this as a return of multiple recordsets for the operation.  To prevent this put the following at the begining of the stored proc. and recompile it.

SET NOCOUNT ON

This will prevent counts of rows effected from being returned, so your ADO command will only return the results of your select statement.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eric07Author Commented:
Thanks allot you saved the day.

This helped me allot
0
danchrCommented:
No problem.  I've banged my head against that one several times myself.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.