Solved

Bound Combo Box with Bound Text Box SQL Database

Posted on 2008-10-20
27
1,888 Views
Last Modified: 2012-05-05
I have a bound combo box to an sql database, and I have several bound text boxes that are also bound / vis the data bindings in it's properties. Currently I can pick my client A in my combo box and client B in my text box works fine. However, the text boxes default to client A's address since it is bound to the address.

Here is what I want..
The text box to be empty and when the client A or client B is selected, would like the text boxes to be updated. I think I do not what to bind the text box through the properties, instead code the following event to update the text boxes with the appropriate information. Not sure how to do this exactly and not sure if there is a standardized way this should be done.

private void MyComboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
           
        }
0
Comment
Question by:mhjassoc
  • 15
  • 12
27 Comments
 
LVL 18

Expert Comment

by:Priest04
ID: 22765466
One way would be

private void MyComboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    textBox1.Text = DataSetName.Tables["TableName"].Rows[bindingSource1.Postion]["field_name"].ToString();
}

Goran
0
 

Author Comment

by:mhjassoc
ID: 22798319
I get a NullReferenceException was unhandled / object reference not set to an instance of an object
0
 
LVL 18

Expert Comment

by:Priest04
ID: 22798425
Well, you would need to change above code to match your objects.

DataSetName - its the instance of your DataSet class
tableName - name of the table in DataSet
bindingSource1 - its the instance of the BindingSource class
field_name - name of the field in table

The Exception you get means that you are trying to use some object which is not instantiated (that is null). Example, if you have spelled TableName properly, then

DataSet.Tables["TableName"] will return null reference, and will throw above exception.
0
 

Author Comment

by:mhjassoc
ID: 22803962
I had a typo in my table name... Looks like working but when I select an item in my drop down always get the same field data ie get the first addressin database of the client. Not sure why???
0
 

Author Comment

by:mhjassoc
ID: 22804020
Looked at debug and it appears it just gabs the first address from the row in the table.
Two things...

1. Hopw to resolve the above issue "not sure if the value needs to be released etc"
2. What if I need multiple text box to be updated based on the selection? Here I need the address, address2, city, state and zip code.

Appreciate the information...
0
 
LVL 18

Expert Comment

by:Priest04
ID: 22804576
1. dont understand what you mean
2. since you want to populate textbox manually, then all other related textboxes should also be populated manually,

txtAddress.Text = DataSetName.Tables["TableName"].Rows[bindingSource1.Postion]["address"].ToString();

etc
0
 

Author Comment

by:mhjassoc
ID: 22804858
I have two tables in the same database. One with a list of client names and another table with their address. No matter what client I select I get back the street address of the first client. Looking at the debug appears there is something missing to point to the correct position to match the client with the street address. I would also like to add city, state, and zip.. Any suggestions would be appreciated
0
 
LVL 18

Expert Comment

by:Priest04
ID: 22805177
So, what you are saying is that you have 2 DataTables, Client and Address? And when you select a client, you want to get its address, and display it in textboxes? And you dont want to use databinding, but you want to populate it manually?

Is the data on the form read only?


0
 

Author Comment

by:mhjassoc
ID: 22805604
I tried using the databinding and could not get it to work. This is why I put this out here to see how to do this and the proper way. Yes what you said is what I need to do. I have no prefeances just must be something standard / common. Never checked the date on the form... Never even thought to??? Please explain what this is and will look Monday. Will be unavailable Sunday...
0
 
LVL 18

Expert Comment

by:Priest04
ID: 22805731
>> Is the data on the form read only?

You misunderstood. I asked if data (information in textboxes) is meant only for reading, or you want to update it too. This I ask because if it is only for reading, then you can use a SQL query that will JOIN data from Client and Address tables, and bring joined data in one DataTable only. Then you wont need to synchronize data, since all data will be in one DataTable only.

