How to prevent duplication on insert to mysql db?

[davidck88]

Hi all . i am using the folowing code to insert listview rows to mysql database. The table in my datbase has the following structure:

CREATE TABLE `test` (
  `ID` int(11) NOT NULL auto_increment,
  `proxy` varchar(93) NOT NULL default '0',
  `port` varchar(93) NOT NULL default '0',
  `proxyport` varchar(93) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=299 ;

could any one show me how to make sure no duplicate rows(data) on proxy columnget inserted.(in another word i don't want dulicate proxy column values get insereted to table). Basicaly i want query the database if the data exist .If it exist already on the database then don't insert it and tell that user in massage box that data already exist .(and show the the succesfull insert rows in a textbox)I be happy if you guys show me how i can do such check or show me if there any better way to avoid duplicates.Thanks

sample data :

[B]proxy-------------port------- proxyport[/B]
35.2.133.111       55252         35.2.133.111:55252

 Private Sub Command6_Click()

 Dim CNN As ADODB.Connection
Set CNN = New ADODB.Connection
CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=root;PASSWORD=;OPTION=3;"


 Dim sSQL As String
Dim i As Long
Dim meaw As String ' i added
For i = 1 To myListView.ListItems.Count


     meaw = myListView.ListItems.item(i).Text & ":" & myListView.ListItems.item(i).SubItems(2)
     'MsgBox meaw
    
     CNN.Execute "INSERT INTO test (proxy,port,proxyport) VALUES('" & myListView.ListItems.item(i).Text & "','" & myListView.ListItems.item(i).SubItems(2) & "','" & meaw & "')"
    'MsgBox sSQL
    
Next
MsgBox " finished"
End Sub

 

[Andrzejek]

How about (code not tested):

Private Sub Command6_Click()

 Dim CNN As ADODB.Connection
[blue]Dim rst As ADODB.Recordset[/blue]
Set CNN = New ADODB.Connection
CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=root;PASSWORD=;OPTION=3;"


 Dim sSQL As String
Dim i As Long
Dim meaw As String ' i added
[blue]Set rst = New ADODB.Recordset[/blue]
For i = 1 To myListView.ListItems.Count
[blue]rst.Open "Select proxy From test Where proxy = '" & myListView.ListItems.item(i).Text & "'", CNN

    If rst.Eof = False then[/blue]
     meaw = myListView.ListItems.item(i).Text & ":" & myListView.ListItems.item(i).SubItems(2)
     'MsgBox meaw
    
     CNN.Execute "INSERT INTO test (proxy,port,proxyport) VALUES('" & myListView.ListItems.item(i).Text & "','" & myListView.ListItems.item(i).SubItems(2) & "','" & meaw & "')"
    'MsgBox sSQL
    [blue]TextBox1.Text = TextBox1.Text & myListView.ListItems.item(i).Text & vbNewLine
    Else
        Msgbox myListView.ListItems.item(i).Text & " Not Added."
    End If
    rst.Close
[/blue]    
Next
MsgBox " finished"
[blue]Set rst = Nothing[/blue]
End Sub

Have fun.

---- Andy

 

[gmmastros]

Two database hits per loop now, instead of just one.

I don't know MySQL as well as I do SQL Server. With SQL Server, I would do...

[tt][blue]CNN.Execute "If Not Exists(Select proxy From test Where proxy = '" & myListView.ListItems.item(i).Text & "')
INSERT INTO test (proxy,port,proxyport) VALUES('" & myListView.ListItems.item(i).Text & "','" & myListView.ListItems.item(i).SubItems(2) & "','" & meaw & "')"
[/blue][/tt]

As a query, it would look like this:

If Not Exists(Select proxy 
              From   test 
              Where proxy = [!]YourProxyValue[/!])
  Begin
    INSERT INTO test (proxy,port,proxyport) 
    VALUES([!]YourProxyValue[/!], [!]YourPortValue[/!], [!]YourProxyPortValue[/!])
  End

I don't know if the exists function is valid MySQL syntax. If it doesn't exist, there is most likely something similar.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davidck88]

Thanks you guys for helping me. Andrzejek i tried your solution and i tested it does allow duplicat values !! :-( could you help me fix it ? gmmastros at this moment i use mysql so i hope you guys find me a mysql solution. Looking forward for replies.Thanks

 

[Andrzejek]

To tell the truth, originally I was going to go with Filter:

Set rst = New ADODB.Recordset
rst.Open "Select proxy From test", CNN
For i = 1 To myListView.ListItems.Count
   rst.Filter = "proxy = '" & myListView.ListItems.item(i).Text & "'"
   ...
   rst.Filter = ""
Next
rst.Close
Set rst = Nothing

since I don't know how advanced davidck88 is.

Just one extra trip to DB

Have fun.

---- Andy

 

[Andrzejek]

Ooops, change:
[tt]If rst.Eof = False then[/tt]
to:
[tt]If rst.Eof = True then[/tt]
or just
[tt]If rst.Eof then[/tt]
that will fix it



Have fun.

---- Andy

 

[tsuji]

If it is driven by the table design spec, it should better be scripted into the create table statement, no? like adding a UNIQUE after the PRIMARY KEY.
[tt]
PRIMARY KEY (`ID`),
UNIQUE (`ID`,`proxy`)
[/tt]
In that case, the insert into can be indirectly monitored by the error object in case duplicated proxy is attempted to be inserted but rejected.

 

[davidck88]

Andrzejek Many Many thanks it worked prefectly. But now i want to do the same thing without using database for example using listbox as an ip storage.Could you show me how i can check if an ip exist in listbox before adding it? If it exist already in the listbox then don't add it and tell that user in massage box that data already exist .(and show the the succesfull added values in a textbox).Looking forward for your reply.Thanks

tsuji i have tried using UNIQUE before posting in this site but that will produce erro and it needs error trapping and ..

 

[tsuji]

>... i have tried using UNIQUE before posting in this site but that will produce erro and it needs error trapping and ..
If the requirement is driven by the db design spec, you don't take the UNIQUE statement away for anything. You can do whatever preparation before the insert into sql, but the constraint should still be there in the dbase.
Besides, what's wrong with error trapping. Error object is there for a purpose other than frivolity.

 

[Andrzejek]

Place on the Form a List1, Text1 and Command1

[Code In the Form]
Option Explicit

Private Sub Command1_Click()

If AddToListBox(List1, Text1.Text) Then
List1.AddItem Text1.Text
Else
MsgBox "Item already in the list."
End If

End Sub
[/code]
[Code in the Module]
Option Explicit

Public Function AddToListBox(ByRef lstBox As ListBox, _
strItem As String) As Boolean
Dim i As Integer

AddToListBox = True

For i = 0 To lstBox.ListCount - 1
If UCase(strItem) = UCase(lstBox.List(i)) Then
AddToListBox = False
Exit For
End If
Next i

End Function
[/code]

Have fun.

---- Andy