[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Checked ListBox - Saving to databse

Posted on 2004-11-24
35
Medium Priority
?
478 Views
Last Modified: 2011-08-18
I'm using a checked listbox that contains various ethnic groups like Asian, American, European, etc.

Users are able to select multiple ethnic groups and I'm wanting to save it into a database table with two fields (ClientNumber, EthnicGroupIDAuto). ClientNumber is the unique identifier for the client while EthnicGroupIAuto is the unique identifier for each Ethnic Group.

I'm loading the options with the following:

    Private Function LoadEthnicGroups() As Boolean

        Dim Index As Integer
        Dim cmd As SqlCommand = cnn.CreateCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "procLoadEthnicGroupOptions"

        Dim reader As SqlDataReader = cmd.ExecuteReader

        With reader
            While reader.Read
                ListBox_EthnicGroup.Items.Add(.GetValue(.GetOrdinal("LookUp_EthnicGroupIDAuto")))
                ListBox_EthnicGroup.Items.Item(Index) = .GetValue(.GetOrdinal("EthnicGroup"))
                Index = Index + 1
            End While
        End With

        reader.Close()

        LoadEthnicGroups = True

    End Function


CREATE PROCEDURE procLoadEthnicGroupOptions
AS
      select LookUp_EthnicGroupIDAuto, EthnicGroup
      from LookUp_EthnicGroup
      where DisableThisRecord = 0
      order by EthnicGroup asc
GO


How could I go about saving the data to the database table?
0
Comment
Question by:PeterErhard
  • 17
  • 9
  • 6
  • +2
35 Comments
 
LVL 9

Accepted Solution

by:
Lacutah earned 1500 total points
ID: 12669938
First of all, here's a fix for your checked list box population, replace what you have (starting at "Dim reader As SqlDataReader = cmd.ExecuteReader") with the following:

        Dim da As New Data.SqlClient.SqlDataAdapter(cmd)
        Dim dt As DataTable
        da.Fill(dt)
        with  ListBox_EthnicGroup
            .BeginUpdate()
            .DataSource = dt
            .DisplayMember = "EthnicGroup" 'I'm assuming this is the display name.
            .ValueMember = "LookUp_EthnicGroupIDAuto" 'I'm assuming this is the ID field
            .EndUpdate()
        End With

To read the checked items back into the database, use the following:

        Dim cmdInsert As New Data.SqlClient.SqlCommand("Insert into TableName (ClientNumber, EthnicGroupIDAuto) VALUES (@Client, @EthnicGroupID)", cnn)
        cmdInsert.Parameters.Add("@Client", ClientIDNumber)
        cmdInsert.Parameters.Add("@EthnicGroupID", SqlDbType.Int)

        Dim i As Integer

        For Each i In ListBox_EthnicGroup.CheckedIndices()
            cmdInsert.Parameters("@EthnicGroupID").Value = CType(clb.Items(i), DataRowView)("LookUp_EthnicGroupIDAuto")
            cmdInsert.ExecuteNonQuery()
        Next

0
 

Author Comment

by:PeterErhard
ID: 12670546
Can you explain this line please:

cmdInsert.Parameters("@EthnicGroupID").Value = CType(clb.Items(i), DataRowView)("LookUp_EthnicGroupIDAuto")

What's clb.Items(i)?
0
 

Author Comment

by:PeterErhard
ID: 12671038
Also, the below gives me the follownig error "Value cannot be null. Parameter name =datatable"

    Private Function LoadEthnicGroups() As Boolean

        Dim Index As Integer
        Dim cmd As SqlCommand = cnn.CreateCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "procLoadEthnicGroupOptions"
        'Dim reader As SqlDataReader = cmd.ExecuteReader

        Dim da As New Data.SqlClient.SqlDataAdapter(cmd)
        Dim dt As DataTable
        da.Fill(dt)
        With ListBox_EthnicGroup
            .BeginUpdate()
            .DataSource = dt
            .DisplayMember = "EthnicGroup" 'I'm assuming this is the display name.
            .ValueMember = "LookUp_EthnicGroupIDAuto" 'I'm assuming this is the ID field
            .EndUpdate()
        End With

        LoadEthnicGroups = True

    End Function
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:PeterErhard
ID: 12671454
Can someone please help me with this?
0
 
LVL 4

Expert Comment

by:vinhthuy_nguyen
ID: 12671661
Hi,
I have a question :
Do you mean that you want to save data to Table(ClientNumber, EthnicGroupIDAuto) ? but how can it do with multiple checkbox, do you want to save with multiple records at once ?
0
 

Author Comment

by:PeterErhard
ID: 12671711
A further explanation:

The following table called EthnicGroup:

EthnicGroupIDAuto                      EthnicGroup
1                                      European
3                                      Asian
5                                      Pacific Peoples
9                                      African
10                                      American - North/South
12                                      European
13                                      Middle Eastern

needs to go into the CheckedListBox control.

So if European and Asian were selected it would save the following into the ClientNumber_EthnicGroupIDAuto table:

ClientNumber      EthnicGroupIDAuto
1583                      1
1583                      3

Once the record is loaded again it needs to tick European and Asian automatically.
0
 
LVL 1

Expert Comment

by:ddetering
ID: 12671784
Peter,

Your error with the dataTable attempt could be caused because you cannot declare a DataTable but a DataSet. The DataTable will be created with the Fill method of the DataAdapter, like here:

            Dim cn1 As String
            Dim dadGeocodes As SqlDataAdapter
            cn1 = ConfigurationSettings.AppSettings("cn1")
            Dim cn As SqlConnection = New SqlConnection(cn1)
            Dim strselect As String
            strselect = "select geo_id, cou_id, code, long, lat from country_postalcode where type = 'S'"
            dadGeocodes = New SqlDataAdapter(strselect, cn)
            dstGeocodes = New DataSet
            dadGeocodes.Fill(dstGeocodes, "geocodes")

You can then bind your ListBox to it, like this: (next posting to come)
0
 
LVL 1

Expert Comment

by:ddetering
ID: 12671807
         With ListBox_EthnicGroup
                .DataSource = yourDataset 'this will pull your table because it is the only one - default behavior
                .DataTextField = "value to see" whatever you call the ethic group's column in the query feeding the dataadapater
                .DataValueField = "val_id" the name of the ID column in the query feeding the dataadapter
                .DataBind()
            End With

Now, your users can select their values and submit the page. We'll see next how to go about writing the results to the DB
0
 
LVL 4

Expert Comment

by:vinhthuy_nguyen
ID: 12671811
OK, Let's try with:

        Dim i As checkbox
        DIm mysqlcommand as SQLCommand
        For Each i In ListBox_EthnicGroup
           i = listbox_EthnicGroup.Item
           If i.checked = true then
mysqlcommand = new SQLCommand("Insert into ClientNumber_EthnicGroupIDAuto(clientnumber,EthnicGroupIDAuto) values('" + clientnumber + "','" + i.SelectedValue +"'),MyConnection)"
            MysqlCommand.ExecuteNonQuery()
        Next
Pls fit it to your applcation.
Regards.

 
0
 
LVL 1

Expert Comment

by:ddetering
ID: 12671829
When submitting, you will loop through each of the items of your listbox and see whether it is selected:

Dim ctr as Integer
Dim strDeleteInsert As String = [you might want to begin with a delete command that deletes all values in the table of that user so that there are no duplicates]

            For ctr = 0 To ListBox_EthnicGroup.Items.Count - 1 'the last index number is the number of items - 1
                If ListBox_EthnicGroup.Items(ctr).Selected Then
                    strDeleteInsert += "insert [you sql to write to the DB plus the value of the selected ethnic group:]ListBox_EthnicGroup.Items(ctr).Value & "'); "
                End If
            Next

Finally run the complete statement against the DB:

            strDeleteInsert = "Begin Transaction " & DeleteCats & "; " & strDeleteInsert & " Commit Transaction"
            cmdDeleteInsert = New SqlCommand(strDeleteInsert, cn)
            cn.Open()
            cmdDeleteInsert.ExecuteNonQuery()
            cn.Close()
0
 
LVL 1

Expert Comment

by:ddetering
ID: 12671849
Loading the existing values as checked into the listbox for returning users is a little trickier. You could run a DataReader and listen to each value that comes from it. If you find the same value in the ListBox, mark it as selected. I don't have a code sample for it because I have pages with many different, but similar checkboxes etc and have decided it would be easier to load all values from the DB, build a string from it, and then loop through each item of the list controls on the page and see whether the item.value matches a substring in the old values string. Of course, this requires that all values (and correspondingly the IDs in the db) have the same lenght. Four digits were sufficient for me. Interested in the code?
0
 

Author Comment

by:PeterErhard
ID: 12671875
Thanks for all your responses. Starting to work through them. Having trouble with ddetering's first up suggestion however.

The below code gives me the following error: "Could not bind to the new display member. Parameter name: newDisplayName:

   Private Function LoadEthnicGroups() As Boolean

        Dim cmd As SqlCommand = cnn.CreateCommand
        Dim da As SqlDataAdapter = New SqlDataAdapter()
        Dim ds As DataSet = New DataSet()

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "procLoadEthnicGroupOptions"

        da.SelectCommand = cmd
        da.Fill(ds, "EthnicGroupSelection")

        With ListBox_EthnicGroup
            .DataSource = ds
            .DisplayMember = "EthnicGroup"
            .ValueMember = "LookUp_EthnicGroupIDAuto"
        End With

        LoadEthnicGroups = True

    End Function
0
 
LVL 1

Expert Comment

by:ddetering
ID: 12671933
Hm, I don't know. But I also don't know what .DisplayMember is - are we talking a .net listbox here? I have instead DataTextField (and DataValueField instead of .valuemember), and you are also missing th .Bind() at the end...
0
 

Author Comment

by:PeterErhard
ID: 12672077
Yes we're talking a .Net CheckedListBox Control in a Windows application

DataTextField, DataValueField and Bind() don't seem to exist.

Can someone help re the above error please?

0
 
LVL 1

Expert Comment

by:ddetering
ID: 12672090
aah... Windows vs. Web. It is confusing that Experts-Exchange does not keep them separate. I doubt I'll be of much help then. Good luck!
0
 

Author Comment

by:PeterErhard
ID: 12672127
Thanks - hopefully someone can come on and help *fingers and toes crossed*
0
 
LVL 6

Expert Comment

by:platinumbay
ID: 12672400
   Windows                Web
DisplayMember = DataTextField
ValueMember = DataValueField

And there is no .Bind on the Windows side.

Try using da.tables(0) for the datasource.

I'm going to try to replicate your project, and get you some source code.  You have the two tables of interest here, right?
0
 
LVL 6

Expert Comment

by:platinumbay
ID: 12672474
So far this worked fine for loading the Listbox:

    Private Function LoadEthnicGroups() As Boolean
        Dim cnn As New SqlConnection("Server=(local);Database=EthnicGroups;Trusted_Connection=True;")
        Dim cmd As SqlCommand = cnn.CreateCommand
        Dim da As SqlDataAdapter = New SqlDataAdapter
        Dim ds As DataSet = New DataSet

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "procLoadEthnicGroupOptions"

        da.SelectCommand = cmd
        da.Fill(ds, "EthnicGroupSelection")

        With ListBox_EthnicGroup
            .DisplayMember = "EthnicGroup"
            .ValueMember = "LookUp_EthnicGroupIDAuto"
            .DataSource = ds.Tables(0)
        End With

        LoadEthnicGroups = True
    End Function
0
 
LVL 6

Expert Comment

by:platinumbay
ID: 12672505
I am having some 'technical difficulties with my database, but the following should work to add the new record after a selection is made:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim clientID As Integer = 1 ' Or get your client ID
        Dim selID As Integer = Convert.ToInt32(CType(Me.ListBox_EthnicGroup.SelectedValue, DataRowView).Item(0).ToString)
        Dim cmd As SqlCommand = cnn.CreateCommand
        If cnn.State <> ConnectionState.Open Then cnn.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "INSERT INTO ClientNumber_EthnicGroupIDAuto (ClientNumber, EthnicGroupIDAuto) VALUES (" & clientID & "," & selID & ")"
        Dim result As Integer = CInt(cmd.ExecuteNonQuery)
        If cnn.State <> ConnectionState.Closed Then cnn.Close()
        MsgBox(result & " rows updated.")
    End Sub
0
 
LVL 6

Expert Comment

by:platinumbay
ID: 12672512
Ah, change the CommandType to Text.
0
 
LVL 6

Expert Comment

by:platinumbay
ID: 12672518
To pull it all together here is my complete code:

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form

#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 ListBox_EthnicGroup As System.Windows.Forms.ListBox
    Friend WithEvents Button1 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.ListBox_EthnicGroup = New System.Windows.Forms.ListBox
        Me.Button1 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'ListBox_EthnicGroup
        '
        Me.ListBox_EthnicGroup.Location = New System.Drawing.Point(72, 48)
        Me.ListBox_EthnicGroup.Name = "ListBox_EthnicGroup"
        Me.ListBox_EthnicGroup.Size = New System.Drawing.Size(120, 95)
        Me.ListBox_EthnicGroup.TabIndex = 0
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(120, 176)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 1
        Me.Button1.Text = " submit "
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 273)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.ListBox_EthnicGroup)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Dim cnn As New SqlConnection("Server=(local);Database=EthnicGroups;Trusted_Connection=True;")

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

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim clientID As Integer = 1
        Dim selID As Integer = Convert.ToInt32(CType(Me.ListBox_EthnicGroup.SelectedValue, DataRowView).Item(0).ToString)
        Dim cmd As SqlCommand = cnn.CreateCommand
        If cnn.State <> ConnectionState.Open Then cnn.Open()
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "INSERT INTO ClientNumber_EthnicGroupIDAuto (ClientNumber, EthnicGroupIDAuto) VALUES (" & clientID & "," & selID & ")"
        Dim result As Integer = CInt(cmd.ExecuteNonQuery)
        If cnn.State <> ConnectionState.Closed Then cnn.Close()
        MsgBox(result & " rows updated.")
    End Sub

    Private Function LoadEthnicGroups() As Boolean
        Dim cmd As SqlCommand = cnn.CreateCommand
        Dim da As SqlDataAdapter = New SqlDataAdapter
        Dim ds As DataSet = New DataSet

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "procLoadEthnicGroupOptions"

        da.SelectCommand = cmd
        da.Fill(ds, "EthnicGroupSelection")

        With ListBox_EthnicGroup
            .DisplayMember = "EthnicGroup"
            .ValueMember = "LookUp_EthnicGroupIDAuto"
            .DataSource = ds.Tables(0)
        End With

        LoadEthnicGroups = True
    End Function
