Improve company productivity with a Business Account.Sign Up

x
?
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
?
632 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 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…
In the video, one can understand the process of resizing images in single or bulk. Kernel Bulk Image Resizer is an easy to use tool for resizing large number of images. One can add and resize multiple images with this tool in single go. The video sh…

585 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