duplicate in database how to handle them?

[TexG]

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

 

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()

[Derek Stone]

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.

[TexG]

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

[Robby]

You can use an INSERT INTO statement (as you did), add a sub-query in the WHERE clause at the end....

dim nAffected as integer = MyCommand.ExecuteNonQuery()

if nAffected  = -1 then
   messagebox.show("Already exists")
end if

Edited April 23, 2003 by Robby

[TexG]

could you check this out. its not finding the searchstring in the database.

 

thanks

 

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)

[Robby]

what's the value of Catagory, is it a field name?

And is there a valid value held by SearchString ?

 

Also, don't surround it with the single quotes.

 

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)

[TexG]

catagory is a string that im passing to the select stetment.

 

its one of my fields in my database.

 

same as searchstring

[Robby]

Does the value in SearchString exist in the table?

[TexG]

Yes , looking over your code that you sent looks like it working now

thanks robby

[TexG]

Robby,

 

Is there a way to do like wild card searchs like * or tex*

 

* to bring all up

 

tex* to show all that start with tex

 

thanks

[Robby]

yup...

"....where FieldName Like 'Tex%'"

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

'btw, that's a Percent sign

[TexG]

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

[Robby]

you can find the real basics here... http://www.w3schools.com/ado/ado_examples.asp and here .... http://www.w3schools.com/sql/default.asp

[TexG]

Great site thanks.

 

Still trying to figur out this one.

 

 

Dim nAffected As Integer = MyCommand.ExecuteNonQuery()

If nAffected  = -1 Then
   messagebox.show("Already exists")
End If

[Robby]

MyCommand.ExecuteNonQuery().... This returns how many rows were affected by the Delete, Insert or Update.

If the value is 10, then 10 rows were (let's say) Inserted.

[TexG]

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

 

       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)