DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Getting SQL Server stored procedure parameters from VB.NET form

  1. #1
    Join Date
    Oct 2005
    Posts
    95

    Getting SQL Server stored procedure parameters from VB.NET form

    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

  2. #2
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538
    You want the item the user selected from the Combobox, right?

    ComboBox1.Text won't give you that.

    Use ComboBox1.SelectedItem instead.


    -Andrew

  3. #3
    Join Date
    Oct 2005
    Posts
    95

    Getting SQL Server stored procedure parameters from VB.NET form

    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

  4. #4
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538
    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:
    Code:
    SqlDataAdapter2.SelectCommand.Parameters.Add("@lookuptypedesc", SqlDbType.Char, 32).Value = ComboBox1.Text
    It should be this:
    Code:
    SqlDataAdapter2.SelectCommand.Parameters.Add("@lookuptypedesc", SqlDbType.Char, 32).Value = ComboBox1.SelectedItem
    Try that and tell me if you are still getting an error.

    -Andrew

  5. #5
    Join Date
    Oct 2005
    Posts
    95

    Getting SQL Server stored procedure parameters from VB.NET form

    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

Similar Threads

  1. find version & service packs
    By rperez in forum Database
    Replies: 5
    Last Post: 01-02-2009, 05:14 PM
  2. Replies: 0
    Last Post: 10-25-2001, 02:40 PM
  3. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 12:22 PM
  4. Replies: 3
    Last Post: 08-28-2000, 02:47 PM
  5. SQL server administrative knowledge
    By Becky in forum Database
    Replies: 1
    Last Post: 07-11-2000, 03:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center