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
618 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
What Security Threats Are You Missing?

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.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

15 Experts available now in Live!

Get 1:1 Help Now