[wbwalsh]

Hi All! I am beginning work on my first VB app using SQL. I have worked extensively with Access and need to make the jump to VB/SQL to create apps that can be easily distributed. With that said here is the first of my questions I will have!

I don’t want to use built-in data binding through Visual Studio, I would like to learn and code directly. I have created a form with a listbox on the left side and several texbox on the right side. I have code working that populates a listbox from my data reader (MyReader). The listbox works fine. There are two (2) buttons over the listbox to select All Active records or All Inactive records that queries the field “Status” in the “tblAdvocates” table.

So the listbox works fine. Problem is trying to setup the SelectedIndexChanged function. I can’t figure out how to take the selected record from the listbox and use that to fill the textboxes. Do I “re-query” the database using the ID field from the table? Do I use the MyReader from the form load code and pull the data from that?

I’m really at a loss. Your guidance would be appreciated? At 42 years old I feel a little like a “dinosaur” trying to learn a new trick! LOL

Here is my code:

Imports System.Data.SqlClient
Imports System.Windows.Forms.ListBox
Imports System.Data
Imports System.Data.OleDb

Public Class frmAdvocates
	Inherits System.Windows.Forms.Form
	'Create ADO.NET objects.
	Private myConn As SqlConnection
	Private myCmd As SqlCommand
	Private myReader As SqlDataReader
	Private results As String

	Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
		'Close the database connection
		myReader.Close()
		myConn.Close()

		'Close this form
		Me.Close()
	End Sub

	Private Sub frmAdvocates_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

		'Clear items in listbox
		lbxVictimAdvocates.Items.Clear()

		'Create a Connection object.
		myConn = New SqlConnection("Initial Catalog=VictimAdvantage_db;" & _
				"Data Source=WALSH\SQLEXPRESS;Initial Catalog=VictimAdvantage_db;Integrated Security=True")

		'Create a Command object.
		myCmd = myConn.CreateCommand
		myCmd.CommandText = "SELECT NameFirst, NameMidInt, NameLast, ID, Status FROM tblAdvocates WHERE Status='Active' ORDER BY NameLast"

		'Open the connection.
		myConn.Open()

		'Fill reader
		myReader = myCmd.ExecuteReader()

		'Concatenate the query result into a string.
		Do While myReader.Read()
			results = results & myReader.GetString(2) & ", " & myReader.GetString(0) & " " & myReader.GetString(1) & vbLf
			lbxVictimAdvocates.Items.Add(myReader.GetString(2) & ", " & myReader.GetString(0) & vbLf)
		Loop

		'Check for DOB entry and calculate age or leave empty
				If txtDOB.Text = String.Empty Then
					txtAge.Text = ""
				Else
					txtAge.Text = DateDiff(DateInterval.Year, CDate(txtDOB.Text.Trim), Now.Date)
				End If

		'Display proper name on Advocate list groupbox
				gbxAdvocates.Text = "Advocates - Active"
				gbxAdvocates.ForeColor = Color.DarkGreen

		'Check for email address
		If txtEmail.Text = String.Empty Then
			btnSendEmail.Enabled = False
		Else
			btnSendEmail.Enabled = True
		End If
	End Sub

	Private Sub lbxVictimAdvocates_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbxVictimAdvocates.SelectedIndexChanged

		'txtNameLast.Text = lbxVictimAdvocates.Items(lbxVictimAdvocates.SelectedIndex)

		'Reference By Name, where Column Name = Description.
		txtNameLast.Text = myReader.Item(2).ToString()

		'Reference By Index (Zero Based).
		txtNameFirst.Text = myReader.Item(1).ToString()


		'Check for DOB entry and calculate age or leave empty
		If txtDOB.Text = String.Empty Then
			txtAge.Text = ""
		Else
			txtAge.Text = DateDiff(DateInterval.Year, CDate(txtDOB.Text.Trim), Now.Date)
		End If
		'Check for email address
		If txtEmail.Text = String.Empty Then
			btnSendEmail.Enabled = False
		Else
			btnSendEmail.Enabled = True
		End If
	End Sub

	Private Sub btnDisplayRecordsInactive_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplayRecordsInactive.Click
		'Display proper name on Advocate list groupbox
		gbxAdvocates.Text = "Advocates - Inactive"
		gbxAdvocates.ForeColor = Color.Red

		'Clear items in listbox
		lbxVictimAdvocates.Items.Clear()

		'Create a Connection object.
		myConn = New SqlConnection("Initial Catalog=VictimAdvantage_db;" & _
				"Data Source=WALSH\SQLEXPRESS;Initial Catalog=VictimAdvantage_db;Integrated Security=True")

		'Create a Command object.
		myCmd = myConn.CreateCommand
		myCmd.CommandText = "SELECT NameFirst, NameMidInt, NameLast, ID, Status FROM tblAdvocates WHERE Status='Inactive' ORDER BY NameLast"

		'Open the connection.
		myConn.Open()

		myReader = myCmd.ExecuteReader()

		'Concatenate the query result into a string.
		Do While myReader.Read()
			results = results & myReader.GetString(2) & ", " & myReader.GetString(0) & " " & myReader.GetString(1) & vbLf
			lbxVictimAdvocates.Items.Add(myReader.GetString(2) & ", " & myReader.GetString(0) & vbLf)
		Loop
	End Sub

	Private Sub btnDisplayAdvocatesActive_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplayAdvocatesActive.Click
		'Display proper name on Advocate list groupbox
		gbxAdvocates.Text = "Advocates - Active"
		gbxAdvocates.ForeColor = Color.DarkGreen

		'Clear items in listbox
		lbxVictimAdvocates.Items.Clear()

		'Create a Connection object.
		myConn = New SqlConnection("Initial Catalog=VictimAdvantage_db;" & _
			   "Data Source=WALSH\SQLEXPRESS;Initial Catalog=VictimAdvantage_db;Integrated Security=True")

		'Create a Command object.
		myCmd = myConn.CreateCommand
		myCmd.CommandText = "SELECT NameFirst, NameMidInt, NameLast, ID, Status FROM tblAdvocates WHERE Status='Active' ORDER BY NameLast"

		'Open the connection.
		myConn.Open()

		myReader = myCmd.ExecuteReader()

		'Concatenate the query result into a string.
		Do While myReader.Read()
			results = results & myReader.GetString(2) & ", " & myReader.GetString(0) & " " & myReader.GetString(1) & vbLf
			lbxVictimAdvocates.Items.Add(myReader.GetString(2) & ", " & myReader.GetString(0) & vbLf)
		Loop
	End Sub

	Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
	End Sub