Synchronizing data from 2 DataTables on databound controls is achieved with DataRelation. I have made an example for you to see how it works. Paste the below code on the form, and add one ComboBox named cmbClients, and one TextBox named txtAddress.
Private Function GetClients() As DataTable

    ' first we create a DataTable and it structure

    ' first create a DataTable object

    Dim clients As New DataTable("Clients")

    ' then create a DataColumn for the DataTable

    Dim col As New DataColumn("ClientID", GetType(Integer))

    ' add this column to clients DataTable

    clients.Columns.Add(col)

    ' same thing for the second column

    col = New DataColumn("ClientName", GetType(String))

    clients.Columns.Add(col)

    

    ' above code will populate 3 rows of data in clients DataTable

    Dim row As DataRow = clients.NewRow()

    row(0) = 1

    row(1) = "First Client"

    clients.Rows.Add(row)

    

    row = clients.NewRow()

    row(0) = 2

    row(1) = "Second client"

    clients.Rows.Add(row)

    

    row = clients.NewRow()

    row(0) = 3

    row(1) = "Third client"

    clients.Rows.Add(row)

    

    Return clients

End Function
 

Private Function GetAddress() As DataTable

    ' the code is very similar to code for creating client DataTable

    ' except that we add another column of type ClientID

    ' which will be used for creating a relationship betweem clients and address DataTables

    Dim address As New DataTable("Address")

    Dim col As New DataColumn("AddressID", GetType(Integer))

    address.Columns.Add(col)

    col = New DataColumn("ClientID", GetType(Integer))

    address.Columns.Add(col)

    col = New DataColumn("address", GetType(String))

    address.Columns.Add(col)

    

    Dim row As DataRow = address.NewRow()

    row(0) = 100

    row(1) = 1

    row(2) = "First Clients address"

    address.Rows.Add(row)

    

    row = address.NewRow()

    row(0) = 101

    row(1) = 2

    row(2) = "Second clients address"

    address.Rows.Add(row)

    

    row = address.NewRow()

    row(0) = 102

    row(1) = 3

    row(2) = "Third clients address"

    address.Rows.Add(row)

    

    Return address

End Function
 

Private Sub Form1_Load(sender As Object, e As EventArgs)

    ' this will create clients datatable and add some data in it

    Dim clients As DataTable = GetClients()

    ' this will create address datatable and add some data in it

    Dim address As DataTable = GetAddress()

    

    ' we create a new dataset

    Dim ds As New DataSet()

    ' we add clients and address DataTables to it

    ds.Tables.Add(clients)

    ds.Tables.Add(address)

    ' this is the catch. we create a new DataRelation object,

    ' which will synchronize data between clients and address DataTables.

    ' first parameter is the relation name, second is the ClientID column from the clients table,

    ' and third is the ClientID column from the address tables.

    Dim relation As New DataRelation("clients_address", clients.Columns("ClientID"), address.Columns("ClientID"))

    ' add relation to DataSet

    ds.Relations.Add(relation)

    

    ' create new BindingSource object

    Dim clientsBS As New BindingSource()

    ' set its DataSource to DataSet, 

    clientsBS.DataSource = ds

    ' set DataMember property to the clients DataTable NAME

    clientsBS.DataMember = "Clients"
 

    ' create new BindingSource object    

    Dim addressBS As New BindingSource()

    ' Set the DataSource property of the addressBS to the clientsBS

    addressBS.DataSource = clientsBS

    ' set DataMember property to the data relation

    addressBS.DataMember = "clients_address"

    

    ' now bind cmbClients to clientsBS

    cmbClients.DataSource = clientsBS

    cmbClients.DisplayMember = "ClientName"

    cmbClients.ValueMember = "ClientID"

    

    ' bind TextBox to addressBS

    txtAddress.DataBindings.Add("Text", addressBS, "address")

End Sub

Open in new window

0
 

Author Comment

