?
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
?
628 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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