What does VB/SQL Server want for a parameter name?

[Rob Richardson]

Greetings!

I have a SQL Server stored procedure that takes one parameter, named @key.
I am trying to run that stored procedure from a VB app. When I create a
Parameter object and give it the name "key", I get an error message that
says "key is not a parameter for procedure sp_get_active_h oldings". When I
name the parameter "@key", I get a message that says "Procedure
'sp_get_active_ holdings' expects parameter '@key', which was not supplied."

Huh?

It looks like VB is complaining first that "key" is incorrect, and then SQL
is complaining that "@key" is incorrect! What do I have to do? It's a
dinky little stored procedure that could be done just as well in the VB
code, but I'd really like to have all DB-related stuff in the DB.

Here's the procedure:

ALTER PROCEDURE dbo.sp_get_acti ve_holdings (@key INT)
AS
SELECT Name, Symbol, Quantity, Execute_price, IsLong
FROM Holdings
WHERE Covered = 0 AND Portfolio_fk = @key
ORDER BY Symbol;

Here's the VB code:

Public Sub Open(ByVal number As Integer)
m_connection = gDB
Try
If gDB.State = ConnectionState .Closed Then
m_connection.Op en()
End If

dcmdData.Connec tion = gDB
daData.SelectCo mmand = dcmdData
Dim dtData As New dsPortfolio.Por tfolioDataDataT able
daData.Fill(dtD ata)
m_drowData = dtData.Rows(0)
Dim daActiveHolding As New SqlDataAdapter
Dim dcmdActiveHoldi ng As New SqlCommand
Dim pKeyParam As New SqlParameter
Dim pReturnParam As New SqlParameter
pReturnParam.Pa rameterName = "@RETURN_VA LUE"
pReturnParam.Di rection = ParameterDirect ion.ReturnValue
dcmdActiveHoldi ng.Parameters.A dd(pReturnParam )
pKeyParam.Param eterName = "@key"
pKeyParam.SqlDb Type = SqlDbType.Int
pKeyParam.Direc tion = ParameterDirect ion.Input
dcmdActiveHoldi ng.Connection = gDB
dcmdActiveHoldi ng.CommandType = CommandType.Sto redProcedure
dcmdActiveHoldi ng.CommandText = "sp_get_active_ holdings"
dcmdActiveHoldi ng.Connection = m_connection
dcmdActiveHoldi ng.Parameters.A dd(pKeyParam)
daActiveHolding .SelectCommand = dcmdActiveHoldi ng
m_activeHolding s = New dsPortfolio.Act iveHoldingsData Table
daActiveHolding .SelectCommand = dcmdActiveHoldi ng
daActiveHolding .Fill(m_activeH oldings)

Catch e As SqlException
MessageBox.Show ("Error when opening database: " & vbCrLf & _
e.Message)
End Try
End Sub

Thanks very much!

Rob

[Ken Dopierala Jr.]

Hi Rob,

It doesn't look like you are assigning the pKeyParam a value. Something
like pKeyParam.Value = 5. Good luck! Ken.

--
Ken Dopierala Jr.
For great ASP.Net web hosting try:
http://www.webhost4life.com/default.asp?refid=Spinlight
If you sign up under me and need help, email me.

"Rob Richardson" <th*****@n2net. net> wrote in message
news:%2******** ********@TK2MSF TNGP14.phx.gbl. ..

Greetings!

I have a SQL Server stored procedure that takes one parameter, named @key.
I am trying to run that stored procedure from a VB app. When I create a
Parameter object and give it the name "key", I get an error message that
says "key is not a parameter for procedure sp_get_active_h oldings". When I name the parameter "@key", I get a message that says "Procedure
'sp_get_active_ holdings' expects parameter '@key', which was not supplied."
Huh?

It looks like VB is complaining first that "key" is incorrect, and then SQL is complaining that "@key" is incorrect! What do I have to do? It's a
dinky little stored procedure that could be done just as well in the VB
code, but I'd really like to have all DB-related stuff in the DB.

Here's the procedure:

ALTER PROCEDURE dbo.sp_get_acti ve_holdings (@key INT)
AS
SELECT Name, Symbol, Quantity, Execute_price, IsLong
FROM Holdings
WHERE Covered = 0 AND Portfolio_fk = @key
ORDER BY Symbol;

Here's the VB code:

Public Sub Open(ByVal number As Integer)
m_connection = gDB
Try
If gDB.State = ConnectionState .Closed Then
m_connection.Op en()
End If

dcmdData.Connec tion = gDB
daData.SelectCo mmand = dcmdData
Dim dtData As New dsPortfolio.Por tfolioDataDataT able
daData.Fill(dtD ata)
m_drowData = dtData.Rows(0)
Dim daActiveHolding As New SqlDataAdapter
Dim dcmdActiveHoldi ng As New SqlCommand
Dim pKeyParam As New SqlParameter
Dim pReturnParam As New SqlParameter
pReturnParam.Pa rameterName = "@RETURN_VA LUE"
pReturnParam.Di rection = ParameterDirect ion.ReturnValue
dcmdActiveHoldi ng.Parameters.A dd(pReturnParam )
pKeyParam.Param eterName = "@key"
pKeyParam.SqlDb Type = SqlDbType.Int
pKeyParam.Direc tion = ParameterDirect ion.Input
dcmdActiveHoldi ng.Connection = gDB
dcmdActiveHoldi ng.CommandType = CommandType.Sto redProcedure
dcmdActiveHoldi ng.CommandText = "sp_get_active_ holdings"
dcmdActiveHoldi ng.Connection = m_connection
dcmdActiveHoldi ng.Parameters.A dd(pKeyParam)
daActiveHolding .SelectCommand = dcmdActiveHoldi ng
m_activeHolding s = New dsPortfolio.Act iveHoldingsData Table
daActiveHolding .SelectCommand = dcmdActiveHoldi ng
daActiveHolding .Fill(m_activeH oldings)

Catch e As SqlException
MessageBox.Show ("Error when opening database: " & vbCrLf & _
e.Message)
End Try
End Sub

Thanks very much!

Rob