by:mhjassoc
ID: 22813620
Thank's I will give it a try tonight..
All the data is read only at this point... This may need to be read / write but not sure at this moment.
0
 

Author Comment

by:mhjassoc
ID: 22840535
Sorry for the delay...
Looks like your example is in VB using C#...

I think my solution is in using "system.data.datarelation do you have any information on this
0
 
LVL 18

Expert Comment

by:Priest04
ID: 22840909
:) actually, I have mistaken that you are using VB, so I translated it from C# to VB. :) Here is the c# version.

As you can see in the example, I am using DataRelation class.
private DataTable GetClients()

{

    // first we create a DataTable and it structure

    // first create a DataTable object

    DataTable clients = new DataTable("Clients");

    // then create a DataColumn for the DataTable

    DataColumn col = new DataColumn("ClientID", typeof(int));

    // add this column to clients DataTable

    clients.Columns.Add(col);

    // same thing for the second column

    col = new DataColumn("ClientName", typeof(string));

    clients.Columns.Add(col);

    

    // above code will populate 3 rows of data in clients DataTable

    DataRow row = clients.NewRow();

    row[0] = 1;

    row[1] = "First Client";

    clients.Rows.Add(row);

    

    row = clients.NewRow();

    row[0] = 2;

    row[1] = "Second client";

    clients.Rows.Add(row);

    

    row = clients.NewRow();

    row[0] = 3;

    row[1] = "Third client";

    clients.Rows.Add(row);

    

    return clients;

}
 

private DataTable GetAddress()

{

    // the code is very similar to code for creating client DataTable

    // except that we add another column of type ClientID

    // which will be used for creating a relationship betweem clients and address DataTables

    DataTable address = new DataTable("Address");

    DataColumn col = new DataColumn("AddressID", typeof(int));

    address.Columns.Add(col);

    col = new DataColumn("ClientID", typeof(int));

    address.Columns.Add(col);

    col = new DataColumn("address", typeof(string));

    address.Columns.Add(col);

    

    DataRow row = address.NewRow();

    row[0] = 100;

    row[1] = 1;

    row[2] = "First Clients address";

    address.Rows.Add(row);

    

    row = address.NewRow();

    row[0] = 101;

    row[1] = 2;

    row[2] = "Second clients address";

    address.Rows.Add(row);

    

    row = address.NewRow();

    row[0] = 102;

    row[1] = 3;

    row[2] = "Third clients address";

    address.Rows.Add(row);

    

    return address;

}
 

private void Form1_Load(object sender, EventArgs e)

{

    // this will create clients datatable and add some data in it

    DataTable clients = GetClients();

    // this will create address datatable and add some data in it

    DataTable address = GetAddress();

    

    // we create a new dataset

    DataSet ds = new DataSet();

    // we add clients and address DataTables to it

    ds.Tables.Add(clients);

    ds.Tables.Add(address);

    // this is the catch. we create a new DataRelation object,

    // which will synchronize data between clients and address DataTables.

    // first parameter is the relation name, second is the ClientID column from the clients table,

    // and third is the ClientID column from the address tables.

    DataRelation relation = new DataRelation("clients_address", clients.Columns["ClientID"], address.Columns["ClientID"]);

    // add relation to DataSet

    ds.Relations.Add(relation);

    

    // create new BindingSource object

    BindingSource clientsBS = new BindingSource();

    // set its DataSource to DataSet, 

    clientsBS.DataSource = ds;

    // set DataMember property to the clients DataTable NAME

    clientsBS.DataMember = "Clients";

    

    // create new BindingSource object    

    BindingSource addressBS = new BindingSource();

    // Set the DataSource property of the addressBS to the clientsBS

    addressBS.DataSource = clientsBS;

    // set DataMember property to the data relation

    addressBS.DataMember = "clients_address";

    

    // now bind cmbClients to clientsBS

    cmbClients.DataSource = clientsBS;

    cmbClients.DisplayMember = "ClientName";

    cmbClients.ValueMember = "ClientID";

    

    // bind TextBox to addressBS

    txtAddress.DataBindings.Add("Text", addressBS, "address");

}

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:mhjassoc
ID: 22841986
Just trying to get my head around this b/c would like a simplistic solution for down the road additions etc. I follow for the most part what you put together here. What I have and know + what I am looking at is the following:
Two Table Adapters
ClientName
ClientAddress

