Returning a dataset from a componant [RESOLVED]

[carlblanchard]

Hi all ive got this class of

#Region "Data set"
        Public Function GetDataSet()
            Try
                Dim myCommand As SqlCommand = New SqlCommand
                Dim myReader As SqlDataReader = myCommand.ExecuteReader
                With myCommand
                    .Connection = MyConnection
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "GetAllLetters"
                End With
 
                Return myReader
 
                Return True
            Catch ex As Exception
                mErrorMsg = ex.ToString
                Return False
            End Try
        End Function
#End Region

Which should hopefully just return me a dataset which i wana bind to a combo box

Dim objData As DesignerDI.GetDI
 Me.cboLetter.DataSource = objData.GetDataSet

How can i bind it to the control ?

[Edneeis]

Are you talking about ASP.NET? If not then you can't bind to a Datareader you'll need to have GetDataSet actually return a dataset. The way you have it now it returns a boolean as an object. It should be more along this line:

#Region "Data set"
        Public Function GetDataSet() As DataSet
            Try
                'where is MyConnection declared?
                Dim myCommand As New SqlCommand(MyConnection,"GetAllLatters")
                myCommand.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter(myCommand)
                Dim ds As New DataSet
                da.Fill(ds)
 
                Return ds
            Catch ex As Exception
                mErrorMsg = ex.ToString
                Return Nothing
            End Try
        End Function
#End Region

I didn't double check my syntax but it should give you the idea.

[carlblanchard]

cheers Edneeis

But how can i bind it to the control ???

[Edneeis]

What you had was fine you just need to set the display member.

Dim objData As DesignerDI.GetDI
Me.cboLetter.DataSource = objData.GetDataSet
Me.cboLetter.DisplayMember="Fieldnametoshow"

[carlblanchard]

thanks mate

[carlblanchard]

edneeis any ideas what this means (System.Data.DataViewManagerListItemTypeDescriptor)
thats whats coming up in the combo box ???

[carlblanchard]

edneeis any ideas what this means (System.Data.DataViewManagerListItemTypeDescriptor)
thats whats coming up in the combo box ???

[Edneeis]

It means it didn't find the field that you set as the DisplayMember so it defaulted to the object's ToString method. Check what you have as the Displaymember, make sure its spelled right and it is also case sensitive (so "name" and "Name" aren't the same). You can also try setting the Displaymember before the Datasource even though it shouldn't matter either way.

[carlblanchard]

Code within my COM

#Region "Data set"
        Public Function GetDataSet() As DataSet
            Try
                Dim myCommand As New SqlCommand("GetAllLetters", MyConnection)
                myCommand.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter(myCommand)
                Dim ds As New DataSet
                da.Fill(ds)
                Return ds
 
            Catch ex As Exception
                mErrorMsg = ex.ToString
                Return Nothing
            End Try
        End Function
#End Region

heres the code in my onLoad function

Private Sub Designer_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim objData As New NewForestMortgagesAPP.DesignerDI.GetDI
        Me.cboLetter.DisplayMember = "Id"
        Me.cboLetter.DataSource = objData.GetDataSet
 
    End Sub

Attached is query showing the names of the columns ive made sure there case and still same message

[Edneeis]

My bad we never assigned a table to the binding. We have to pick a table in the dataset that has the Id field and use that as the datasource or optionally you could add it to the displaymember. So if there is only 1 table in the dataset then try this:

Me.cboLetter.DataSource = objData.GetDataSet.Tables(0)

Otherwise use the tablename of index number of the table in the dataset which has the "Id" field you want to bind to. This happens because a dataset object can hold multiple tables and we never said which one to bind to.

[carlblanchard]

OK cool thats sorted it now (THANK YOU EVER SO MUCH MATE)

Now its binded is there a easy way to show a value but have an onChange and set an int

ie. the combo is loading all the letters from db and of course i wana show them by name but when they select a letter i need to call my getSpeficRecord by the id of the record so is there an easy way to get that id now its binded ?

[Edneeis]

The id should just be the text so you can use the SelectionChangeCommited or SelectedIndexChanged events to detect a change and just use the current text as the id.

[carlblanchard]

Hi edinees

Cant really use the ID as a visual display users wouldnt have a clue

cant i use the ValueMember element of the combo box to store the id of the record SO when they select a value it returns my db id as the valueMember ???

Dim objFields As New NewForestMortgagesAPP.DesignerDI.ComboPopulation
        objFields.ComboId = Me.cboEditItem.SelectedIndex
 
        Me.cboFields.DisplayMember = "ItemText"
        Me.cboFields.ValueMember = "ItemCode"
        Me.cboFields.DataSource = objFields.GetDataSet.Tables(0)
        objFields = Nothing

If so how do i set the value member to get the id from the dataset ItemCode is the returned record from my query