duplicate in database how to handle them?

T

TexG

Well-known member
Joined
Apr 2, 2003
Messages
88
Location
San Antonio Texas
Hello All,

Makeing a database where i want the primary key to be a word.
how do i check for duplicate in database or how to handle them error code wise?

any ideals

thanks

[VB]
Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
IO.Path.GetDirectoryName(Application.ExecutablePath) & "\DataBase\" & _
NameofDB)

MyConnection.Open()

Dim MyCommand As New OleDbCommand("INSERT INTO Cable " & _
"(Jack_Name, Jack_Location, IDF_Location, Patch_Panel_Number," & _
"Port_Number, Cable_Type, Assigned_Port_on_Switch, Assigned_Vlan) VALUES " & _
"(" & MainClass.ADDNewLineDB.TextBox1.Text & ", " & MainClass.ADDNewLineDB.TextBox2.Text & "," & _
"" & MainClass.ADDNewLineDB.TextBox3.Text & ", " & MainClass.ADDNewLineDB.TextBox4.Text & "," & _
"" & MainClass.ADDNewLineDB.TextBox5.Text & ", " & MainClass.ADDNewLineDB.TextBox6.Text & "," & _
"" & MainClass.ADDNewLineDB.TextBox7.Text & ", " & MainClass.ADDNewLineDB.TextBox8.Text & ")", MyConnection)

MyCommand.ExecuteNonQuery()

MyConnection.Close()

MyCommand.Dispose()
[/VB]
 
It all depends. You can use the DISTINCT keyword to ignore duplicates, a SELECT statement to check for duplicates or a DELETE statement to get rid of them. We need more information on what exactly you need to do.
 
Well if duplicate then msgbox warning the user and then ill have my program go to that duplicate. then the user can ether delete it update it so on

how would i do a check stetment if found the msgbox if not go head and add?

thanks
 
You can use an INSERT INTO statement (as you did), add a sub-query in the WHERE clause at the end....
Code: 
dim nAffected as integer = MyCommand.ExecuteNonQuery()

if nAffected  = -1 then
    messagebox.show("Already exists")
end if
 
Last edited by a moderator:
could you check this out. its not finding the searchstring in the database.

thanks

Code: 
Dim MyCommand As New OleDbCommand("SELECT Jack_Name, Jack_Location, IDF_Location, " & _
        "Patch_Panel_Number, Port_Number, Cable_Type, Assigned_Port_on_Switch, " & _
        "Assigned_Vlan from Cable WHERE (" & Catagory & " = " & SearchString & ")" & _
        "order(by) " & Catagory & "", MyConnection)
 
whats the value of Catagory, is it a field name?
And is there a valid value held by SearchString ?

Also, dont surround it with the single quotes.

Code: 
Dim MyCommand As New OleDbCommand("SELECT Jack_Name, Jack_Location, IDF_Location, " & _
        "Patch_Panel_Number, Port_Number, Cable_Type, Assigned_Port_on_Switch, " & _
        "Assigned_Vlan from Cable WHERE " & Catagory & " = " & SearchString & "" & _
        "order by " & Catagory , MyConnection)
 
yup...
Code: 
"....where FieldName Like Tex%"

or
dim s as string = "Tex"
"....where FieldName Like " & s & "%"

btw, thats a Percent sign
 
Wow that worked great.

One last question.

I there a wec site or book that has all of the neat strings so i can learn more on the select statment?

Thanks robby
 
Great site thanks.

Still trying to figur out this one.


Code: 
Dim nAffected As Integer = MyCommand.ExecuteNonQuery()

If nAffected  = -1 Then
    messagebox.show("Already exists")
End If
 
MyCommand.ExecuteNonQuery().... This returns how many rows were affected by the Delete, Insert or Update.
If the value is 10, then 10 rows were (lets say) Inserted.
 
let me explain it again i think were off track.

looking for duplicates if found then msgbox if not continue

heres the code the key to the database is Jack_number

if jack_number is allready in database msgbox nop cant do that
else it adds it and contues on.

Thanks robby

Code: 
        Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                                   IO.Path.GetDirectoryName(Application.ExecutablePath) & "\DataBase\" & _
                                   NameofDB)

        MyConnection.Open()

        Dim MyCommand As New OleDbCommand("INSERT INTO Cable " & _
        "(Jack_Name, Jack_Location, IDF_Location, Patch_Panel_Number," & _
        "Port_Number, Cable_Type, Assigned_Port_on_Switch, Assigned_Vlan) VALUES " & _
        "(" & MainClass.ADDNewLineDB.TextBox1.Text & ", " & MainClass.ADDNewLineDB.TextBox2.Text & "," & _
        "" & MainClass.ADDNewLineDB.TextBox3.Text & ", " & MainClass.ADDNewLineDB.TextBox4.Text & "," & _
        "" & MainClass.ADDNewLineDB.TextBox5.Text & ", " & MainClass.ADDNewLineDB.TextBox6.Text & "," & _
        "" & MainClass.ADDNewLineDB.TextBox7.Text & ", " & MainClass.ADDNewLineDB.TextBox8.Text & ")", MyConnection)

        MyCommand.ExecuteNonQuery()

        MyConnection.Close()

        MyCommand.Dispose()




        Dim i As Integer

        i = MainClass.DB.ListView1.Items.Count

        MainClass.DB.ListView1.Items.Add(MainClass.ADDNewLineDB.TextBox1.Text)

        MainClass.DB.ListView1.Items(i).SubItems.Add(MainClass.ADDNewLineDB.TextBox2.Text)

        MainClass.DB.ListView1.Items(i).SubItems.Add(MainClass.ADDNewLineDB.TextBox3.Text)

        MainClass.DB.ListView1.Items(i).SubItems.Add(MainClass.ADDNewLineDB.TextBox4.Text)

        MainClass.DB.ListView1.Items(i).SubItems.Add(MainClass.ADDNewLineDB.TextBox5.Text)

        MainClass.DB.ListView1.Items(i).SubItems.Add(MainClass.ADDNewLineDB.TextBox6.Text)

        MainClass.DB.ListView1.Items(i).SubItems.Add(MainClass.ADDNewLineDB.TextBox7.Text)

        MainClass.DB.ListView1.Items(i).SubItems.Add(MainClass.ADDNewLineDB.TextBox8.Text)
 
Back
Top