End Class
0
 

Author Comment

by:PeterErhard
ID: 12672535
Thanks for your comment.

Yes there's two tables.

First Table - EthnicGroup (Description - populates the CheckedListBox control)

LookUp_EthnicGroupIDAuto      EthnicGroup
1                                      European
3                                      Asian
5                                      Pacific Peoples
9                                      African
10                                      American - North/South
12                                      European
13                                      Middle Eastern

Second Table - ClientNumber_EthnicGroupIDAuto (Description - populates with the ClientNumber and the EthnicGroupIDAuto's that were clicked from the CheckedListBox)

ClientNumber      EthnicGroupIDAuto
1583                      1
1583                      3

The above is obviously for client 1583 and the user clicked  European and Asian within the CheckedListBox control.
0
 
LVL 6

Expert Comment

by:platinumbay
ID: 12672552
Correction:  I made the following change to support multiple-select.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim clientID As Integer = 1
        Dim selItem As Integer
        Dim cmd As SqlCommand = cnn.CreateCommand
        If cnn.State <> ConnectionState.Open Then cnn.Open()
        cmd.CommandType = CommandType.Text
        Dim result As Integer
        For Each lvi As DataRowView In Me.ListBox_EthnicGroup.SelectedItems
            selItem = CInt(lvi.Item(0))
            'selItem = CInt(soc.Item(0))
            cmd.CommandText = "INSERT INTO ClientNumber_EthnicGroupIDAuto (ClientNumber, EthnicGroupIDAuto) VALUES (" & clientID & "," & selItem & ")"
            result += CInt(cmd.ExecuteNonQuery)
        Next

        If cnn.State <> ConnectionState.Closed Then cnn.Close()
        MsgBox(result & " rows updated.")
    End Sub
0
 

Author Comment

by:PeterErhard
ID: 12672605
Thanks very much for that!

Are you able to include the loading of the data back into the CheckedListBox? i.e.  this data:

ClientNumber     EthnicGroupIDAuto
1583                     1
1583                     3

when loaded would check European and Asian.
0
 
LVL 6

Expert Comment

by:platinumbay
ID: 12672642
You would have to pull that data as well from the database, and then use something similar to:

http://forums.devshed.com/archive/t-198200
0
 

Author Comment

by:PeterErhard
ID: 12672729
Thanks very much - will give all of these options a go :)
0
 

