Getting SQL Server stored procedure parameters from VB.NET form

[software_develo]

Hi

I have a quick question about how to use SQL Server Stored procedure parameters in vb.net form

Here is the problem I am having

I have drop down list/combo box and a data grid on a VB.NET form . I have to show the data in the datagrid based on the value selected from the drop down list box . For example if the value selected from the drop down box is 6 and user click on a Button all the information related to value 6 will be shown in the data grid .

To solve this problem I have created a stored procedure with SQL Server 2000 where the I have a parameter ( which is field supplying values to the drop down box ) . But when I run the VB.NET form I am getting an error " @ parameter value not supplied " . I need to have @parameter value ( which is the parameter in SQL Server Stored procedure ) to filter the data in the data grid . other wise whatever value I choose from the drop down list box all every information will show in the data grid and it will not filter data . I like to filter data in to data grid based on the value from the drop down list box

How to do that

here is my code

the stored procedure is like this

CREATE PROCEDURE dbo.GetLookUp_Admin
@LookupTypeDesc char(10)
AS
Select * from TBL_LookUps where LookUpTypeDesc = @LookUpTypeDesc
order by code
RETURN @@rowcount
GO

The VB.NET GET procedure is like this . I have two dataset and SQL DATA Adapter

Public Function GetLookupAdmin(ByVal LookUpTypeDesc As String)
'run the second tored procedure to get the ObligorInd
'clear the dataset table, in case it load second time
If DataSet11.Tables().Contains("LookUpTypeDesc") Then
DataSet11.Tables("LookUpType").Clear()
End If
Dim GetLookupCommand As New SqlCommand("dbo.GetLookup_admin", DataSetClass.DBConenction) 'stored procedure name is GetLookup
GetLookupCommand.CommandType = CommandType.StoredProcedure
Dim LookupPar As SqlParameter = GetLookupCommand.Parameters.Add("@LookUpTypedesc", SqlDbType.Char)
LookupPar.Direction = ParameterDirection.Input
LookupPar.Value = LookUpTypeDesc 'assign the ObligorID to the parameter

Dim SQLdataadapter1 As New SqlDataAdapter
SQLdataadapter1.TableMappings.Add("Table", "LookupTypedesc") 'create a table Lookup in dataset , and load the data from database into this table
SQLdataadapter1.SelectCommand = GetLookupCommand
SQLdataadapter1.Fill(DataSet11)

Dim SQLdataadapter2 As New SqlDataAdapter
SQLdataadapter2.TableMappings.Add("Table", "LookupTypedesc") 'create a table Lookup in dataset , and load the data from database into this table
SQLdataadapter2.SelectCommand = GetLookupCommand
SQLdataadapter1.Fill(DataSet21)

End Function

Code for Button

SqlDataAdapter2.SelectCommand.Parameters.Add("@lookuptypedesc", SqlDbType.Char, 32).Value = ComboBox1.Text
'placing a value into the SQL parameter corresponding to the lookuptypedesc field
DataSet21.Clear()
SqlDataAdapter2.Fill(DataSet21)


Page load code

Private Sub DaAdmin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

DataSet11.Clear()
' SqlDataAdapter2.SelectCommand.Parameters.Add("@lookuptypedesc", SqlDbType.Char, 32).Value = ComboBox1.Text

LoadDataset()

End Sub

Load data set code

GetLookupAdmin(GlobalVar.CreditMemoId)


Please help me to solve my problem . Please also so me some code example if possible

[Andrew Cushen]

You want the item the user selected from the Combobox, right?

ComboBox1.Text won't give you that.

Use ComboBox1.SelectedItem instead.


-Andrew

[software_develo]

Hi

Thanks for the reply . I have wrote two SQL Server Stored procedure one each for drop down list / combo box and Data grid . The first stored procedure in populating the combo box with the values that user will select . Another stored procedure suppose to populate the data grid with the corresponding values based on the value user had choosen from the drop down list box . The first part is working and it populate the drop down box with data but how do I populate the data grid with corresponding information

Thanks

[Andrew Cushen]

Have you tried changing to what I suggested?

The error you are getting indicates that the value in the combo box is not getting to the stored procedure as a parameter. If you are using ComboBox1.Text to try to get the item the user selected, IT WON'T WORK. Change it to ComboBox1.SelectedItem.

