Calling Stored Procedures from VB.NET

Question

Dear All,

Can anyone please help me on the following:

I have created a SQL Server stored procedure as follows:

create procedure prcGet_sub_menu_list
     @sub_menu     char(5)
as
begin
     select
          'menu_code'     =     menu_code
          'menu_name'     =     menu_name
     from sub_menu_master
     where menu_code      =     @sub_menu
end
return

Now i am calling this procedure from VB.NET, but i get an error like 'Stored procedure prcGet_sub_menu_list expects parameter @sub_menu which was not supplied'. Please help me on the same. The code which i have in VB.NET is as follows:

Imports System.Data
Imports System.Data.SqlClient

Dim sqlConn as New SqlClient.SqlConnection
sqlConn.ConnectionString = "........"
sqlConn.Open()

Dim menuCode as string
menuCode = cboDetails.selectedItem

Dim sqlCmd as New SqlCommand
sqlCmd.Connection = Connection.sqlConn
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.CommandText = "prcGet_sub_menu_list"
sqlCmd.Parameter.Add("menuCode", SqlDbType.Char)

Dim sqlDA as New SqlDataAdapter()
sqlDA.SelectCommand = sqlCmd
Dim sqlDT as New DataTable
sqlDA.Fill(sqlDT)

This is the code that i have written and it gives me the error: 'Stored procedure prcGet_sub_menu_list expects parameter @sub_menu which was not supplied'.

Please give me some help on the same.

Regards, George

Answer 1

As the message implies, you should add a parameter named "sub_menu" in the same way as you're adding the "menuCode" parameter. You should probably also give it a value:

sqlCmd.Parameter.Add("sub_menu", SqlDbType.Char).Value = "Blah"

(Of course, I don't know what the correct type is, so Char is just an example.)

Answer 2

You need to use the correct parameter name

Something like

sqlCmd.Parameter.Add("@sub_menu", SqlDbType.Char)

And assign it a value

Something like

sqlCmd.Parameters("@sub_menu").Value = val

Have a look at SqlCommand.Parameters Property

and maybe Lesson 07: Using Stored Procedures

Answer 3

Hi,

You have to give the same parameter name and type like in your stored procedure.

  sqlCmd.Parameters.Add(New SqlParameter("@sub_menu", SqlDbType.Char, 5)).Value = "Joe"

Geetha.