One Data Set
DataSetClientName with the above two tables included i.e. Fill, GetData() SQL statement for both tables

Three Bindingsources
ClientName
ClientAddress
BOTH ClientName and ClientAddress

I thought I could use this binding source to relate both tables, but not sure and then I see you using the  data relation and just not sure I follow the logic. Perhaps my thoughts on data sets, binding sources, and table adapters is not clear. I prefer to use the designers in VS b/c of the visual simplistic aspects but like I said I thought the binding source connects my two other bindings and from there can work with this overall binding to do what I am doing.

Sorry for the continued questions. I am traveling, so access to internet has been hit and miss thus the delays here and there. Thank You for all your help!


0
 
LVL 18

Expert Comment

by:Priest04
ID: 22846251
mhjassoc, DataRelation IS used for relating two tables in a dataset, its her purpose. Its very simple:

You have created 1 dataset, with 2 datatables (clientName and ClientAddress), and 2 bindingSources - all through wizard.

Next step is to add a relation between these two tables.

In order to help you how to do that, if you dont understand the above example, you would need to post here

 - structure of tables ClientName and ClientAddress
 - names of the Client and Address DataTables
0
 

Author Comment

by:mhjassoc
ID: 22849926
Here is what I have for both tables seem to be dense on this I guess.

dbo.ClientName
ClientID(PK, int, not null)
ClientName (nvchar(60), not null)
rowguid (uniqueidentifer, not null)
ModifiedDate (datetime, not null)

dbo.ClientAddress
AddressID (PK, int, not null)
ClientID (FK, int, not null)
AddressLine1 (nvarchar(60), not null)
AddressLine2 (nvarchar(60), null)
City (nvarchar(30), not null)
State (nvarchar(50), not null)
PostalCode (nvarchar(15), not null)
rowguid (uniqueidentifer, not null)
ModifiedDate (datetime, not null)

Thanks Again!

0
 
LVL 18

Expert Comment

by:Priest04
ID: 22880555
mhjassoc, I now see you didnt get my response. Must the browser didnt pass the message, and I thought it. I am writting it again.

After you have created dataset with two tables, use this code to create DataRelation. You didnt wrote the names of the DataTables, so I will assume its the same as database tables names.


DataRelation relation = new DataRelation("clients_address", ds.Tables["ClientName"].Columns["ClientID"], ds.Tables["ClientAddress"].Columns["ClientID"]);
 

// add relation to DataSet

ds.Relations.Add(relation);

    

// create new BindingSource object

BindingSource clientsBS = new BindingSource();

// set its DataSource to DataSet, 

clientsBS.DataSource = ds;

// set DataMember property to the clients DataTable NAME

clientsBS.DataMember = "ClientName";

    

// create new BindingSource object    

BindingSource addressBS = new BindingSource();

// Set the DataSource property of the addressBS to the clientsBS

addressBS.DataSource = clientsBS;

// set DataMember property to the data relation

addressBS.DataMember = "clients_address";

    

// now bind cmbClients to clientsBS

cmbClients.DataSource = clientsBS;

cmbClients.DisplayMember = "ClientName";

cmbClients.ValueMember = "ClientID";

    

// bind TextBox to AddressLine1 field

txtAddress.DataBindings.Add("Text", addressBS, "AddressLine1");

Open in new window

0
 

Author Comment

by:mhjassoc
ID: 22900137
I do not follow this part...

