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

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.

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

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.

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

Select allOpen in new window

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.

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

very well explained solutions. you are a tremendous help to me. thank you so much.

Not a problem, glad to help.  ;=)