We help IT Professionals succeed at work.

How to Populate a ListBox Control with Values from an SQL Server Express Database Table

FaheemAhmadGul
FaheemAhmadGul used Ask the Experts™
on
I have ListBox1 Control on a Visual Studio 2008 Project in Visual Basic.  
I need help with code for the initialise event of Form1 which will populate this ListBox1 with Values (in alphabetical order) from the ClientName field of all records in a table named ClientNames) in an SQL Server Express Edition 2005 Database.

The connection string for connecting to my SQL Server Express Database is as follows:
ConnectionString = "Data Source=VISTA-ON-MAC\SQLEXPRESS;Initial Catalog=WriterDemo;Persist Security Info=True;User ID=sa;Password=peter;Connect Timeout=30"


Many thanks for your help.
Please note I am using Visual Studio 2008 for a Visual Studio 2008 Project.

The code that I am providing in the Code window pulls one ClientName from ClientNames Table into a textbox  named txtNotes on a Form  in my project.

The code that solved my previous problem on this theme is here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26206780.html
However my previous question on this theme was about filling a listbox with Data from an Access Database.


Many thanks for your help in anticipation.

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Public Class ViewClientNames

    Dim sqlConn As New SqlConnection
    Private mySQLConnection As New SqlConnection
    Private mySQLDataAdapter As SqlDataAdapter
    Private mySQLCommandBuilder As SqlCommandBuilder
    Private myDataTable As New DataTable
    Private RowPosition As Integer
    Private Sub ViewClientNames_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        mySQLConnection.Close()
        mySQLConnection.Dispose()
    End Sub
    Private Sub ViewClientNames_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        mySQLConnection.ConnectionString = "Data Source=VISTA-ON-MAC\SQLEXPRESS;Initial Catalog=WriterDemo;Persist Security Info=True;User ID=sa;Password=peter;Connect Timeout=30"
        mySQLConnection.Open()
        mySQLDataAdapter = New SqlDataAdapter("SELECT note FROM ClientNames", mySQLConnection)
        mySQLCommandBuilder = New SqlCommandBuilder(mySQLDataAdapter)
       
        mySQLDataAdapter.Fill(myDataTable)
        Me.ShowCurrentRecord()
    End Sub
    Private Sub ShowCurrentRecord()
        If myDataTable.Rows.Count = 0 Then
            txtNotes.Text = ""
            Exit Sub
        End If
        txtNotes.Rtf = myDataTable.Rows(RowPosition)("ClientName").ToString()
       

    End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
As before... change the query:

---
        mySQLDataAdapter = New SqlDataAdapter("SELECT note FROM ClientNames", mySQLConnection)
should be
        mySQLDataAdapter = New SqlDataAdapter("SELECT ClientName FROM ClientNames", mySQLConnection)
---

and then loop through them:

---
        If myDataTable.Rows.Count = 0 Then
            Exit Sub
        End If
For i = 0 to myDataTable.Rows.Count - 1
ListBox1.Items.Add(myDataTable.Rows(i)("ClientName").ToString())
Next
---

As before- there are other ways to achieve... but this follows on from your code :)

Ta

L

Author

Commented:
This has worked very nicely. Many thanks.
Regards
Most Valuable Expert 2014

Commented:
You may want to add an ORDER BY clause.

And over time use SQLString variable to just aid readabilty. Once your queries get complex it makes it easier to digest.

Just my $0.02.


Private SQLString as String

SQLString = "SELECT ClientName " & _
            "FROM ClientNames " & _
            "ORDER BY ClientNames;" 
....
....
....mySQLDataAdapter = New SqlDataAdapter(SQLString, mySQLConnection)

Open in new window