// now bind cmbClients to clientsBS
cmbClients.DataSource = clientsBS;
cmbClients.DisplayMember = "ClientName";
cmbClients.ValueMember = "ClientID";
0
 
LVL 18

Expert Comment

by:Priest04
ID: 22900695
What exactly you dont understand?
// bind cmbClients to clientsBS

cmbClients.DataSource = clientsBS;

// set DisplayMember to the name of the field that you want to be displayed in the drop down list

cmbClients.DisplayMember = "ClientName";

// this is the actual value of the item in the combobox list - usually a primary key

cmbClients.ValueMember = "ClientID";

Open in new window

0
 

Author Comment

by:mhjassoc
ID: 22902432
cmbClinets icmbclinets is not defined anywhere in the code you sent me. Therefore nothing i.e. DataSource, DisplayMember, nor ValueMember are available attributes that can be set.
0
 

Author Comment

by:mhjassoc
ID: 22902436
cmbClinets is not defined anywhere in the code you sent me. Therefore nothing i.e. DataSource, DisplayMember, nor ValueMember are available attributes that can be set.
0
 
LVL 18

Expert Comment

by:Priest04
ID: 22903131
mhjassoc, cmbClients is just the name I used for the ComboBox you are using on you form. I will quote you:

>> I have a bound combo box to an sql database, and I have several bound text boxes that are also bound.

You should always name you controls properly (not just MyComboBox1).
0
 

Author Comment

by:mhjassoc
ID: 22934218
Ok...
I think I am close still working on this, but was using the designer vs. manual. Here is where I am at with this. Example works fine...

When I run the application I see the first client name in the combobox and when I select and make a change the correct ID is displayed. The problem is the ClientID is not what I am after. It is a foreign key in in the ClientAddress table. and is the primary key for the ClientName table.

What I want is the AddressLine1 from the ClientAddress table. Now when I select the combobox I always get the same address of the first item in the list. Since Example 1 works and Example 2 does not I am thinking I am missing something simple here...

If I have a relationship ClientID in my database set as a FK do I need to add a relationship between the name and address? Would think I would not have to do this but will play around with the relationship(s) to see what my results are.

I appreciate your help with this sorry I can only award 500 pts. since this has been going on longer than I anticipated.






Example 1: 

private void comboBox1_SelectedChangeComitted(object sender, EventArgs e)

        {

  textBox1.DataBindings.Clear();

            textBox1.DataBindings.Add("Text", ClientNameBindingSource, "ClientID");
 

        }
 

Example 2:
 

Example 1: 

private void comboBox1_SelectedChangeComitted(object sender, EventArgs e)

        {

  textBox1.DataBindings.Clear();

            textBox1.DataBindings.Add("Text", ClientAddressBindingSource, "ClientAddress");

}

Open in new window

0
 
LVL 18

Expert Comment

by:Priest04
ID: 22934764
Both examples are incorrect. By the way, you are at the position where you were at the beginning, if I remember well. :)

1) You cannot bind a control in an event which is fired whenever user selects different client from list. Databinding should be done one time only and the rest should be done automatically..
2) you cannot have two binding sources, than each point to distinct datatable, in that cas they will be independent, and will not know anything about each other - and in this case you need to relate them

You haven't gone through my first code. If you did, you would see the result you are after, and you would know that you have the solutions, and that you just need to implement it in your situation. the only thing you need to do in order to try the examples, is to create new project, add one combobox control named cmbClients, and one textbox control named txtAddress, And, I just say, you also need to add ......Handles MyBase.Load, since I converted c# code and I forgot to add it. So after you copy the code, just change this line

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load ' add Handles.....

after you have executed the code, and see that it works as you want it, you will pay attention to Load event> What is the key is:

1) you dont need to create datase, datatable clients and datatable address, since you already create them in designer. If you dont create dataset in designer, then you should create it manually, since it needs to contain both tables in it.
2) the following code I am talking about should be places in an event that is fired before the form is displayed (example, in form load ecent)
3) if you dont create dataset in designer, this is the time when you need to create it, and add datatables in it

