How to generate each item(s) checked from checkedlistbox with ranges (from start date to end date) and insert into table?

Question

0.00/5 (No votes)

See more:

current code only insert one checked item from checkedlistbox plus ranges of (start date to end date) although i had checked multiples item(s). i want it to be if i checked 5 items therefore inside my table should contain 5 checked item(s) plus the ranges of dates

e.g.:

donald|01.01.14
donald|01.02.14
donald|01.03.14

helloword|01.01.14
helloword|01.02.14
helloword|01.03.14

Wong|01.01.14
Wong|01.02.14
Wong|01.03.14

Codes:

VB

Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & aaa & "';Persist Security Info=False;")
Dim stard As DateTime = DateTimePicker1.Value
Dim endd As DateTime = DateTimePicker2.Value

Dim itemchecked As Object
For Each itemchecked In CheckedListBox1.CheckedItems
    Do While stard <= endd
        Dim insertinto As String = "INSERT INTO try ([SN],[Dateplease]) VALUES (@SN, @Dateplease)"
        Dim cmd As New OleDbCommand(insertinto, connection)
        cmd.Parameters.AddWithValue("@SN", itemchecked.ToString)
        cmd.Parameters.AddWithValue("@Dateplease", stard)
        If connection.State = ConnectionState.Closed Then
            connection.Open()
        End If
        cmd.ExecuteNonQuery()
        stard = stard.AddDays(1)
    Loop
Next
connection.Close()

Posted 19-Jun-14 22:34pm

donaldliaw87

Updated 19-Jun-14 22:41pm

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kschuler · Accepted Answer · 2014-06-20T05:31:00

Solution 1

Where is this code being called from? Have you debugged? Is it finding all the items that you have checked (in other words, is it looping more than once in the For Each loop)? If so, walk through it and watch what is happening with the stard and endd variables. They don't appear to ever get reset per checked item, so I'd guess that the second checked item already has a stard<=endd and doesn't do any thing, and so on.

Posted 20-Jun-14 5:31am

Kschuler

Comments

donaldliaw87 22-Jun-14 21:11pm

hi Kschuler! i'm a little bit understand what you explained, so the first thing i have to do is to loop particular column's rows instead of for each loop) and [after first checkeditem been loop, i have to wrote something to allow it loop trough the next checkeditem]. Please guide me am i in right-path!?

donaldliaw87 · Accepted Answer · 2014-06-22T20:00:00

Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & aaa & "';Persist Security Info=False;")
        Dim originalstard As DateTime = DateTimePicker1.Value
        Dim stard As DateTime = DateTimePicker1.Value
        Dim endd As DateTime = DateTimePicker2.Value

        'Dim itemchecked As Object
        'For Each itemchecked In CheckedListBox1.CheckedItems
        Dim count = CheckedListBox1.CheckedItems.Count
        'CheckedListBox1.Items.Clear()
        For i As Integer = 0 To (count - 1)
            Dim checked = CheckedListBox1.CheckedItems.Item(i)
            Do While stard <= endd
                Dim insertinto As String = "INSERT INTO try ([SN],[Dateplease]) VALUES (@SN, @Dateplease)"
                Dim cmd As New OleDbCommand(insertinto, connection)
                cmd.Parameters.AddWithValue("@SN", checked)
                cmd.Parameters.AddWithValue("@Dateplease", stard)
                If connection.State = ConnectionState.Closed Then
                    connection.Open()
                End If
                cmd.ExecuteNonQuery()
                stard = stard.AddDays(1)
            Loop
            stard = originalstard
            'Next
        Next
        connection.Close()