• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

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
0
eric07
Asked:
eric07
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now