Hightlight rows in list box upon loading the from

Imports SqlParametersDB.Name
Imports VB = Microsoft.VisualBasic
Imports System.Data.SQLClient
 
 
Public Class frmagency
    Inherits System.Windows.Forms.Form
    Dim countprogram
    Dim Connection1 As New SqlClient.SqlConnection(basGlobals.sCon)
#Region " Windows Form Designer generated code "
 
    Public Sub New()
        MyBase.New()
 
        'This call is required by the Windows Form Designer.
        InitializeComponent()
 
        'Add any initialization after the InitializeComponent() call
 
    End Sub
 
    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
 
    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer
 
    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents DsAgency1 As SqlParametersDB.DSAgency
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents ListBox1 As System.Windows.Forms.ListBox
    Friend WithEvents SqlDataAdapter2 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents DspaReferal1 As SqlParametersDB.DSPAReferal
    Friend WithEvents SqlDataAdapter3 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents DspaExistingRef1 As SqlParametersDB.DSPAExistingRef
    Friend WithEvents SqlSelectCommand3 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlSelectCommand2 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand2 As System.Data.SqlClient.SqlCommand
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Label1 = New System.Windows.Forms.Label
        Me.ComboBox1 = New System.Windows.Forms.ComboBox
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.DsAgency1 = New SqlParametersDB.DSAgency
        Me.Label2 = New System.Windows.Forms.Label
        Me.Label3 = New System.Windows.Forms.Label
        Me.ListBox1 = New System.Windows.Forms.ListBox
        Me.SqlDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter
        Me.DspaReferal1 = New SqlParametersDB.DSPAReferal
        Me.SqlDataAdapter3 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlSelectCommand3 = New System.Data.SqlClient.SqlCommand
        Me.DspaExistingRef1 = New SqlParametersDB.DSPAExistingRef
        Me.SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand2 = New System.Data.SqlClient.SqlCommand
        CType(Me.DsAgency1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.DspaReferal1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.DspaExistingRef1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'Label1
        '
        Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 10.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label1.Location = New System.Drawing.Point(8, 8)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(336, 16)
        Me.Label1.TabIndex = 5
        Me.Label1.Text = "Description"
        '
        'ComboBox1
        '
        Me.ComboBox1.Location = New System.Drawing.Point(8, 48)
        Me.ComboBox1.Name = "ComboBox1"
        Me.ComboBox1.Size = New System.Drawing.Size(336, 21)
        Me.ComboBox1.TabIndex = 4
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=JUSTINECLEARY;packet size=4096;user id=sa;data source=""BC-BIZTALK""" & _
        ";persist security info=True;initial catalog=HealthDistrict;password=bristleconef" & _
        "ox"
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblLkpAgency", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("strAgency", "strAgency"), New System.Data.Common.DataColumnMapping("lngAgencynum", "lngAgencynum")})})
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO tblLkpAgency(strAgency, lngAgencynum) VALUES (@strAgency, @lngAgencyn" & _
        "um); SELECT strAgency, lngAgencynum FROM tblLkpAgency"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@strAgency", System.Data.SqlDbType.NVarChar, 50, "strAgency"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lngAgencynum", System.Data.SqlDbType.Int, 4, "lngAgencynum"))
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT strAgency, lngAgencynum FROM tblLkpAgency"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'DsAgency1
        '
        Me.DsAgency1.DataSetName = "DSAgency"
        Me.DsAgency1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'Label2
        '
        Me.Label2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label2.Location = New System.Drawing.Point(8, 32)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(136, 16)
        Me.Label2.TabIndex = 8
        Me.Label2.Text = "Agency Filter"
        '
        'Label3
        '
        Me.Label3.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label3.Location = New System.Drawing.Point(8, 80)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(200, 16)
        Me.Label3.TabIndex = 9
        Me.Label3.Text = "Select Agencies for Referral"
        '
        'ListBox1
        '
        Me.ListBox1.Location = New System.Drawing.Point(8, 96)
        Me.ListBox1.Name = "ListBox1"
        Me.ListBox1.SelectionMode = System.Windows.Forms.SelectionMode.MultiExtended
        Me.ListBox1.Size = New System.Drawing.Size(336, 160)
        Me.ListBox1.TabIndex = 10
        '
        'SqlDataAdapter2
        '
        Me.SqlDataAdapter2.InsertCommand = Me.SqlInsertCommand2
        Me.SqlDataAdapter2.SelectCommand = Me.SqlSelectCommand2
        Me.SqlDataAdapter2.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblContactType", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("lngContactType", "lngContactType"), New System.Data.Common.DataColumnMapping("strTypeDesc", "strTypeDesc")})})
        '
        'DspaReferal1
        '
        Me.DspaReferal1.DataSetName = "DSPAReferal"
        Me.DspaReferal1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'SqlDataAdapter3
        '
        Me.SqlDataAdapter3.SelectCommand = Me.SqlSelectCommand3
        Me.SqlDataAdapter3.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblPARefer", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("strTypeDesc", "strTypeDesc"), New System.Data.Common.DataColumnMapping("lngContactType", "lngContactType"), New System.Data.Common.DataColumnMapping("lngVisitID", "lngVisitID"), New System.Data.Common.DataColumnMapping("lngReferID", "lngReferID"), New System.Data.Common.DataColumnMapping("lngid", "lngid")})})
        '
        'SqlSelectCommand3
        '
        Me.SqlSelectCommand3.CommandText = "SELECT tblContactType.strTypeDesc, tblContactType.lngContactType, tblPARefer.lngV" & _
        "isitID, tblPARefer.lngReferID, tblPARefer.lngid FROM tblPARefer INNER JOIN tblCo" & _
        "ntactType ON tblPARefer.lngReferID = tblContactType.lngContactType WHERE (tblPAR" & _
        "efer.lngVisitID = @paramid)"
        Me.SqlSelectCommand3.Connection = Me.SqlConnection1
        Me.SqlSelectCommand3.Parameters.Add(New System.Data.SqlClient.SqlParameter("@paramid", System.Data.SqlDbType.Int, 4, "lngVisitID"))
        '
        'DspaExistingRef1
        '
        Me.DspaExistingRef1.DataSetName = "DSPAExistingRef"
        Me.DspaExistingRef1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'SqlSelectCommand2
        '
        Me.SqlSelectCommand2.CommandText = "SELECT lngContactType, RTRIM(strTypeDesc) AS strTypeDesc FROM tblContactType WHER" & _
        "E (bytFlag = 0) ORDER BY strTypeDesc"
        Me.SqlSelectCommand2.Connection = Me.SqlConnection1
        '
        'SqlInsertCommand2
        '
        Me.SqlInsertCommand2.CommandText = "INSERT INTO tblContactType(lngContactType) VALUES (@lngContactType); SELECT lngCo" & _
        "ntactType, RTRIM(strTypeDesc) AS strTypeDesc FROM tblContactType WHERE (lngConta" & _
        "ctType = @lngContactType) ORDER BY strTypeDesc"
        Me.SqlInsertCommand2.Connection = Me.SqlConnection1
        Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lngContactType", System.Data.SqlDbType.Int, 4, "lngContactType"))
        '
        'frmagency
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(352, 262)
        Me.Controls.Add(Me.ListBox1)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.ComboBox1)
        Me.Name = "frmagency"
        Me.Text = "Agency"
        CType(Me.DsAgency1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DspaReferal1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DspaExistingRef1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
 
    End Sub
 
#End Region
 
    Private Sub frmagency_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If newvisit = False Then
            'fill the listbox and then highlight ones that have been choosen 
            DspaReferal1.Clear()
            Try
                countprogram = (SqlDataAdapter2.Fill(DspaReferal1))
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            ListBox1.DataSource = DspaReferal1.Tables("tblContactType")
            ListBox1.DisplayMember = "strTypeDesc"
            ListBox1.ValueMember = "lngContactType"
            DspaExistingRef1.Clear()
            Dim refcount As Integer
            SqlDataAdapter3.SelectCommand.Parameters("@paramid").Value = visitid
            Try
                refcount = SqlDataAdapter3.Fill(DspaExistingRef1)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            'highlight rows of listbox
            Dim j As Integer
            
            If DspaExistingRef1.Tables(0).Rows.Count > 0 Then
           
                ''loop through each row
                For j = 0 To DspaExistingRef1.Tables(0).Rows.Count - 1
                    Dim refer As String
                    ''loop through each column
                    ' For refcount = 0 To DspaExistingRef1.Tables(0).Columns.Count - 1
                    refer = DspaExistingRef1.Tables(0).Rows(j).Item("strTypeDesc")
                    RTrim(refer)
                    ListBox1.SetSelected(refcount, True)
 
 
                    ' ListBox1.SelectedValue = refer
                    '  Next
                Next
            End If
 
 
 
 
 
        Else
 
                DspaReferal1.Clear()
                Try
                    countprogram = (SqlDataAdapter2.Fill(DspaReferal1))
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
                ListBox1.DataSource = DspaReferal1.Tables("tblContactType")
                ListBox1.DisplayMember = "strTypeDesc"
                ListBox1.ValueMember = "lngContactType"
 
                DsAgency1.Clear()
 
                Try
                    countprogram = (SqlDataAdapter1.Fill(DsAgency1))
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
                ComboBox1.DataSource = DsAgency1.Tables("tbllkpAgency")
                ComboBox1.DisplayMember = "strAgency"
                ComboBox1.ValueMember = "lngAgencynum"
        End If
    End Sub
 
    Private Sub ComboBox1_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.DropDown
        Dim countprogram
        DsAgency1.Clear()
        Try
            countprogram = (SqlDataAdapter1.Fill(DsAgency1))
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ComboBox1.DataSource = DsAgency1.Tables("tbllkpAgency")
        ComboBox1.DisplayMember = "strAgency"
        ComboBox1.ValueMember = "lngAgencynum"
    End Sub
 
    Private Sub frmagency_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Leave
        If newvisit = True Then
            Dim table As DataTable = Me.ListBox1.DataSource
 
            Dim newTable As DataTable
            newTable = table.Clone()
 
            'Add each selected item to the newTable
            For Each row As DataRowView In Me.ListBox1.SelectedItems
 
                newTable.Rows.Add(row.Row.ItemArray())
 
            Next
            'write out from datatable
            Dim cmd As New SqlCommand
            Dim newrow As DataRow
            With cmd
                .Connection = Connection1
                .CommandText = "INSERT INTO tblPArefer (lngvisitid, lngreferid) VALUES (@cola, @colb)"
                With .Parameters
                    .Add("@cola", SqlDbType.NVarChar)  'typesize is optional for fixed size datatypes like integer, datetime, etc.
                    .Add("@colb", SqlDbType.NVarChar)
 
                End With
            End With
 
            For Each newrow In newTable.Rows
                cmd.Parameters("@cola").Value = visitid
                cmd.Parameters("@colb").Value = newrow("lngContactType")
 
                Try
                    cmd.Connection.Open()
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MessageBox.Show("unable to insert record: ")
                Finally
                    If cmd.Connection.State = ConnectionState.Open Then
                        cmd.Connection.Close()
                    End If
                End Try
        Next
        Else
            newvisit = True
        End If
    End Sub
End Class