End Class

[FlashM]

Hi!

Take a look at my code that I posted today in some other thread. It's quite similar to what you are looking for. Go through that code, ask if anything is unclear, try to use that code and feel free to ask whatever problems you encounter.

Populating A List Box With Query Results

[wbwalsh]

Looking at your code from the other post now. Will get back to you soon. Thanks.

[wbwalsh]

Sorry… I can’t relate that code to mine.
Can I use the “MyReader” data from “FormLoad” or do I need to capture the ID number and pass that to another select statement in “SelectedItemChanged”… or what code should I put where???
Any help would be appreciated.

[FlashM]

Actually the code is very much related to the thing you are trying to do. That's why I prepared an example just to show you how it's done. Of course, there are things you will have to do by your own. I'm using MS Access database (just switch to your DB provider and don't forget to update my Oledb statements. If anything is unclear, feel free to ask. Code is tested and it works.

Form class:

Imports System.Data
Imports System.Data.OleDb
Imports System.ComponentModel

Public Class Form1

	Private conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=testDatabase.mdb;User Id=;Password=;")
	Private advocates As New List(Of Advocate)

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

		Dim reader As IDataReader ''Here you can use your SqlDataReader
		Dim cmd As New OleDbCommand()
		cmd.CommandText = "SELECT ID, NameFirst, NameMidInt, NameLast, Status FROM tblAdvocates"
		cmd.Connection = conn

		Try
			conn.Open()
			reader = cmd.ExecuteReader()

			Do While reader.Read()
				Dim advocate As New Advocate()
				With advocate
					.ID = reader(0)
					.FirstName = reader(1)
					If reader(2) Is DBNull.Value Then
						.MiddleName = String.Empty
					Else
						.MiddleName = reader(2)
					End If
					.LastName = reader(3)
					.Status = reader(4)
				End With
				advocates.Add(advocate)
			Loop

		Catch ex As Exception
			MsgBox(ex.Message)
		Finally
			conn.Close()
			conn.Dispose()
			reader.Close()
			reader.Dispose()
			cmd.Dispose()
		End Try

	End Sub

	Private Sub btnShowAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowAll.Click
		Dim tempList As New BindingList(Of Advocate)(advocates)
		Dim bsource As New BindingSource
		bsource.DataSource = tempList
		lstAdvocates.DataSource = bsource.DataSource
		lstAdvocates.DisplayMember = "Name"
		lstAdvocates.ValueMember = "ID"
	End Sub

	Private Sub lstAdvocates_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstAdvocates.SelectedIndexChanged
		If lstAdvocates.SelectedIndex > -1 Then
			Dim advocate As Advocate = lstAdvocates.SelectedItem

			txtId.Text = advocate.ID
			txtFirstName.Text = advocate.FirstName
			txtMiddleName.Text = advocate.MiddleName
			txtLastName.Text = advocate.LastName
			txtStatus.Text = advocate.Status
		End If
	End Sub
