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_holdings". 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_active_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.Open()
End If

dcmdData.Connection = gDB
daData.SelectCommand = dcmdData
Dim dtData As New dsPortfolio.PortfolioDataDataTable
daData.Fill(dtData)
m_drowData = dtData.Rows(0)
Dim daActiveHolding As New SqlDataAdapter
Dim dcmdActiveHolding As New SqlCommand
Dim pKeyParam As New SqlParameter
Dim pReturnParam As New SqlParameter
pReturnParam.ParameterName = "@RETURN_VALUE"
pReturnParam.Direction = ParameterDirection.ReturnValue
dcmdActiveHolding.Parameters.Add(pReturnParam)
pKeyParam.ParameterName = "@key"
pKeyParam.SqlDbType = SqlDbType.Int
pKeyParam.Direction = ParameterDirection.Input
dcmdActiveHolding.Connection = gDB
dcmdActiveHolding.CommandType = CommandType.StoredProcedure
dcmdActiveHolding.CommandText = "sp_get_active_holdings"
dcmdActiveHolding.Connection = m_connection
dcmdActiveHolding.Parameters.Add(pKeyParam)
daActiveHolding.SelectCommand = dcmdActiveHolding
m_activeHoldings = New dsPortfolio.ActiveHoldingsDataTable
daActiveHolding.SelectCommand = dcmdActiveHolding
daActiveHolding.Fill(m_activeHoldings)

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****************@TK2MSFTNGP14.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_holdings". 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_active_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.Open()
End If

dcmdData.Connection = gDB
daData.SelectCommand = dcmdData
Dim dtData As New dsPortfolio.PortfolioDataDataTable
daData.Fill(dtData)
m_drowData = dtData.Rows(0)
Dim daActiveHolding As New SqlDataAdapter
Dim dcmdActiveHolding As New SqlCommand
Dim pKeyParam As New SqlParameter
Dim pReturnParam As New SqlParameter
pReturnParam.ParameterName = "@RETURN_VALUE"
pReturnParam.Direction = ParameterDirection.ReturnValue
dcmdActiveHolding.Parameters.Add(pReturnParam)
pKeyParam.ParameterName = "@key"
pKeyParam.SqlDbType = SqlDbType.Int
pKeyParam.Direction = ParameterDirection.Input
dcmdActiveHolding.Connection = gDB
dcmdActiveHolding.CommandType = CommandType.StoredProcedure
dcmdActiveHolding.CommandText = "sp_get_active_holdings"
dcmdActiveHolding.Connection = m_connection
dcmdActiveHolding.Parameters.Add(pKeyParam)
daActiveHolding.SelectCommand = dcmdActiveHolding
m_activeHoldings = New dsPortfolio.ActiveHoldingsDataTable
daActiveHolding.SelectCommand = dcmdActiveHolding
daActiveHolding.Fill(m_activeHoldings)

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

Thanks very much!

Rob