[RESOLVED] Dataset and Database do not match

[gwboolean]

I am having quite a problem running a combobox.

        cboManager.Enabled = False
        cboManager.TabIndex = 1
        cboManager.TabStop = False
        cboManager.DataSource = ManagerBindingSource
        cboManager.ValueMember = "colStaffID"
        cboManager.DisplayMember = "colFull"
        cboOwner.Enabled = False
        cboOwner.TabIndex = 2
        cboOwner.TabStop = False
        cboOwner.DataSource = DeptBindingSource
        cboOwner.ValueMember = "colDept"
        cboOwner.DisplayMember = "colDept"

The above code is for two comboboxes, one of which binds to the reference table as expected, and the other not so much. The cboManager combobox fails to display any data.

I checked the Access database and the table contained two records. Additionally, the table connected the Server explorer contains the same two records. However, If I check the table in the dataset the two rows of data do not exist in the table, but all of the data in all of the other tables do exist. I am not even sure how this could happen and have no idea how one would correct this.

So the question is, how is it that one specific table in a dataset has no records, while the same table in the database has records? All other tables in both the dataset and the database have identical data.

[techgnome]

It's recommended that you set the .DataSource after setting .ValueMember and .DisplayMember ... it has to do with the way with how the combobox gets rendered and the items added to the list.
That said ... it sounds like you have a larger problem, but it's hard to say. It depends on how you're creating the binding source and pulling the data.

-tg

[gwboolean]

It's recommended that you set the .DataSource after setting .ValueMember and .DisplayMember ... it has to do with the way with how the combobox gets rendered and the items added to the list.
That said ... it sounds like you have a larger problem, but it's hard to say. It depends on how you're creating the binding source and pulling the data.

-tg

I believe I do have a larger problem. As noted, when I check that table from the dataset, there are no records in that table. However, the table, when checked at the database, or through the Server explorer, contains the records.

All of the other tables in that database contain records. As far as I can tell it is just that one table.

Should I recreate the dataset? I have done this in the past, but it has worked out badly for me most of the time.

[gwboolean]

Well, I did try your suggestion and nothing changed. So I went barbarian and got rid of the dataset and created a new one. I have gotten away with that a couple of times in the past. This time, however, was a disaster that ended with my designer completely fouled by my rash action and a project that will have to be recreated. I really hate datasets!

You know, the ONLY thing I use a dataset for is for comboboxes. I use comboboxes a lot with lookup tables, each of which is filled with a set of defined inputs for the user to choose from. Is there a way to achieve this without using datasets?

[wes4dbt]

You certainly don't have to use a dataset. A dataset is just a group of datatables. You don't have a add your datatables to a a dataset. But you've left out some important information. Are you using a Type Dataset or UnTyped Dataset. We have no idea how you are creating your dataset, your bindingsource or your datatables. The answer to your problem could be as simple as you forgot to Fill the datatable but we have no way of knowing.

[wes4dbt]

Here is a simple example of not using a dataset

Imports System.Data.OleDb

Public Class Form9

    Private con As New OleDbConnection(My.Settings.waterConnectionString)
    Private usersDataadapter As New OleDbDataAdapter("Select UserId, Name From Users", con)
    Private groupsDataadapter As New OleDbDataAdapter("Select groupid, Crop From Groups", con)
    Private usersData As New DataTable
    Private groupsData As New DataTable

    Private Sub Form9_Load(sender As Object, e As EventArgs) Handles Me.Load
        SetUpComboBoxes()
    End Sub

    Private Sub SetUpComboBoxes()

        usersDataadapter.Fill(usersData)
        groupsDataadapter.Fill(groupsData)

        Me.UsersComboBox.DisplayMember = "Name"
        Me.UsersComboBox.ValueMember = "userid"
        Me.UsersComboBox.DataSource = usersData

        Me.GroupsComboBox.DisplayMember = "crop"
        Me.GroupsComboBox.ValueMember = "groupid"
        Me.GroupsComboBox.DataSource = groupsData

    End Sub
