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:
[code=vbnet]
Dim OLEConnect As System.Data.Ole Db.OleDbConnect ion = New System.Data.Ole Db.OleDbConnect ion(genCONNECTI ON)
Dim cmd As OleDbCommand = New OleDbCommand("s p_get_permissio ned_single_curv es", OLEConnect)
cmd.CommandType = CommandType.Sto redProcedure
Dim InputValue As OleDbParameter = cmd.Parameters. Add("@curvename ", OleDbType.VarCh ar, 50)
InputValue.Dire ction = ParameterDirect ion.Input
InputValue.Valu e = PersistedObject s.LoginUserName

Dim daCurveSetup As OleDbDataAdapte r = New OleDbDataAdapte r()
daCurveSetup.Se lectCommand = cmd

Dim dsCurveSetup = New DataSet("CurveS etup")

daCurveSetup.Fi ll(dsCurveSetup , "CurveSetup ")

'Add Data to the Curve Combo Box:
With cmbSingleCurven ame
.DataSource = dsCurveSetup.Ta bles("CurveSetu p")
.DisplayMember = "Curve_Name "
.ValueMember = "BaseCurve_Setu p_ID"
End With

'SelectedIndexC hange procedure code:

Private Sub cmbSingleCurven ame_SelectedInd exChanged(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles cmbSingleCurven ame.SelectedInd exChanged

ContractNumber = Me.dsCurveSetup .Tables("CurveS etup").Rows(cmb SingleCurvename .SelectedIndex) .Item("Contract _ID")

With cmbContract_Nam e
.SelectedValue = ContractNumber
End With

End Sub
[/code]

[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!!