VB.Net Populating a list box from SQLServer DB and binding a text box to the list box!
Posted on 2004-04-08
A while back I came up with a VB.Net sollution that worked with an Access database. I am trying to do some thing along the same lines again, but instead I will be using a database in SQL server.
In my previous application, I added an OLEDBConnection and OLEDBDataAdapter and also a DataSet to a form in the form design view to create my application. By doing it this way, I was able to populate a list box using its databindings:
DataSource (refrencing the required dataset
DisplayMember (refrencing the required dataset table and field name)
ValueMember (again referencing the required dataset table and field name which was its primary key)
By using the form design view I was able to reference the dataset to also be used by a textbox that was on the same form by referncing the same dataset in the text boxes 'Text' property under Databindings. By doing this, when I clicked the various list box records, the associated text box changed with it.
So for example I would have a table of employees in my dataset. The list box would show Employee Surname and would also contain the value of the employee's unique ID. When I selected an employee name on the list, the text box to the right of the list box would then show the relevent addressLine1 data.
I have tried to re-create this scenareo, but for some reason I am unable to the application to work in the same way as I can not create an SQLserver connection in the design view.
I decided to see if I could do this by code instead and have so far managed to populate a list box using the something simmilar to the following code:
Dim strConn As String
Dim conn As New SqlConnection()
strConn = ModSQLConnection.GetConnectionString() 'Calls a seperate function to build by SQLserver connectionString
conn.ConnectionString = strConn
' Create a data adapter
Dim da As New SqlDataAdapter("SELECT ContractsSupplierContract.SupplierContractID, ContractsSupplier.SupplierName + ': ' + ContractsSupplierContract.SupplierContractRef AS ContractInfo FROM ContractsSupplier INNER JOIN ContractsSupplierContract ON ContractsSupplier.SupplierID=ContractsSupplierContract.SupplierID ORDER BY ContractsSupplier.SupplierName;", conn)
'Dim ds As New DataSet("myTable")
da.Fill(ds, "myTable") ' populates the dataset
lstBoxSupplierContracts.DataSource = ds.Tables("myTable").DefaultView ' sets the list box contents to be based upon the dataset
txtContractReference.DataBindings.Add("text", ds.Tables("myTable"), "ContractInfo") lstBoxSupplierContracts.DisplayMember = "ContractInfo" 'sets which field to be seen in the listbox
lstBoxSupplierContracts.ValueMember = "SupplierContractID" 'sets what the listbox field value is assigned to
Catch oExcept As Exception ' catches any errors!
MessageBox.Show("Error executing SQL: " & _
The above code was added to the forms load event and seemed to work fine allthough selecting the list boxes contents did not change the text boxes value.
So far my attempt to resolve this was to use include the code:
txtContractReference.DataBindings.Add("text", ds.Tables("myTable"), "ContractInfo")
to try and get this to work, but all this does is just show the first records value even when I change the selected item in the list box.
THe only sollution I can think of at the moment is for me to populate the text boxe's value by runing a sepeate query on the database when an item in the list box has been selected - the only problem with this is that this connects to the server twice when I should be able to do this in one connection seeing as the dataset is capable of storing the required information.
I would be gratefull if anyone can help with the sollution to my problem (thats if you can understand what I'm trying to explain!).
If you need more information, please let me know!
(It's been one of those long days, so I hope this all makes sense!)