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(dtReturn)"

The stored procedure and VB code are below:

DELIMITER $$

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

DELIMITER ;



Dim MyConnection As New OdbcConnection(System.Configuration.ConfigurationM anager.AppSettings("connStr"))
Dim adapter As New OdbcDataAdapter
Dim dtReturn As New DataSet
MyConnection.Open()

Using oCmd As New OdbcCommand("{call sp_select_test}", MyConnection)
oCmd.CommandType = CommandType.StoredProcedure
oCmd.Parameters.Add("channelID", OdbcType.Int).Value = 1
adapter.SelectCommand = oCmd
adapter.Fill(dtReturn)
End Using
rptProducts.DataSource = dtReturn
rptProducts.DataBind()
MyConnection.Close()



Thanks in advance

[balabaster]

Try this:

Dim oCmd As New OdbcCommand("{call sp_select_test}", MyConnection)
oCmd.CommandType = CommandType.StoredProcedure
oCmd.Parameters.Add(New OdbcParameter("channelID", 1))
adapter.SelectCommand = oCmd
adapter.Fill(dtReturn)

[zombiemasta]

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

Many thanks

Try this:

Dim oCmd As New OdbcCommand("{call sp_select_test}", MyConnection)
oCmd.CommandType = CommandType.StoredProcedure
oCmd.Parameters.Add(New OdbcParameter("channelID", 1))
adapter.SelectCommand = oCmd
adapter.Fill(dtReturn)