Author Comment

by:PeterErhard
ID: 12677024
This doesn't work as lvi is undefined. What is lvi and where does the DataRowView come in?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim clientID As Integer = 1
        Dim selItem As Integer
        Dim cmd As SqlCommand = cnn.CreateCommand
        If cnn.State <> ConnectionState.Open Then cnn.Open()
        cmd.CommandType = CommandType.Text
        Dim result As Integer
        For Each lvi As DataRowView In Me.ListBox_EthnicGroup.SelectedItems
            selItem = CInt(lvi.Item(0))
            'selItem = CInt(soc.Item(0))
            cmd.CommandText = "INSERT INTO ClientNumber_EthnicGroupIDAuto (ClientNumber, EthnicGroupIDAuto) VALUES (" & clientID & "," & selItem & ")"
            result += CInt(cmd.ExecuteNonQuery)
        Next

        If cnn.State <> ConnectionState.Closed Then cnn.Close()
        MsgBox(result & " rows updated.")
    End Sub
0
 

Author Comment

by:PeterErhard
ID: 12677383
Anyone?
0
 
LVL 6

Expert Comment

by:platinumbay
ID: 12678340
The statement:

For Each lvi As DataRowView In Me.ListBox_EthnicGroup.SelectedItems

defines lvi as a DataRowView.  I am reading through the selected options.

