?
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
Medium Priority
?
626 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 800 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

801 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