Hello
kindly help me to solve my problem to check item listview to avoid duplicate item in database.
I have 1 listview, my plan the listview checking each item from database before insert to database.
and item in listview obtained from opendialog excel

this is my code :
when items duplicate already exist, my app error/stop at cmd.ExecuteNonQuery()
and I'm hopeless to code check each item when reportcode exist in row of 5 or other row

Private Sub BBIUpload_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BBIUpload.ItemClick

Dim iCount As Integer
Dim iLoop As Integer
Dim cmd As New MySqlCommand
Dim lvitem
cmd.Connection = New MySqlConnection("server=localhost; user id=root; password=mypass; database=custdata")
cmd.Connection.Open()
iCount = lvlistpocorr.Items.Count
'For iLoop = 0 To lvLogs.Items.Count - 1

If Not lvlistpocorr.Items.Count = 0 Then
Do Until iLoop = lvlistpocorr.Items.Count
lvitem = lvlistpocorr.Items.Item(iLoop)

With lvitem
cmd.CommandText = "insert into cust_reportcode(reportcode,first_name,last_name,total_item,total_price,date_trasaction) values " _
& "('" & lvitem.subitems(0).text & "','" & lvitem.subitems(1).text & "','" & lvitem.subitems(2).Text & "','" & lvitem.subitems(3).Text & "','" & lvitem.subitems(4).Text & "','" & lvitem.subitems(5).Text & "')"
cmd.ExecuteNonQuery()
End With

iLoop = iLoop + 1
lvitem = Nothing
Loop

Else
MsgBox("Duplicate ID Report Code", MsgBoxStyle.Information, "Information")

End If
ExportXml()
MsgBox("done")
End Sub

If any one knows the solution please let me know.
Thanks in advance.

Edited 4 Years Ago by nubie.net

Let's start with error control. Use Try...Catch around your cmd.ExecuteNonQuery(). Then you will be able to handle the error. If you want you can skip the duplicate record and continue inserting or whatever.
If that doesn't cover you then I guess you could insert your values in a temp/work table and left/right join that to your cust_reportcode table. Any records that have null cust_reportcode fields are unique.
I'd pass the iLoop var in the temp table and retrieve it back, so that I could highlight or something the ones that already exist.

Let's start with error control. Use Try...Catch around your cmd.ExecuteNonQuery(). Then you will be able to handle the error. If you want you can skip the duplicate record and continue inserting or whatever.
If that doesn't cover you then I guess you could insert your values in a temp/work table and left/right join that to your cust_reportcode table. Any records that have null cust_reportcode fields are unique.
I'd pass the iLoop var in the temp table and retrieve it back, so that I could highlight or something the ones that already exist.

thank adam for reply :)
do you mean ' I create 2 listview and before insert, the program search items listitem1 that already exist on the move to listview2.. I need checking each item on database in listview one by one without move to listview2 because I just use 1 listview i think more then simple than use 2 listview.. if item duplicate already exist, item removed..
help me.. ' code appreciated..

Edited 4 Years Ago by nubie.net

No, 2 listviews was not what I had in mind. If the number of records in your table is high enough or you are connecting to your db over slow connection, it would take forever to populate the listview with useless data and it would probably take some time to verify each record you have against that list.
I take it you prefer to check for duplicate records instead of entering only the new ones.
I'd change the cmd.command text to :

cmd.CommandText = "insert into temp_reportcode(reportcode,first_name,last_name,total_item,total_price,date_trasaction, row_number) values " _
& "('" & lvitem.subitems(0).text & "','" & lvitem.subitems(1).text & "','" & lvitem.subitems(2).Text & "','" & lvitem.subitems(3).Text & "','" & lvitem.subitems(4).Text & "','" & lvitem.subitems(5).Text & "','" & iLoop & "')" _
& " declare @rows int " _ 

& " Select @rows = count() from temp_reportcode t inner join cust_reportcode c on t.reportcode = c.reportcode and t.first_name = c.first_name and " _
& " t.last_name = c.last_name and t.total_item = c.total_item and t.total_price = c.total_price and t.date_trasaction = c.date_transaction " _
& " if @rows > 0 then " _
& " Select row_number from temp_reportcode t inner join cust_reportcode c on t.reportcode = c.reportcode and t.first_name = c.first_name and " _
& " t.last_name = c.last_name and t.total_item = c.total_item and t.total_price = c.total_price and t.date_trasaction = c.date_transaction " _ 
& " else " _ 
& " insert into cust_reportcode select reportcode,first_name,last_name,total_item,total_price,date_transaction from temp_reportcode "