You can replace that line with:

Dim lvi as DataRowView
For Each lvi In Me.ListBox_EthnicGroup.SelectedItems
0
 

Author Comment

by:PeterErhard
ID: 12678418
Thanks for responding.

Very close now. It's saving one of the values into the database but not all, i.e. if I click three checkboxes in the CheckedListBox Control it only saves one.

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim clientID As Integer = 1
            Dim selItem As Integer
            Dim cmd As SqlCommand = cnn.CreateCommand
            If cnn.State <> ConnectionState.Open Then cnn.Open()
            cmd.CommandType = CommandType.Text
            Dim lvi As DataRowView
            Dim result As Integer
            For Each lvi In Me.ListBox_EthnicGroup.SelectedItems
                selItem = CInt(lvi.Item(0))
                'selItem = CInt(soc.Item(0))
                cmd.CommandText = "INSERT INTO ClientIDAuto_EthnicGroupIDAuto (ClientIDAuto, EthnicGroupIDAuto) VALUES (" & clientID & "," & selItem & ")"
                cmd.ExecuteNonQuery()
            Next

            If cnn.State <> ConnectionState.Closed Then cnn.Close()
            MsgBox(result & " rows updated.")
        Catch ex As Exception
            MessageBox.Show(Err.Description)
        End Try

    End Sub
