Have Geek Brain

How to get a value from a recordset that is not a return value or an output parameter using vb6

I have a stored procedure for an existing third party application (SQL 2005) that I want to interact with.

This is an insert statement followed by a select statement:

  
    Set @CustomerId = Cast (SCOPE_IDENTITY () As [int])

    Select @CustomerId

Using VB6 how do I access the @CustomerID value?

set rs = cmd.Execute

does not return the result as expected ...

[Edit]

rs.Fields.Count is 0.

Any attempt to access the resulting recordset, such as rs (0) .Value, simply raises an "Item not found ..." error.

0


a source to share


1 answer


I would assume that your stored procedure is returning more than one recordset.

If so, you can use a method NextRecordset()

to iterate through them.

MSDN:



  • If a command that returns a string succeeds but returns no records, the returned Recordset object will be open but empty. A test for this case is that the BOF and EOF properties are True.
  • If the command that does not return a string succeeds, the returned Recordset object will be closed, which you can check by checking the property status of the Recordset.
  • If there are no more results, the recordset will be set to Nothing.

This means that I would suggest something like this to solve your problem:

Set rs = cmd.Execute

''# fetch the first value of the last recordset
Do Until rs Is Nothing
  If rs.State = adStateOpen Then
    If Not (rs.BOF And rs.EOF) Then
      ''# You can do a different sanity check here, or none at all
      If rs.Fields(0).Type = adInteger Then
        CustomerId = rs.Fields(0).Value
      End If
    End If
  End If
  Set rs = rs.NextRecordSet
Loop

MsgBox CustomerId
+3


a source






More articles:


All Articles