• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

How to populate SQL data into checked list box then write to file?

hello experts,
i have found many solutions of people asking how to extract data from a checked list box but i have been unsuccessful finding code to write data into a checked list box. ive found it is as simple as drag and drop in studio 2008 for a list box but not a checked list box? i need to write all of the records (inv_num) from table (fedexinvoices) in database (shipping) on server (tristar6). is there a trick to this to get all invoices to populate? then once the appropriate boxes are checked and a button is pressed the program will take whichever ones are checked and place them in a csv file. i know this is lenghty perhaps someone could just get me off on the right foot please? thanks very much!
0
psuscott
Asked:
psuscott
  • 5
  • 4
1 Solution
 
Fernando SotoCommented:
Hi psuscott;

Check out the code snippet and comments

Fernando
Imports System.Data.SqlClient
Imports System.Text
Imports System.IO
 
Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
 
        ' Change the connection string to meet your needs
        Dim cnn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=shipping;Integrated Security=True")
        ' Create a command object
        Dim cmd As SqlCommand = cnn.CreateCommand()
        ' Select the data
        cmd.CommandText = "Select * From fedexinvoices"
        cmd.CommandType = CommandType.Text
        ' Create a DataTable for the data returned
        Dim dt As New DataTable()
        ' Create a SqlDataAdapter with the command object
        Dim da = New SqlDataAdapter(cmd)
        ' Fill the data table
        da.Fill(dt)
        ' Bind the table to the CheckedListBox
        CheckedListBox1.DataSource = dt
        ' Tell it what field to display
        CheckedListBox1.DisplayMember = "inv_num"
 
    End Sub
 
 
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        ' Build the CSV string in a string builder
        Dim sb As New StringBuilder()
 
        ' Enumerate through the checked items
        For Each idx As Integer In CheckedListBox1.CheckedIndices
            ' Write the data to the string
            sb.AppendFormat("{0}, ", CheckedListBox1.GetItemText(CheckedListBox1.Items(idx)).Trim())
        Next
 
        ' Revove the last comma and space from the string
        If sb.Length > 0 Then
            sb.Length = sb.Length - 2
            Dim sw As New StreamWriter("Data.csv")
            sw.Write(sb)
            sw.Close()
        End If
 
    End Sub
 
End Class

Open in new window

0
 
psuscottAuthor Commented:
that is perfect that is a very thorough response thank you very much. there is one other question i have is there a way i can insert after the selected inv_num the inv_date that matches the inv_num? thank you again very much.
0
 
Fernando SotoCommented:
Hi psuscott;

Where? In the CheckedListBox control or in the CSV file? Where will the inv_date comming from the DB? If so is it the same table?

Fernando
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
psuscottAuthor Commented:
the inv_date is in the same table but i need to add it to the csv file not the checkedlistbox, the checkedlistbox will only ever show the inv_num and thats it. thanks again very much. also, one last request which i have found tricky, i also need to add a field to the csv file that says how many items were checked off, but this is separated by a * symbol not a comma, so the file would read *3* at the very beginning if there were 3 items checked off the checkedlistbox.
0
 
Fernando SotoCommented:
Hi psuscott;

Change the code as follows.

Fernando
' Build the CSV string in a string builder
Dim sb As New StringBuilder()
' Write the number ov invoices selected in the list box in the format of *#* 
' Where # is a numeric value
sb.Append("*" & CheckedListBox1.CheckedIndices.Count.ToString() & "*" & vbCrLf)
 
' Enumerate through the checked items
For Each idx As Integer In CheckedListBox1.CheckedIndices
    ' Get the date from the data row and convert to a short string date
    Dim inv_date As String = CDate(CType(CheckedListBox1.Items(idx), DataRowView).Row("inv_date")).ToShortDateString()
    ' Write the data to the string
    sb.AppendFormat("{0}, {1}, ", CheckedListBox1.GetItemText(CheckedListBox1.Items(idx)).Trim(), _
                    inv_date)
Next
 
' Revove the last comma and space from the string
If sb.Length > 0 Then
    sb.Length = sb.Length - 2
    Dim sw As New StreamWriter("Data.csv")
    sw.Write(sb)
    sw.Close()
End If

Open in new window

0
 
psuscottAuthor Commented:
perfect!!! thank you again so much. i just have one small request, the *2* appears on its own line and the other string appears below, i am trying for one continuous string, how could i fix this?

also, if i were to want to separate the invoices and their dates with a string *RMR|IV* would i be able to insert that in line 12 after the {1}, so that it woudl read:

2992183, 10/1/2008 *RMR|IV* 2921569, 10/2/2008

i will be sure to accept the first reply you made as the accepted solution as that completely answered my first question, i apologize i am taking your time to custom tailor this for me, you are very helpful.
0
 
Fernando SotoCommented:
Hi psuscott;

To place the *2* on the same line change this :

sb.Append("*" & CheckedListBox1.CheckedIndices.Count.ToString() & "*" & vbCrLf)

To This:

sb.Append("*" & CheckedListBox1.CheckedIndices.Count.ToString() & "*")

Note that I just removed the  & vbCrLf from the end. This will make the *2* displayed in the file as follows:

*2*2992183, 10/1/2008 *RMR|IV* 2921569, 10/2/2008

So if you want a space between the two change the second "*" to what you need such as "* " which will give:

*2* 2992183, 10/1/2008 *RMR|IV* 2921569, 10/2/2008

or this "*, " which will give this:

*2*, 2992183, 10/1/2008 *RMR|IV* 2921569, 10/2/2008

The other request does not look correct to me for the following reason, you have an invoice field then a comma then a date field then *RMR|IV* a space followed by a invoice field, no comma between *RMR|IV* and invoice field. Also you have *RMR|IV* folloowing the first date field but not the second.

To set the fields to what you need all you need to is modify this string format parameter, "{0}, {1}, ". The {0} is the invoice place holder and the {1} is the date place holder all other characters get inserted as is. So the following format parameter,  "{0}, {1} *RMR|IV*, " will result in the following:

*2*, 2992183, 10/1/2008 *RMR|IV*, 2921569, 10/2/2008 *RMR|IV*

Note that the way it is formatted in the loop will be invoice, invoice-date *RMR|IV*,

If you need anything other then that then in the loop you will need logic to format the string in a non repeating way. Either way you have how to access the correct values and it just a matter of formatting it the way you need.

Fernando
0
 
psuscottAuthor Commented:
very well explained solutions. you are a tremendous help to me. thank you so much.
0
 
Fernando SotoCommented:
Not a problem, glad to help.  ;=)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now