End Class

The Advocate class:

Public Class Advocate
	Private _id As Long
	Private _firstName As String
	Private _middleName As String
	Private _lastName As String
	Private _status As String

	Public Property ID() As Long
		Get
			Return _id
		End Get
		Set(ByVal value As Long)
			_id = value
		End Set
	End Property

	Public Property FirstName() As String
		Get
			Return _firstName
		End Get
		Set(ByVal value As String)
			_firstName = value
		End Set
	End Property

	Public Property MiddleName() As String
		Get
			Return _middleName
		End Get
		Set(ByVal value As String)
			_middleName = value
		End Set
	End Property

	Public Property LastName() As String
		Get
			Return _lastName
		End Get
		Set(ByVal value As String)
			_lastName = value
		End Set
	End Property

	Public ReadOnly Property Name() As String
		Get
			Return LastName & ", " & FirstName
		End Get
	End Property

	Public Property Status() As String
		Get
			Return _status
		End Get
		Set(ByVal value As String)
			_status = value
		End Set
	End Property

End Class

[wbwalsh]

Thank You. Thank You. Thank You!

The code works great. I made the SQL connection changes and everything works fine. It will take me a day or two to "take it apart" and learn from it.

I have about 30 fields in each record and will add the rest of the fields to the form and then move on to update, add and delete functions.

Thanks agian!

[FlashM]

The advantage of this code is that when you select an item in a listbox, you don't select just the string, but you actually select the complete Advocate object with all data about this person (id, firstname, lastname, middlename, status, etc...).

And there is one more advantage: If you want to add new advocate into your listbox, instead adding it to a listbox control, just add it to bindinglist and it will be automatically added to a listbox, since there is a bindingsource connection between them. Also when you want to remove advocate from listbox, just remove it from your bindinglist or update any advocate data in bindinglist and changes made will be applied in your listbox as well.

[FlashM]

One more thing: relation between List(Of Advocates) and BindingList(Of Advocates) is really not needed and you should skip this step. Just create a BindingList(Of Advocate) as your global variable and insert objects into bindinglist just the way you would insert objects into List(Of Advocates).

[dhammasiri]

