Retriveing a value from a dataset - VB.NET

[cwilliams01]

Hello

I am new to VB.NET, so sorry if this is really obvious.

I have written the following code to fill my dataset from a stored procedure requiring 1 paramter input (from a SQL database). The Stored Procedure is quite lengthy including a number of inner joins.

The Dataset is used to populate a combobox on a form. I get the combobox to be populated by the dataset correctly based on my query selection. When the combobox selected item is changed, I want to return a value from the Dataset based on a row number (corresponding to the SelectedIndex from my ComboBox) given a Column Name within the dataset.

However, when I get to the SelectedIndexCh ange procedure the dataset is nothing, even though I have declared the variable as private within the form class (i.e. not within the set up procedure which creates the Dataset).

Code within initial procedure on loading the form:

            Dim OLEConnect As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(genCONNECTION)
            Dim cmd As OleDbCommand = New OleDbCommand("sp_get_permissioned_single_curves", OLEConnect)
            cmd.CommandType = CommandType.StoredProcedure
            Dim InputValue As OleDbParameter = cmd.Parameters.Add("@curvename", OleDbType.VarChar, 50)
            InputValue.Direction = ParameterDirection.Input
            InputValue.Value = PersistedObjects.LoginUserName
 
            Dim daCurveSetup As OleDbDataAdapter = New OleDbDataAdapter()
            daCurveSetup.SelectCommand = cmd
 
            Dim dsCurveSetup = New DataSet("CurveSetup")
 
            daCurveSetup.Fill(dsCurveSetup, "CurveSetup")
 
            'Add Data to the Curve Combo Box:
            With cmbSingleCurvename
                .DataSource = dsCurveSetup.Tables("CurveSetup")
                .DisplayMember = "Curve_Name"
                .ValueMember = "BaseCurve_Setup_ID"
            End With
 
'SelectedIndexChange procedure code:
 
    Private Sub cmbSingleCurvename_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbSingleCurvename.SelectedIndexChanged
 
            ContractNumber = Me.dsCurveSetup.Tables("CurveSetup").Rows(cmbSingleCurvename.SelectedIndex).Item("Contract_ID")
 
            With cmbContract_Name
                .SelectedValue = ContractNumber
            End With
 
End Sub
 

[Plater]

The selectedindex changed event gets fired when you apply a datasource to those items, so you should make sure the selectedindex != -1 for starters.

As for why the dataset is nothing, it looks like you declare your dataset inside a function so it's no longer in scope for the index changed event?

[cwilliams01]

Hello - thanks for the reply.

The sub which creates the dataset is called from here:

Private Sub fmCurveSetup_Lo ad(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles MyBase.Load
TestAdapter()
End Sub

The Test adapter procedure has the following form, so not a function:

Private Sub TestAdapter()
'Code here....
End Sub

I did a watch on the dataset and as soon as it fills the datasource you are right the selectedindexch anged procedure is called for the combobox, but the dataset is nothing within that procedure, whereas when it returns back to the TestAdapter Sub it is not nothing again.

Bizarrely within the Testadapter I have the following code, where the dataset "dsContract s" does persist on selectedIndexCh ange events for another comboBox which is activated during the same sub (when the datsource is set):

Dim daContracts As OleDbDataAdapte r = New OleDbDataAdapte r("SELECT * FROM tbl_Contracts", genCONNECTION)
dsContracts = New DataSet("Contra cts")
daContracts.Fil l(dsContracts, "Contracts" )

Sorry for long reply but I am really stumped, getting the stored procedure to notice that the input parameter had a value to quite a while...

[Plater]

Well I see this:
Dim dsCurveSetup = New DataSet("CurveS etup")

Which makes me think that, even if you DID declare dsCurveSetup in the spot where it should be, you are creating and populating a new dataset and not the one defined to be used in all functions

[cwilliams01]

Hello - sorry have been an idiot - I have dim'd the dataset twice (once in the sub and once in the class)!!! Thanks for tips anyway!!