Dim ds as New DataSet()

ds.Tables.Add(dtClients) ' add clients datatable that you have created in designer
ds.Tables.Add(dtClientAddress) ' add client address datatable that you have created in designer

4) After you have dataset with clients and clientsaddress datatables, you need to explain in code what is the relation between them, if any. Since these two datatables are logically related, we also need to create a relation between them. This is what DataRelation is used for.

' here we create a relation with name clients_address, and we tell it that it should relate two columns in two different tables
Dim relation As New DataRelation("clients_address", clients.Columns("ClientID"), address.Columns("ClientID"))
' after we have created it, we still need to add this relation to DataSet
ds.Relations.Add(relation)

5) now we create two binding sources objects. If you now think "I have already created binding sources in the designer", just make SURE that these two objects have exactly the same properties like I have set them in the following code:


Dim clientsBS As New BindingSource() ' create new BindingSource
clientsBS.DataSource = ds ' set its DataSource to DataSet,
clientsBS.DataMember = "Clients" ' set DataMember property to the clients DataTable NAME
 
Dim addressBS As New BindingSource() ' create new BindingSource object    
addressBS.DataSource = clientsBS ' Set the DataSource property to the clientsBS - key moment
addressBS.DataMember = "clients_address" ' set DataMember property to the name of data relation object we have previously created

6) now we have everything prepared that we need for databinding. Data binding is also done in form load code. I will bind one combobox, that will contain clients in the list, and one textbox, that will display the address of the clients

' now bind cmbClients to clientsBS
cmbClients.DataSource = clientsBS
cmbClients.DisplayMember = "ClientName"
cmbClients.ValueMember = "ClientID"
   
' bind TextBox to addressBS
txtAddress.DataBindings.Add("Text", addressBS, "ClientAddress")

And that is all you need to to make this work.
0
 

Author Comment

by:mhjassoc
ID: 22935542
My example 1 worked fine, and read through your code again and missed something you repeated several times...

Here is what I did to get it to work:
1. Created a data source within the designer and selected both client and client address tables.
2. Noticed under the Data Sources Tab / Window that I see Client Address table at the top and the Client Name at the bottom and when you expand the Client Name there is also the Client Address "all is good".
3. Then I created two binding sources: ClientNameBS and ClientAddressBS with the designer and it automatically created the table adapters for each.
4. Then I set the combo box properties: DataSource: ClientNameBS, DisplayMember: ClientName, and ValueMember: ClientID
5. Then I changed the properties of the ClientAddressBS: DataMember: To the FK and used the same data source as the ClientName

Now all the pluming was in place, so the only code I needed was how I would display / use the data in the windows form.

Let me know if this sounds good to you and I appreciate all the information and help! THE TRICK WAS STEP 5
0
 
LVL 18

Accepted Solution

by:
Priest04 earned 500 total points
ID: 22935698
well, we disagree in the step 5, where I set datasource for the ClientAddressBS to ClientNameBS , and DataMember property to datarelation, but I guess fk could work too.

I have just visited microsoft site to see what they say about it, and I have found an example where they show master/detail form with 2 datagridview's - similar to your situation. In this example, they also use DataRelation, but if you find your way working, then I guess you can leave it as it is.

Here is the link for information

http://msdn.microsoft.com/en-us/library/c12c1kx4.aspx
0
 

Author Closing Comment

by:mhjassoc
ID: 31507843
Yes...
I looked at that the other day but l think what I have makes sense. I did try what you had for the data relation and I get a message during compile that the relation already exists. That's how I new I was on the right track with this. Then reading your example it made sense to just use the relation "FK" that exists in the database. I think if the "FK" was not present then it would work the way you are going, but only time will tell if this works well or not. At the moment it seems to work.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now