Hi FlashM, thank you so much for the guidance. Honestly, I am a newbee. In my database, I have two tables. Should I create two classes? In your example, when the application is loading, the tblAdvocates is selected and populate it in lstAdvocates. If I also want to select table2 (just say tblClient) and want to populate it in combobox1, can you help me to show the way?
Can you show me the example how to insert new data to the database?
Thanks in advance.

This post has been edited by dhammasiri: 02 January 2010 - 09:28 PM

[FlashM]

How many classes you will create depends on your needs, but it's good if every DB table / entity has its own class, but again this really depends on what you are trying to do. If you have more tables, the procedure is still the same. You will just have more binding lists, each one for its own table in DB.

[dhammasiri]

I try you code by it creates exception in:
reader.Close()
reader.Dispose()
It says that it is used before it has been assigned a value and when I run the application it cannot run saying that the connection is closed. However, it is working if I do not put those codes.
Will it create a bug if I do not put those codes? How to solve this problem?

[FlashM]

Well, that's really strange. Could you post your complete code here to see what is causing this?

[dhammasiri]

I just copy and paste your code. However, I use SQLite as my database. I use the same class as yours. But I get exception at

reader.Close()
 reader.Dispose()

Here is my complatecode:

Imports System.Data.SQLite
Imports System.ComponentModel
Public Class Form1
	Private conn As New SQLite.SQLiteConnection("Data Source=" & Application.StartupPath & "\Data\DUBD2.db;")
	Private advocates As New List(Of Advocate)
  

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		Dim reader As SQLiteDataReader ''Here you can use your SqlDataReader
		Dim cmd As New SQLiteCommand()
		cmd.CommandText = "SELECT ID, NameFirst, NameMidInt, NameLast, Status FROM Entry"
		cmd.Connection = conn

		Try
			conn.Open()
			reader = cmd.ExecuteReader()

			Do While reader.Read()
				Dim advocate As New Advocate()
				With advocate
					.ID = reader(0)
					.FirstName = reader(1)
					If reader(2) Is DBNull.Value Then
						.MiddleName = String.Empty
					Else
						.MiddleName = reader(2)
					End If
					.LastName = reader(3)
					.Status = reader(4)
				End With
				advocates.Add(advocate)
			Loop

		Catch ex As Exception
			MsgBox(ex.Message)
		Finally
			conn.Close()
			conn.Dispose()
			reader.Close()
			reader.Dispose()
			cmd.Dispose()
		End Try

	End Sub
	Private Sub btnShowAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowAll.Click
		Dim tempList As New BindingList(Of Advocate)(advocates)
		Dim bsource As New BindingSource
		bsource.DataSource = tempList
		lstAdvocates.DataSource = bsource.DataSource
		lstAdvocates.DisplayMember = "Name"
		lstAdvocates.ValueMember = "ID"

	End Sub
	Private Sub lstAdvocates_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstAdvocates.SelectedIndexChanged
		If lstAdvocates.SelectedIndex > -1 Then
			Dim advocate As Advocate = lstAdvocates.SelectedItem

			txtId.Text = advocate.ID
			txtFirstName.Text = advocate.FirstName
			txtMiddleName.Text = advocate.MiddleName
			txtLastName.Text = advocate.LastName
			txtStatus.Text = advocate.Status
		End If

	End Sub

	
End Class 

[blaine69]

FlashM, on 12 December 2009 - 08:07 AM, said:

The advantage of this code is that when you select an item in a listbox, you don't select just the string, but you actually select the complete Advocate object with all data about this person (id, firstname, lastname, middlename, status, etc...).

And there is one more advantage: If you want to add new advocate into your listbox, instead adding it to a listbox control, just add it to bindinglist and it will be automatically added to a listbox, since there is a bindingsource connection between them. Also when you want to remove advocate from listbox, just remove it from your bindinglist or update any advocate data in bindinglist and changes made will be applied in your listbox as well.

Hi There Flash,