End Class

[gwboolean]

You certainly don't have to use a dataset. A dataset is just a group of datatables. You don't have a add your datatables to a a dataset. But you've left out some important information. Are you using a Type Dataset or UnTyped Dataset. We have no idea how you are creating your dataset, your bindingsource or your datatables. The answer to your problem could be as simple as you forgot to Fill the datatable but we have no way of knowing.

To be frank I have never setup a dataset, except through the lizard... wizard if you will. I have, in the past, been asked whether I want typed or untyped and while I am not sure, I think I chose untyped. It is one of the many things that I do not clearly understand, which is why I use the lizard in the first place.

The binding source I always get from the tools. I assign the dataset that I created using the lizard then assign the table (member) from the tables in the dataset. After that, when I set the controls on the form I use following code.

DataSource = BindingSourceName
ValueMember = "columnname"
DisplayMember = "columnname"

Depending on what I want from the lookup table, the value and display member for the combobox might not be the same column. Actually, there is only one of my comboboxes that I do that with.

I really like the look of what you suggested. As soon as I can study up on that a little bit I am going to give it a try.

Personally, I have come to the conclusion that datasets, at least the ones that I create using the lizard, are not a very reliable process.

[gwboolean]

OK, I believe I have this worked out. So here is what I came up with that kind of followed your suggestion, while fitting into the process that I already have.

So I am using the connection framework I already have, which is from the MasterBaseConn Class The only thing different is I added a dataset. I am giving datasets one more chance, but am hoping that if I don't use the lizard (wizard) things might not get fubar'd.

    Public Class MasterBaseConn
        Public MasterBaseConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MasterBase4.0.accdb;") 'Database Connection
        Public ListCommand As New OleDbCommand
        Public ListAdapter As OleDbDataAdapter
        Public ListTable As DataTable
        Public Params As New List(Of OleDbParameter)
        Public Exception As String
        Public ListDataSet As DataSet


        Public Sub MasterBaseQuery(SetQuery As String)
            RecordCount = 0
            Exception = ""
            Try
                MasterBaseConnection.Open() 'Open connection
                ListCommand = New OleDbCommand(SetQuery, MasterBaseConnection) 'Database Command
                Params.ForEach(Sub(p) ListCommand.Parameters.Add(p)) 'Load params into command
                Params.Clear() 'Clear params list
                ListDataSet = New DataSet
                ListTable = New DataTable
                ListAdapter = New OleDbDataAdapter(ListCommand)
                RecordCount = ListAdapter.Fill(ListTable)
            Catch ex As Exception
                Exception = ex.Message
                MsgBox(ex.Message + vbLf + "Query failed to execute.")
            End Try
            If MasterBaseConnection.State = ConnectionState.Open Then MasterBaseConnection.Close()
        End Sub
        Public Sub AddParam(Name As String, Value As Object)
            Dim NewParam As New OleDbParameter(Name, Value)
            Params.Add(NewParam)
        End Sub
    End Class

So I added the following to my form with the combobox.

    Private Sub GetDeptList()
        cboOwner.Items.Clear()
        MasterBase.MasterBaseQuery("SELECT colDept From lkpDept")
        If RecordCount > 0 Then
            For Each r As DataRow In MasterBase.ListDataSet.Tables(0).Rows
                cboOwner.Items.Add(r("colDept"))
            Next
            cboOwner.ValueMember = "colDept"
            cboOwner.DisplayMember = "colDept"
            cboOwner.SelectedIndex = -1
        ElseIf MasterBase.Exception <> "" Then
            MsgBox(MasterBase.Exception)
        End If
    End Sub

This should work for me... but there is still an error at the line

For Each r As DataRow In MasterBase.ListDataSet.Tables(0).Rows

What I get is an out of range exception at the row. I figure this should be left for a new thread after I have made my best effort to figure it out.