SQL-Server Stored Procedure Expects Parameter?!!!

[FederalProgrammer]

Here's my super simple Stored procedure in SQL-Server:

CREATE PROCEDURE sp_AccountBalance (@Email varchar(255), @Balance money  OUT) AS

SELECT @Balance = tblBands.accountBalance 
FROM tblBands INNER JOIN tblUsers ON tblBands.ID = tblUsers.bandID
Where tblUsers.Email = @Email

I call this stored procedure from vb.NET code like so:

Dim storedProcCaller As New OdbcCommand("sp_AccountBalance", Me._cn)
storedProcCaller.CommandType = CommandType.StoredProcedure

'----------------------------------------
'Creating/Adding Input parameter:
Dim inParam As New OdbcParameter 
inParam.ParameterName = "@Email"
inParam.Value = "Some Value"
inParam.OdbcType = OdbcType.VarChar
inParam.Direction = ParameterDirection.Input
storedProcCaller.Parameters.Add(inParam)

'----------------------------------------
'Creating/Adding output parameter:
Dim outParam As New OdbcParameter 
outParam.ParameterName = "@Balance"
outParam.Value = Nothing
outParam.OdbcType = OdbcType.Double
outParam.Direction = ParameterDirection.Output
storedProcCaller.Parameters.Add(outParam)

'Calling the stored procedure:
Me.OpenConnection()
storedProcCaller.ExecuteNonQuery()
Me.CloseConnection()

I put a break point by the line:

storedProcCaller.ExecuteNonQuery()

storedProcCaller seems to have all of its parameters set along with valid values. However, the mentioned line above, breaks and following exception occurs:

"sp_AccountBalance" expects a paramter '@Email' which was not provided...

Any ideas??



---------------

http://www.enyotic.com

 

[chiph]

Possibly stupid question -- why are you using the Odbc* family of objects when you're talking to a SQL server, which has it's own family of Sql* objects?

Just for giggles, I would try leaving the @-sign off the parameter name:

inParam.ParameterName = "Email"

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[FederalProgrammer]

Hay chip... thanx for the reply...
I tried removing @ sign... no luck...

The only reason I've been using ODBC, is because of habit... I've been using it for a while and it's been doing what I've wanted it do...

Anyways, just so that I don't have to redo my entire project, what is the correct way to call an sql-Server stored-procedure from .NET environment using ODBC.NET?



---------------

http://www.enyotic.com