Okay I think it will be easier to figure out if I just write you an example
;-). This example uses the SQL Northwind database. It selects Regions and
Territories, sets up a relation and has combobox that binds the selected
value to the Territories.RegionID field. I'm putting a duplicate row into
the Region table to simulate your scenario and it works fine. Note the call
to EndCurrentEdit in the SelectedIndexChanged event handler.
Public Class Form3
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents TextBox2 As System.Windows.Forms.TextBox
Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents Label4 As System.Windows.Forms.Label
Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
Friend WithEvents Label3 As System.Windows.Forms.Label
Friend WithEvents TextBox3 As System.Windows.Forms.TextBox
Friend WithEvents Button1 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.TextBox2 = New System.Windows.Forms.TextBox
Me.TextBox1 = New System.Windows.Forms.TextBox
Me.Label1 = New System.Windows.Forms.Label
Me.Label2 = New System.Windows.Forms.Label
Me.Label4 = New System.Windows.Forms.Label
Me.TextBox3 = New System.Windows.Forms.TextBox
Me.ComboBox1 = New System.Windows.Forms.ComboBox
Me.Label3 = New System.Windows.Forms.Label
Me.Button1 = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'TextBox2
'
Me.TextBox2.Location = New System.Drawing.Point(192, 56)
Me.TextBox2.Name = "TextBox2"
Me.TextBox2.Size = New System.Drawing.Size(160, 20)
Me.TextBox2.TabIndex = 5
Me.TextBox2.Text = "TextBox2"
'
'TextBox1
'
Me.TextBox1.Location = New System.Drawing.Point(192, 24)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(48, 20)
Me.TextBox1.TabIndex = 4
Me.TextBox1.Text = "TextBox1"
'
'Label1
'
Me.Label1.AutoSize = True
Me.Label1.Location = New System.Drawing.Point(24, 24)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(112, 16)
Me.Label1.TabIndex = 6
Me.Label1.Text = "Territories.TerritoryID"
'
'Label2
'
Me.Label2.AutoSize = True
Me.Label2.Location = New System.Drawing.Point(24, 56)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(157, 16)
Me.Label2.TabIndex = 7
Me.Label2.Text = "Territories.TerritoryDescription"
'
'Label4
'
Me.Label4.AutoSize = True
Me.Label4.Location = New System.Drawing.Point(24, 120)
Me.Label4.Name = "Label4"
Me.Label4.Size = New System.Drawing.Size(109, 16)
Me.Label4.TabIndex = 10
Me.Label4.Text = "Territorries.RegionID"
'
'TextBox3
'
Me.TextBox3.Location = New System.Drawing.Point(192, 120)
Me.TextBox3.Name = "TextBox3"
Me.TextBox3.Size = New System.Drawing.Size(48, 20)
Me.TextBox3.TabIndex = 8
Me.TextBox3.Text = "TextBox4"
'
'ComboBox1
'
Me.ComboBox1.DropDownStyle =
System.Windows.Forms.ComboBoxStyle.DropDownList
Me.ComboBox1.Location = New System.Drawing.Point(192, 88)
Me.ComboBox1.Name = "ComboBox1"
Me.ComboBox1.Size = New System.Drawing.Size(160, 21)
Me.ComboBox1.TabIndex = 11
'
'Label3
'
Me.Label3.AutoSize = True
Me.Label3.Location = New System.Drawing.Point(24, 88)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(40, 16)
Me.Label3.TabIndex = 12
Me.Label3.Text = "Region"
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(296, 144)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(96, 23)
Me.Button1.TabIndex = 13
Me.Button1.Text = "Show Changes"
'
'Form3
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(432, 182)
Me.Controls.Add(Me.Button1)
Me.Controls.Add(Me.Label3)
Me.Controls.Add(Me.ComboBox1)
Me.Controls.Add(Me.Label4)
Me.Controls.Add(Me.TextBox3)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.TextBox2)
Me.Controls.Add(Me.TextBox1)
Me.Name = "Form3"
Me.Text = "Form3"
Me.ResumeLayout(False)
End Sub
#End Region
Private MyDataset As DataSet
Private Class DataAccess
Private Const SQL_CONNECTION_STRING As String = _
"Data Source=localhost;" & _
"Initial Catalog=Northwind;" & _
"Integrated Security=SSPI"
Public Shared Sub LoadData(ByRef ds As DataSet)
ds = New DataSet
Dim da, da2 As SqlDataAdapter
Dim cnn As SqlConnection
Try
cnn = New SqlConnection(SQL_CONNECTION_STRING)
da = New SqlDataAdapter("SELECT * FROM Region", cnn)
da.Fill(ds, "Region")
With ds.Tables("Region")
If .Rows.Count > 0 Then
'-- Load a "Duplicate" row. has a unique primary key,
'-- but same description as first row.
Dim values As Object() = {.Rows.Count + 1,
.Rows(0)("RegionDescription")}
.LoadDataRow(values, True)
End If
End With
da = New SqlDataAdapter("SELECT * FROM Territories", cnn)
da.Fill(ds, "Territories")
ds.Relations.Add("Region_Territories", _
ds.Tables("Region").Columns("RegionID"), _
ds.Tables("Territories").Columns("RegionID"))
ds.DataSetName = "RegionTerritories"
Catch Exp As Exception
MessageBox.Show(Exp.Message)
End Try
End Sub
End Class
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
DataAccess.LoadData(MyDataset)
Me.ComboBox1.DataSource = MyDataset.Tables("Region")
Me.ComboBox1.ValueMember = "RegionID"
Me.ComboBox1.DisplayMember = "RegionDescription"
Me.ComboBox1.DataBindings.Add("SelectedValue", MyDataset,
"Territories.RegionID")
Me.TextBox1.DataBindings.Add("Text", MyDataset, "Territories.TerritoryID")
Me.TextBox2.DataBindings.Add("Text", MyDataset,
"Territories.TerritoryDescription")
Me.TextBox3.DataBindings.Add("Text", MyDataset, "Territories.RegionID")
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
'-- This forces the comboxbox's value to be written to the dataset.
Dim cm As CurrencyManager = Me.BindingContext(MyDataset, "Territories")
cm.EndCurrentEdit()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
'-- View the diffgram in the web browser
Try
If Not (MyDataset Is Nothing) Then
Dim cFileName As String =
Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) +
"\Diff"
Dim dsDiffgram As DataSet
If MyDataset.HasChanges Then
dsDiffgram = MyDataset.GetChanges()
dsDiffgram.WriteXml(cFileName + MyDataset.DataSetName + ".xml",
XmlWriteMode.DiffGram)
System.Diagnostics.Process.Start("file://" + cFileName +
MyDataset.DataSetName + ".xml")
Else
MessageBox.Show("Please make changes first.", "Show Changes",
MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End If
Catch exp As Exception
End Try
End Sub
End Class
cryolitte said:
1). Setting it up kinda like this:
DataTable dt = generateTestDataTable(); // in here column ID is set as
Unique
comboBox1.DataSource = dt;
comboBox1.ValueMember = "ID";
comboBox1.DisplayMember = "Name";
2). Then I added the data bindings, as suggested:
comboBox1.DataBindings.Add("SelectedValue", dt, "ID");
3). But then I start getting a ConstraintException whenever I make a
selection in the dropdown. Additional information: Column 'ID' is
constrained to be unique. Value '4' (or whatever was selected) is already
present.
Why is this being thrown? I am not trying to edit or insert into the
DataTable. I only wish to select a row in it (and get the Combo to
remember
it!). When I click on the dropdown button, it is still automatically
re-selected to the first matching (based on DisplayValue) row.
4). Just to see what happens, went back, set dt.Columns["ID"].Unique =
false. Then, whenever I select something, it updates its ID with the
current
SelectedValue...
5). Set Unique = true again. I then tried calling
CurrencyManager.EndCurrentEdit() again in both DropDown and DrawItem
events,
and my selectedItem/Index/Value is still being overwritten.
ConstraintException was also thrown (because of the new data-binding, I
think).
Seriously, I can't be the first one asking this question...lol...
Beth Massi said:
How are you setting up the databindings to your dataset? Are you doing
something like this?:
Me.ComboBox1.DataSource = MyLookupTable
Me.ComboBox1.ValueMember = "ID"
Me.ComboBox1.DisplayMember = "Name"
Me.ComboBox1.DataBindings.Add("SelectedValue", myDataSet, "Table1.MyID")
Also, as you suggested, I tried calling
CurrencyManager.EndCurrentEdit()
in
the DropDown and DrawItem events. The ComboBox still selected the first
matching item based on DisplayValue.
Does the problem still happen if the Names are different? If so, you
probably just need to call EndCurrentEdit on the CurrencyManager.
-B