0
 
LVL 6

Expert Comment

by:platinumbay
ID: 12678434
Weird,  it worked great for me.

Set a breakpoint on the For Each line, and debug (F5).  When it stops, continue with F11 to see how it moves through the method.  Also, take a look at the Me.ListBox_EthnicGroup.SelectedItems property and see how many items it has.

Let me know.
0
 

Author Comment

by:PeterErhard
ID: 12678443
Hmm - this is what I get everytime no matter how many items I click in the control so it's only going through the For loop once.

Ideas?

?ListBox_EthnicGroup.SelectedItems
{System.Windows.Forms.ListBox.SelectedObjectCollection}
    Object: {System.Windows.Forms.ListBox.SelectedObjectCollection}
    SelectedObjectMask: 1
    owner: {System.Windows.Forms.CheckedListBox}
    stateDirty: False
    lastVersion: -1
    count: 0
    Count: 1
    InnerArray: {System.Windows.Forms.ListBox.ItemArray}
    IsReadOnly: True
    Item: <cannot view indexed property>


?ListBox_EthnicGroup.SelectedItems.count
1
0
 

Author Comment

by:PeterErhard
ID: 12678683
Ok - gone back through the thread and re-tried Lacutah's code and it works!

Code is below.
 
OK - just the loading to get working now!


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            cnn.Open()

            Dim cmdInsert As New Data.SqlClient.SqlCommand("Insert into ClientIDAuto_EthnicGroupIDAuto (ClientIDAuto, EthnicGroupIDAuto) Values (@Client, @EthnicGroupID)", cnn)
            cmdInsert.Parameters.Add("@Client", 1)
            cmdInsert.Parameters.Add("@EthnicGroupID", SqlDbType.Int)

            Dim i As Integer

            For Each i In ListBox_EthnicGroup.CheckedIndices
                cmdInsert.Parameters("@EthnicGroupID").Value = CType(ListBox_EthnicGroup.Items(i), DataRowView)("LookUp_EthnicGroupIDAuto")
                cmdInsert.ExecuteNonQuery()
            Next
        Catch ex As Exception
            MessageBox.Show(Err.Description)
        Finally
            cnn.Close()
        End Try