And get the rows (iLoop) that already exist. You can use datatable, a datareader or whatever you like.
Then do what you want for each lvlistpocorr.Items.Item(row) that this query returned (change color, font, or whatever)
If the query didn't return any rows then you'll find the records in your cust_reportcode table.

It goes without saying that you need a temp_reportcode table with same columns as your cust_reportcode. Also you can modify the criteria used in the join to fit your needs.

If this is a multiuser environment then temp_reportcode is not a good idea without a session ID you'll be able to use to filter your data only. Alternatively you need to make sure the table is empty (there are several ways to make sure you won't mix user's data)
Usually in MS SQL I prefer to work with variable tables and only use loop to create a single insert with select 'value1' union select 'value2',.. to insert and process everything in 1 statement. This way you don't have to worry about the table being filled with somebody's data as the var exists only in that batch.

Edited 4 Years Ago by adam_k

thank you very much your advice is very impressive for me. consist of : speed connection for check data and change color if data already exist in item and now, I has been create table to monitoring, Who upload this data?
Basically the program can be used in many computer via lan & (example : the file excel sent to backoffice from agent outside backoffice ). why I use email? I can't make website application aspx.
This is only for me, not for sale. I develop this application to increase my analyst experience if doesn't maybe as a problem in my future.
I'm sorry I reveal my story and make your busy.
I has been try your code so I put in cmd.command text & I change the connection to MsSQL. same tragic :(
still have error.. ( cmd.ExecuteNonQuery() )
and your code checking all row is very interest but for my project just need check 1 row in '" & lvitem.subitems(0).text & "' or reportcode so it to avoid a long time

In this application I work alone. I Proud, so far the application already ok and only this case for two weeks I could not finish it.
although I know later this application still have problem like security etc.. but i trust..

hi adam, if you could kindly give the example with .sln and db only about my case upload, I'm very thankfull, because I really confuse.
Thank You
GBU

hi adam, if you could kindly give the example with .sln and db only about my case upload, I'm very thankfull, because I really confuse.
Thank You
GBU

  • hi adam, if you could kindly give the example with .sln and db only about my case duplicate and insert, I'm very thankfull, because I really confuse.
    Thank You
    GBU

It would be interesting if you kindly post the full error message to understand what the error is.

thank you lolafuertes for reply

error occurs when there is a duplicate id in items (0)
the error message box are:
MySqlException was unhandled
Duplicate entry 'TRX10011' for key 'PRIMARY'
TRX ID setting as primary keys should only be 1 for each transaction.
I want how to check every item in listview from database. so that when there are items with the same ID database, the program will do the delete Item and ID different then would insert into the database.

Download My Project

here I have provided my project and I already have 2 excel.
(Excel 1) I do browse and then upload and insert, and then I browse to the second excel, I browse and then I uploaded. This will happen because of data error in excel to 2 already has an existing transaction reportCode in the database. How do I have to check on item (0) reportCode the listview on the database and when reportCode on listview items already exist, the system will delete / skip the item. then will do the insert
I hope that once there can help me.
I say many thanks.

Here is a way to do it. Written for and tested with SQL Express.
Table1 has only col1 which is varchar(50) in my case (and the @dup table has the same structure).

Public Class Form7

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If DataGridView1.RowCount > 0 Then
            Dim con As New SqlClient.SqlConnection
            Dim comm As New SqlClient.SqlCommand
            Dim data As new SqlClient.SqlDataAdapter
            Dim temp As New DataTable
            Dim insert As String
            Dim values As String = ""
            Dim processing As String

            con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\user\Documents\Visual Studio 2008\Projects\WindowsApplication1\WindowsApplication1\1.mdf;Integrated Security=True;User Instance=True"
            con.Open()
            comm.Connection = con

            insert = "declare @dup table(row int, value varchar(50)) insert into @dup (row, value) "

            For i = 0 To DataGridView1.RowCount - 1

                If values.Length > 0 Then values &= " UNION "
                values &= "select '" & i & "','" & DataGridView1.Item(0, i).Value & "' "
            Next

            processing = " declare @counter int select @counter = count(*) from @dup a inner join Table1 b on a.value = b.col1 " _
                        & " if @counter > 0  begin select a.row from @dup a inner join Table1 b on a.value = b.col1 end " _
                        & " else begin insert into Table1 (col1) select value from @dup end "
            comm.CommandText = insert & " " & values & " " & processing
            data.SelectCommand = comm

            data.Fill(temp)

            For a = 0 To temp.Rows.Count - 1
                If DataGridView1.Rows(temp.Rows(a).Item(0).ToString).IsNewRow = False Then
                    DataGridView1.Rows(temp.Rows(a).Item(0).ToString).DefaultCellStyle.BackColor = Color.Red
                End If

            Next

        End If

    End Sub

End Class

oh my god' i'm very bad.. I try your code and I create table1 only 1 column with name col1 and then I create tabel dub and counter with same structure and i try this program, why data is not insert to another table like @dub and then I don't know that what the function @counter? .. and then after insert to table1,I tried to enter data for the second time with different data. but the data does not enter into table1.. I mean the button insert only for 1 time work. sorry to make you busy will this ignorance.
thank you for your help..

again, this script was written with MS SQL in mind. You might need to make adjustments (I've never worked with MySQL, so I don't really know if you have to or if the same syntax and methods can apply).
You don't have to create @dub and @counter is not a function.
Both @dub and @counter are (SQL) variables. @dub is a table variable with is being declared within the insert (VB) var as table with 1 varchar(50) field. The @counter is an integer variable (SQL) that will hold the number of existing records (select @counter = count(*)) . If it doesn't find any matching ones it will insert them into your "live" table. If @counter is greater than 0 (duplicates have been found) it will return the row that the duplicate was found into.

If you can't use my sql or you can't understand what it is doing I suggest you insert debug.print data.selectcommand just above data.fill(temp). This will display the SQL query in the immediate pane.
Copy and paste it to your db environment, try to understand and run it. If it runs the first time, run it again and see if you get the list of duplicate rows.

Let me know if you still have problems.

ok mr. adam , I would understand the sql syntax you have given to me. I will try as much as I can.
thank you for giving the opportunity to understand the syntax. if i have a problem, I will tell you..
nice to meet you :)

