Call storeprocedure with parameter

[Winanjaya]

My store procedure was contained:

CREATE PROCEDURE [usp_showdepartment] @mHostCode CHAR AS
SELECT * FROM Department WHERE HostCode = @mHostCode ORDER BY DepartmentName
GO

How to call it from vb.net code? .. I tried below codes but I got nothing

Private Sub LoadDepartment()
Dim mHostCode As String = UCase(HostCode.Text.Trim)
Dim SQLDataAdapter As New SqlClient.SqlDataAdapter("usp_showdepartment", SQLConn1)
Dim DSDepartment As New DataSet("Department")

SQLDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
SQLDataAdapter.SelectCommand.Parameters.Add("@mHostCode", SqlDbType.Char)
SQLDataAdapter.SelectCommand.Parameters("@mHostCode").Value = mHostCode

DSDepartment.Clear()
SQLDataAdapter.Fill(DSDepartment, "Department")

Me.C1TrueDBGrid2.DataSource = DSDepartment.Tables("Department")

end sub

please advise ,.. many thanks in advance

Regards
Winan

[mendhak]

This should work:

Dim mHostCode As String = UCase(HostCode.Text.Trim)
        Dim SQLDataAdapter As New SqlClient.SqlDataAdapter("usp_showdepartment", SQLConn1)
        Dim cn As New SqlConnection(strConnectionString)
        Dim spCommand As New SqlCommand("usp_showdepartment", cn)
 
        spCommand.CommandType = CommandType.StoredProcedure
        spCommand.Parameters.Add("@mHostCode", SqlDbType.Char)
        spCommand.Parameters("@mHostCode").Value = mHostCode
 
 
        Dim da As New SqlDataAdapter(spCommand)
        Dim ds As New DataSet
        da.Fill(ds, "Departments")
        Me.DataGrid1.DataSource = ds.Tables("Departments")