0
 

Author Comment

by:PeterErhard
ID: 12678711
Shouldn't be too bad with the loading I hope. This was the code I used in VB6 (below) - going to give it a go on Monday so will keep the question open until then in case I run into problems.

Private Function LoadEthnicGroup(ClientNumber As Long) As Boolean
On Error GoTo errorlabel
    Dim ForLoop As Integer, EthnicGroupIDAuto As Integer, Query As String
    Dim rstClientIDAuto_EthnicGroupIDAuto As ADODB.Recordset
       
    Set rstClientIDAuto_EthnicGroupIDAuto = New ADODB.Recordset
    Query = "Select ClientIDAuto, EthnicGroupIDAuto from ClientIDAuto_EthnicGroupIDAuto " & _
        "Where ClientIDAuto = " & ClientNumber
   
    Set rstClientIDAuto_EthnicGroupIDAuto = g_DatabaseConnection.Execute(Query)
   
    With rstClientIDAuto_EthnicGroupIDAuto
        While .EOF = False
            EthnicGroupIDAuto = .Fields("EthnicGroupIDAuto")
            For ForLoop = 0 To ListBox_EthnicGroup.ListCount - 1
                If ListBox_EthnicGroup.ItemData(ForLoop) = EthnicGroupIDAuto Then
                    ListBox_EthnicGroup.ItemChecked(ForLoop) = True
                    Exit For
                End If
            Next
            .MoveNext
        Wend
        .Close
    End With
    LoadEthnicGroupOfClient = True
Exit Function
errorlabel:
    MsgBox "Loading ethnic group selection failed", vbOKOnly + vbCritical, m_MsgBoxHeader
End Function
0
 

Author Comment

by:PeterErhard
ID: 12691644
I've got the loading working with the following code. Does anyone see any problems with the way I've done it?

    Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim EthnicGroupCount As Integer, EthnicGroupIDAuto As Integer
        Dim ItemIndex As Object, Count As Integer

        Try
            'Get Number of Ethnic Group Items in ListBox
            EthnicGroupCount = ListBox_EthnicGroup.Items.Count()

            'Check the status of the connection
            CheckConnectionStatus()

            Dim cmd As SqlCommand = cnn.CreateCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "procLoadClientEthnicGroupOptions"

            cmd.Parameters.Add(New SqlParameter("@ClientIDAuto", SqlDbType.Int, 4))
            cmd.Parameters("@ClientIDAuto").Value = 1

            Dim dr As SqlDataReader = cmd.ExecuteReader

            While dr.Read
                With dr
                    EthnicGroupIDAuto = CInt(.GetValue(.GetOrdinal("EthnicGroupIDAuto")))

                    'Find out the Index for the EthnicGroupIDAuto Saved
                    'And check the item
                    For Count = 0 To EthnicGroupCount - 1
                        ItemIndex = CType(ListBox_EthnicGroup.Items(Count), DataRowView)("LookUp_EthnicGroupIDAuto")
                        If CInt(EthnicGroupIDAuto) = CInt(ItemIndex) Then
                            ListBox_EthnicGroup.SetItemChecked(Count, True)
                        End If
                    Next
                End With
            End While
            dr.Close()

        Catch ex As Exception
            MessageBox.Show(Err.Description)
        Finally
            cnn.Close()
        End Try
    End Sub
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

872 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