Data duplicates using Do While Loop in VB.NET

Question

I just wanted to ask why my data duplicates and how can I prevent it?

NOTE: my SQL query are working properly, the only problem is that every data it saves are duplicated based on the last value of ctr

Here is my code:

  For Each lvi As ListViewItem In lvReportFormat2.Items
            Dim count = lvReportFormat2.Items.Count
            Dim ctr = 0
            Dim orderby = 0

            label.Text = lvReportFormat2.Items(ctr).SubItems(4).Text

            Do While ctr < count
                Label1.Text = lvReportFormat2.Items(ctr).SubItems(4).Text
                Execute("INSERT INTO tblrptChad (accountcode,accountdesc,Type,class,Orderby,ReportType,Formula,Show)VALUES ('" & IIf(lvReportFormat2.Items(ctr).SubItems(0).Text IsNot DBNull.Value, lvReportFormat2.Items(ctr).SubItems(0).Text, "NULL") & "','" & IIf(lvReportFormat2.Items(ctr).SubItems(1).Text IsNot DBNull.Value, lvReportFormat2.Items(ctr).SubItems(1).Text, "NULL") & "','" & IIf(lvReportFormat2.Items(ctr).SubItems(2).Text IsNot DBNull.Value, lvReportFormat2.Items(ctr).SubItems(2).Text, "NULL") & "','" & IIf(lvReportFormat2.Items(ctr).SubItems(3).Text IsNot DBNull.Value, lvReportFormat2.Items(ctr).SubItems(3).Text, "NULL") & "','" & orderby & "','" & Val(IIf(lvReportFormat2.Items(ctr).SubItems(5).Text IsNot DBNull.Value, Val(lvReportFormat2.Items(ctr).SubItems(5).Text), 0)) & "','" & IIf(lvReportFormat2.Items(ctr).SubItems(6).Text IsNot DBNull.Value, lvReportFormat2.Items(ctr).SubItems(6).Text, "NULL") & "','" & Val(IIf(lvReportFormat2.Items(ctr).SubItems(7).Text IsNot DBNull.Value, Val(lvReportFormat2.Items(ctr).SubItems(7).Text), 0)) & "')")
                ctr = ctr + 1
                orderby = orderby + 1
            Loop


        Next

score 0 · Answer 1 · edited May 23 '17 at 11:57

You are inserting duplicates of your records because you loop two times on the Items collection of your ListView. A first time with the foreach lvi ... Next statement and a second time with the Do While loop

A full answer to your question would require to introduce you to parameterized queries, but this involves to change radically your Execute method.
So for the immediate answer

Dim orderby = 0
For Each lvi As ListViewItem In lvReportFormat2.Items

    Label1.Text = lvi.SubItems(4).Text
    Execute("INSERT INTO tblrptChad "& _
    "(accountcode,accountdesc,Type,class,Orderby," & _
    "ReportType,Formula,Show) VALUES " & _
    "('" & _
       If(lvi.SubItems(0).Text IsNot DBNull.Value, _ 
           lvi.SubItems(0).Text, "NULL") & "','" & _ 
       If(lvi.SubItems(1).Text IsNot DBNull.Value, _
           lvi.SubItems(1).Text, "NULL") & "','" & _
       If(lvi.SubItems(2).Text IsNot DBNull.Value, _
           lvi.SubItems(2).Text, "NULL") & "','" & _
       If(lvi.SubItems(3).Text IsNot DBNull.Value, _
           lvi.SubItems(3).Text, "NULL") & "','" & _
       orderby & "','" & _
       Val(If(lvi.SubItems(5).Text IsNot DBNull.Value, _
           Val(lvi.SubItems(5).Text), 0)) & "','" & _
       If(lvi.SubItems(6).Text IsNot DBNull.Value,  _
           lvi.SubItems(6).Text, "NULL") & "','" & _
       Val(If(lvi.SubItems(7).Text IsNot DBNull.Value, _
           Val(lvi.SubItems(7).Text), 0)) & "')")
   orderby = orderby + 1
Next

lvi in the for each is the ListViewItem currently indexed.
This means that lvi is lvReportFormat2.Items(ctr)

As a side note, I am pretty sure that a string property like SubItems(x).Text cannot be a DBNull value, so all these IIF are pretty useless and can be removed. I let you try it.

Said that, you really should look at parameterized queries to simplify your command text removing all those string concatenations that are error prone to get it right (for example, did yuo check what happens if one of your subitems contains a text with an apostrophe?) and are the main vector for Sql Injection attacks.

score 0 · Answer 2 · answered Mar 06 '15 at 02:32

TO SOLVE THE PROBLE YOU JUST NEED TO ADD "EXIT SUB" OUTSIDE THE LOOP

For Each lvi As ListViewItem In lvReportFormat2.Items
            Dim count = lvReportFormat2.Items.Count
            Dim ctr = 0
            Dim orderby = 0

            label.Text = lvReportFormat2.Items(ctr).SubItems(4).Text

            Do While ctr < count
                Label1.Text = lvReportFormat2.Items(ctr).SubItems(4).Text
                Execute("INSERT INTO tblrptChad (accountcode,accountdesc,Type,class,Orderby,ReportType,Formula,Show)VALUES ('" & IIf(lvReportFormat2.Items(ctr).SubItems(0).Text IsNot DBNull.Value, lvReportFormat2.Items(ctr).SubItems(0).Text, "NULL") & "','" & IIf(lvReportFormat2.Items(ctr).SubItems(1).Text IsNot DBNull.Value, lvReportFormat2.Items(ctr).SubItems(1).Text, "NULL") & "','" & IIf(lvReportFormat2.Items(ctr).SubItems(2).Text IsNot DBNull.Value, lvReportFormat2.Items(ctr).SubItems(2).Text, "NULL") & "','" & IIf(lvReportFormat2.Items(ctr).SubItems(3).Text IsNot DBNull.Value, lvReportFormat2.Items(ctr).SubItems(3).Text, "NULL") & "','" & orderby & "','" & Val(IIf(lvReportFormat2.Items(ctr).SubItems(5).Text IsNot DBNull.Value, Val(lvReportFormat2.Items(ctr).SubItems(5).Text), 0)) & "','" & IIf(lvReportFormat2.Items(ctr).SubItems(6).Text IsNot DBNull.Value, lvReportFormat2.Items(ctr).SubItems(6).Text, "NULL") & "','" & Val(IIf(lvReportFormat2.Items(ctr).SubItems(7).Text IsNot DBNull.Value, Val(lvReportFormat2.Items(ctr).SubItems(7).Text), 0)) & "')")
                ctr = ctr + 1
                orderby = orderby + 1
            Loop

               EXIT SUB

        Next

Data duplicates using Do While Loop in VB.NET

2 Answers2