ComboBox1.SelectedItem is the value the user selected. Unless you get that part right, nothing will get passed to the stored procedure as a parameter, and you will get the error you are seeing.

The problem is this line:

SqlDataAdapter2.SelectCommand.Parameters.Add("@lookuptypedesc", SqlDbType.Char, 32).Value = ComboBox1.Text

It should be this:

SqlDataAdapter2.SelectCommand.Parameters.Add("@lookuptypedesc", SqlDbType.Char, 32).Value = ComboBox1.SelectedItem

Try that and tell me if you are still getting an error.

-Andrew

[software_develo]

Hi Andrew

Thanks for the reply . I am still having the same problem . Here is my code from the VB.net form


Private Sub DaAdmin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

' LookupPar.Value = LookUpTypedesc 'assign the ObligorID to the parameter
DataSet11.Clear()
SqlDataAdapter1.Fill(DataSet11)


End Sub



Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
' GetLookupAdapter.Update(DataSet21)

End Sub


Public Function GetLookupAdmin(ByVal LookupTypedesc As Char)
'run the second tored procedure to get the ObligorInd
'clear the dataset table, in case it load second time
If DataSet11.Tables().Contains("LookUpTypeDesc") Then
DataSet11.Tables("LookUpTypeDesc").Clear()
End If
Dim GetLookupCommand As New SqlCommand("dbo.GetLookUp_Admin", DataSetClass.DBConenction) 'stored procedure name is GetLookup
GetLookupCommand.CommandType = CommandType.StoredProcedure
Dim LookupPar As SqlParameter = GetLookupCommand.Parameters.Add("@LookUpTypedesc", SqlDbType.Char).Value = ComboBox1.SelectedItem
LookupPar.Direction = ParameterDirection.Input
LookupPar.Value = LookupTypedesc 'assign the ObligorID to the parameter

Dim SQLdataadapter1 As New SqlDataAdapter
SQLdataadapter1.TableMappings.Add("Table", "LookupTypedesc") 'create a table Lookup in dataset , and load the data from database into this table
SQLdataadapter1.SelectCommand = GetLookupCommand
SQLdataadapter1.Fill(DataSet11)

''Dim SQLdataadapter2 As New SqlDataAdapter
'' SQLdataadapter2.TableMappings.Add("Table", "LookupTypedesc") 'create a table Lookup in dataset , and load the data from database into this table
''SQLdataadapter2.SelectCommand = GetLookupCommand
''SQLdataadapter1.Fill(DataSet21)

End Function

Public Function GetLookupAdmin1(ByVal LookupTypedesc As Char)
If DataSet21.Tables().Contains("LookUpTypeDesc") Then
DataSet21.Tables("LookUpTypeDesc").Clear()
End If
Dim GetLookupCommand1 As New SqlCommand("dbo.GetLookup_admin", DataSetClass.DBConenction) 'stored procedure name is GetLookup
GetLookupCommand1.CommandType = CommandType.StoredProcedure
Dim LookupPar As SqlParameter = GetLookupCommand1.Parameters.Add("@LookUpTypedesc", SqlDbType.Char).Value = ComboBox1.SelectedItem
LookupPar.Direction = ParameterDirection.Input
LookupPar.Value = LookupTypedesc 'assign the ObligorID to the parameter

Dim SQLdataadapter1 As New SqlDataAdapter
SQLdataadapter1.TableMappings.Add("Table", "LookupTypedesc") 'create a table Lookup in dataset , and load the data from database into this table
SQLdataadapter1.SelectCommand = GetLookupCommand1
SQLdataadapter1.Fill(DataSet21)

End Function
Public Sub LoadDataset()
''GetLookupAdmin()
GetLookupAdmin(GlobalVar.LookUpTypeDesc)
GetLookupAdmin1(GlobalVar.LookUpTypeDesc)
End Sub
Public Sub dispTest() ' Display


'curRow = dsAdmin.Tables("LookupType")

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
SqlDataAdapter2.SelectCommand.Parameters("LookupTypedesc").Value = ComboBox1.SelectedItem
'placing a value into the SQL parameter corresponding to the state field
DataSet21.Clear()
SqlDataAdapter2.Fill(DataSet21)


End Sub

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
LoadDataset()
End Sub

Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate

End Sub
End Class