Problem passing a parameter in a MySQL stored procedure in VB.NET

[zombiemasta]

Hi,

I've wrote a stored procedure which expects a parameter to be passed in on the WHERE clause for channelID. The stored procedure runs fine in Navicat for MySQL when you specify the parameter value in the popup prompt when you run it but when trying to use parameter.add in my VB.NET file it brings back the error:

'ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Incorrect number of arguments for PROCEDURE mrluckybags.sp_ select_test; expected 1, got 0'

which breaks at the line "adapter.Fill(d tReturn)"

The stored procedure and VB code are below:

DELIMITER $$

DROP PROCEDURE IF EXISTS `mydatabase`.`s p_select_test`$ $
CREATE DEFINER=`root`@ `%` PROCEDURE `mydatabase`.`s p_select_test`( channelID INT)
select store_channels. Name, store_categorie s.Name, store_products. *
FROM store_channels, store_categorie s, store_products
where store_channels. CHID = channelID
and store_categorie s.CAID = 1
and store_products. Status = 'VISIBLE'
END $$

DELIMITER ;



Dim MyConnection As New OdbcConnection( System.Configur ation.Configura tionManager.App Settings("connS tr"))
Dim adapter As New OdbcDataAdapter
Dim dtReturn As New DataSet
MyConnection.Op en()

Using oCmd As New OdbcCommand("{c all sp_select_test} ", MyConnection)
oCmd.CommandTyp e = CommandType.Sto redProcedure
oCmd.Parameters .Add("channelID ", OdbcType.Int).V alue = 1
adapter.SelectC ommand = oCmd
adapter.Fill(dt Return)
End Using
rptProducts.Dat aSource = dtReturn
rptProducts.Dat aBind()
MyConnection.Cl ose()



Thanks in advance

[balabaster]

Try this:

Dim oCmd As New OdbcCommand("{c all sp_select_test} ", MyConnection)
oCmd.CommandTyp e = CommandType.Sto redProcedure
oCmd.Parameters .Add(New OdbcParameter(" channelID", 1))
adapter.SelectC ommand = oCmd
adapter.Fill(dt Return)

[zombiemasta]

I tried that balabaster but it returns the same error. Any other suggestions?

Many thanks

Try this:

Dim oCmd As New OdbcCommand("{c all sp_select_test} ", MyConnection)
oCmd.CommandTyp e = CommandType.Sto redProcedure
oCmd.Parameters .Add(New OdbcParameter(" channelID", 1))
adapter.SelectC ommand = oCmd
adapter.Fill(dt Return)