Solved

VB.Net Populating a list box from SQLServer DB and binding a text box to the list box!

Posted on 2004-04-08
6
621 Views
Last Modified: 2008-07-03
Hi!

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()
  Try
            strConn = ModSQLConnection.GetConnectionString() 'Calls a seperate function to build by SQLserver connectionString
            conn.ConnectionString = strConn
            conn.Open()
            ' 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
            lstBoxSupplierContracts.Refresh()
            conn.Close()
        Catch oExcept As Exception ' catches any errors!
            MessageBox.Show("Error executing SQL: " & _
             oExcept.Message, "btnExecute_Click()")
        End Try



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!

Many thanks,

Si.

(It's been one of those long days, so I hope this all makes sense!)





0
Comment
Question by:BigSi
  • 3
  • 3
6 Comments
 
LVL 2

Author Comment

by:BigSi
ID: 10784326
Oh and this line:
 'Dim ds As New DataSet("myTable")
was commented out as I declaired this at the top of my form code.
0
 
LVL 7

Accepted Solution

by:
ramesh12 earned 200 total points
ID: 10784374
0
 
LVL 7

Expert Comment

by:ramesh12
ID: 10784411
Change

'Dim ds As New DataSet("myTable") as

Protected ds as Dataset("myTable")


In Page_load

// Consider this as pseudo code not everything with syntax

if not ispostback()

Set your dataset here

end if


and then when ever you want to set the bindings

do that as  shown in the above comment

0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 7

Expert Comment

by:ramesh12
ID: 10784681
Oops sorry , I thought you are looking for asp.net

Just ignore the above post.

The URL post should solve your problem
0
 
LVL 2

Author Comment

by:BigSi
ID: 10784711
Thanks Ramesh12, I had a quick look at this sollution but it looks like there is quite a bit to it!
I won't be able to try any of it until tuesday, but I am still open for suggestions.

Thanks!

Si.
0
 
LVL 2

Author Comment

by:BigSi
ID: 10811823
Thanks Ramesh, it worked just how I wanted it to allthough I have now decided I would be better off running a new query to get the required data everytime something is selected in the list box to allow the user to see up to date data.

Apart from that, the code is perfect and was exactly what I wanted.

For any one else reading this post the following code is what I used for my sollution:

Declaired at the top of the form:
Dim ds As New DataSet("myTable")
Dim objDV As DataView


Private Sub RefreshSupplierContractsListBox()
        Dim strConn As String
        Dim conn As New SqlConnection()

        Try
            strConn = ModSQLConnection.GetConnectionString()
            conn.ConnectionString = strConn
            conn.Open()
            ' 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)
            da.Fill(ds, "myTable")
            lstBoxSupplierContracts.DataSource = ds.Tables("myTable").DefaultView
            objDV = New DataView(ds.Tables(0))

            txtContractReference.DataBindings.Add("Text", objDV, "ContractInfo")
            txtSupplier.DataBindings.Add("Text", objDV, "ContractInfo")
            'Assign the field to be shown in the listbox
            lstBoxSupplierContracts.DisplayMember = "ContractInfo"
            'Assign the field as the value of the selected item in the list box
            lstBoxSupplierContracts.ValueMember = "SupplierContractID"
            'Refresh the list box
            lstBoxSupplierContracts.Refresh()
            conn.Close()
        Catch oExcept As Exception
            MessageBox.Show("Error executing SQL: " & _
             oExcept.Message, "RefreshSupplierContractsListBox()")
        End Try
    End Sub

    Private Sub lstBoxSupplierContracts_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstBoxSupplierContracts.SelectedIndexChanged
        If IsNumeric(lstBoxSupplierContracts.SelectedValue) Then
            objDV.RowFilter = "[SupplierContractID] = " & lstBoxSupplierContracts.SelectedValue
        End If
    End Sub



Thanks again,

Si.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Regular expression help 2 35
Powershell File Sort 8 41
Convert Ctime to date time in textfile? 7 34
Groupbox Control ? 2 19
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 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