This is another approach using two trips to the server to verify if the code exists and insert if does not:

With lvitem
    '
    '  first, search in the db if the code already exist using the function count(*) that will return 0 if the code not exists or 1 it it does
    cmd.commandtext = "selct count(*) from cust_reportcode where reportcode = '" & lvitem.subitems(0).text & "'"
    '
    '  to retrieve the information, you may use a datareader, that will return a row with the returned info
    dim dr as MySql.DataReader = cmd.ExecuteReader
    dr.Read()
    '
    ' now, the reader will return the right value
    dim codeExists as integer = dr.GetInt32(0)
    '
    ' you have the info, close the reader before doing any thing more
    dr.Close()
    '
    ' Now you can decide
    If codeExists > 0 Then
        cmd.CommandText = "insert into cust_reportcode(reportcode,first_name,last_name,total_item,total_price,date_trasaction) values " _18.& "('" & lvitem.subitems(0).text & "','" & lvitem.subitems(1).text & "','" & lvitem.subitems(2).Text & "','" & lvitem.subitems(3).Text & "','" & lvitem.subitems(4).Text & "','" & lvitem.subitems(5).Text & "')"
        cmd.ExecuteNonQuery()
    End If
End With

Hope this helps

@lolafuertes: Your code will work just fine, but for several reasons I decided to have everything in 1 transaction:
1) Your code will take forever if the list is long enough or the server is over WAN (the more hops the worse and if you add a slow connection then it's a nightmare)
2) In the event that the server becomes not available halfway you would end up with half the records processed.
3) In a heavy traffic table this could cause issues like duplicates (the same record can be inserted twice as the count and the insert cannot happen in a single transaction) and added delays. (I know that I'm not using trans either, but it's just way too easy to add them in the SQL batch)
4) It uses too much bandwidth.

This article has been dead for over six months. Start a new discussion instead.