Been a while since this thread was modified however was just curious as i have worked through your code that you supplied to wbwalsh and have it working perfectly thanks heaps. Alot better way of binding controls.

Was wondering if you could please explain how to send the modified data back to the source database eg. A new record button, Update Button and Delete Button Code.

Your help would be much appreciated.

[whatquestions]

FlashM, on 11 December 2009 - 10:49 PM, said:

Actually the code is very much related to the thing you are trying to do. That's why I prepared an example just to show you how it's done. Of course, there are things you will have to do by your own. I'm using MS Access database (just switch to your DB provider and don't forget to update my Oledb statements. If anything is unclear, feel free to ask. Code is tested and it works.

Form class:

Imports System.Data
Imports System.Data.OleDb
Imports System.ComponentModel

Public Class Form1

	Private conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=testDatabase.mdb;User Id=;Password=;")
	Private advocates As New List(Of Advocate)

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

		Dim reader As IDataReader ''Here you can use your SqlDataReader
		Dim cmd As New OleDbCommand()
		cmd.CommandText = "SELECT ID, NameFirst, NameMidInt, NameLast, Status FROM tblAdvocates"
		cmd.Connection = conn

		Try
			conn.Open()
			reader = cmd.ExecuteReader()

			Do While reader.Read()
				Dim advocate As New Advocate()
				With advocate
					.ID = reader(0)
					.FirstName = reader(1)
					If reader(2) Is DBNull.Value Then
						.MiddleName = String.Empty
					Else
						.MiddleName = reader(2)
					End If
					.LastName = reader(3)
					.Status = reader(4)
				End With
				advocates.Add(advocate)
			Loop

		Catch ex As Exception
			MsgBox(ex.Message)
		Finally
			conn.Close()
			conn.Dispose()
			reader.Close()
			reader.Dispose()
			cmd.Dispose()
		End Try

	End Sub

	Private Sub btnShowAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowAll.Click
		Dim tempList As New BindingList(Of Advocate)(advocates)
		Dim bsource As New BindingSource
		bsource.DataSource = tempList
		lstAdvocates.DataSource = bsource.DataSource
		lstAdvocates.DisplayMember = "Name"
		lstAdvocates.ValueMember = "ID"
	End Sub

	Private Sub lstAdvocates_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstAdvocates.SelectedIndexChanged
		If lstAdvocates.SelectedIndex > -1 Then
			Dim advocate As Advocate = lstAdvocates.SelectedItem

			txtId.Text = advocate.ID
			txtFirstName.Text = advocate.FirstName
			txtMiddleName.Text = advocate.MiddleName
			txtLastName.Text = advocate.LastName
			txtStatus.Text = advocate.Status
		End If
	End Sub
End Class

The Advocate class:

Public Class Advocate
	Private _id As Long
	Private _firstName As String
	Private _middleName As String
	Private _lastName As String
	Private _status As String

	Public Property ID() As Long
		Get
			Return _id
		End Get
		Set(ByVal value As Long)
			_id = value
		End Set
	End Property

	Public Property FirstName() As String
		Get
			Return _firstName
		End Get
		Set(ByVal value As String)
			_firstName = value
		End Set
	End Property

	Public Property MiddleName() As String
		Get
			Return _middleName
		End Get
		Set(ByVal value As String)
			_middleName = value
		End Set
	End Property

	Public Property LastName() As String
		Get
			Return _lastName
		End Get
		Set(ByVal value As String)
			_lastName = value
		End Set
	End Property

	Public ReadOnly Property Name() As String
		Get
			Return LastName & ", " & FirstName
		End Get
	End Property

	Public Property Status() As String
		Get
			Return _status
		End Get
		Set(ByVal value As String)
			_status = value
		End Set
	End Property

End Class

hi i tried this code, and it worked perfectly, but i have another question, what if i want to add search function, how do i use the same listbox to display or “filter" the data that "SearchTextBox.Text"want??