I am running a stored procedure from VB with ADO to SQL Server 7, and returning some output parameters. The proc runs fine from the query analyzer and returns values, but no matter what I try, I only get a value of NULL for my output params in VB. Help, please??
Here's some slightly cleaned up version of the code:
VB Code:
Public Function UIRideAEGetLatLon(OAddID As Long, DAddID As Long) As Variant
Dim i As Integer
Dim oCn As Adodb.Connection
Dim oCmd As Adodb.Command
Dim oParams As Adodb.Parameters
Dim dblRetVal1 As Double
Set oCn = DBConnGet
If oCn.State = adStateOpen Then
Set oCmd = New Adodb.Command
With oCmd
Set .ActiveConnection = oCn
.CommandTimeout = 15
.CommandType = adCmdStoredProc
.CommandText = "prRideAEGetLatLon"
'Create parameters
Set oParams = .Parameters
oParams.Append .CreateParameter("@lOAddID", adInteger, adParamInput, 4, OAddID)
oParams.Append .CreateParameter("@lDAddID", adInteger, adParamInput, 4, DAddID)
oParams.Append .CreateParameter("@dblOLat", adDouble, adParamOutput, 8, 0)
'oParams.Append .CreateParameter("@dblOLat", adDouble, adParamOutput) 'this didn't work either
.Execute , , adExecuteNoRecords
'Retrieve output parameter
dblRetVal1 = .Parameters("@dblOLat"
Set .ActiveConnection = Nothing
End With
UIRideAEGetLatLon = RetVal
Else
'no valid connection
Exit Function
End If
End Function
SQL Stored Proc:
Create Procedure prRideAEGetLatLon (
@lOAddID int,
@lDAddID int,
@dblOLat float OUTPUT)
As
set nocount on
Set @dblOLat = (select Lat from Addresses where AddressId = @lOAddID)
--Select @dblOLat = Lat from Addresses where AddressID = @lOAddId
--Neither set or select method works
The proc runs without error, I can get a return value with no problem, I can print out the value of the output parameter to the query analyzed, but it's always NULL in VB!
Thanks in advance for any help.
Here's some slightly cleaned up version of the code:
VB Code:
Public Function UIRideAEGetLatLon(OAddID As Long, DAddID As Long) As Variant
Dim i As Integer
Dim oCn As Adodb.Connection
Dim oCmd As Adodb.Command
Dim oParams As Adodb.Parameters
Dim dblRetVal1 As Double
Set oCn = DBConnGet
If oCn.State = adStateOpen Then
Set oCmd = New Adodb.Command
With oCmd
Set .ActiveConnection = oCn
.CommandTimeout = 15
.CommandType = adCmdStoredProc
.CommandText = "prRideAEGetLatLon"
'Create parameters
Set oParams = .Parameters
oParams.Append .CreateParameter("@lOAddID", adInteger, adParamInput, 4, OAddID)
oParams.Append .CreateParameter("@lDAddID", adInteger, adParamInput, 4, DAddID)
oParams.Append .CreateParameter("@dblOLat", adDouble, adParamOutput, 8, 0)
'oParams.Append .CreateParameter("@dblOLat", adDouble, adParamOutput) 'this didn't work either
.Execute , , adExecuteNoRecords
'Retrieve output parameter
dblRetVal1 = .Parameters("@dblOLat"
Set .ActiveConnection = Nothing
End With
UIRideAEGetLatLon = RetVal
Else
'no valid connection
Exit Function
End If
End Function
SQL Stored Proc:
Create Procedure prRideAEGetLatLon (
@lOAddID int,
@lDAddID int,
@dblOLat float OUTPUT)
As
set nocount on
Set @dblOLat = (select Lat from Addresses where AddressId = @lOAddID)
--Select @dblOLat = Lat from Addresses where AddressID = @lOAddId
--Neither set or select method works
The proc runs without error, I can get a return value with no problem, I can print out the value of the output parameter to the query analyzed, but it's always NULL in VB!
Thanks in advance for any help.