[RESOLVED] Dataset and Database do not match
I am having quite a problem running a combobox.
Code:
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.
Re: Dataset and Database do not match
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
Re: Dataset and Database do not match
Quote:
Originally Posted by
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
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.
Re: Dataset and Database do not match
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?
Re: Dataset and Database do not match
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.
Re: Dataset and Database do not match
Here is a simple example of not using a dataset
Code:
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
Re: Dataset and Database do not match
Quote:
Originally Posted by
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.
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.
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.
Re: Dataset and Database do not match
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.
Code:
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.
Code:
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
Code:
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.