[RESOLVED] [2005] Exist or not

[Loraine]

Previously im asking for the fastest way of adding a record, this is now my problem, when im creating a new account.
Example:
Name: Chitz
Contact: 108545
Address: MilkyWay
when i press the add button, well that info is now added on my Access Database, but when i press again the add button it will add again on my Acces. How i can able to check if the account is already exist.

[talkro]

Don't you have any primary key in your table?
if not, you then have to search row by row and look for a matching combination.
But I suggest you use a primary key

[talkro]

if not, then load you entire table inside a dataset, then when you add new row, you can use this peace of code to check if the row already exists

exist = false
for each row as dataRow in myDAtaset.Tables("myTable").Rows
    if(row("Name").equals(textName.text) andAlso row("Contact") = CInt(textContact.text) andAlso row("Adress").equals(textAdress.text))
            exist = true
            break
Next row
 
if exist then MessageBox.show("Already exists")
if not exist then 'add the row to the dataset.tables("myTable")

[Loraine]

what do you mean about the primary key ?

[Loraine]

I use this but i get this error "Object reference not set to an instance of an object.". using this code

Option Strict On
Option Explicit On

Imports System.Windows.Forms.Form
Imports System.Data.OleDb

Public Class CreateAccount
    Private contactID As Int64 = 0

    Public Property Contact() As Int64 '
        Get
            Return contactID
        End Get
        Set(ByVal value As Int64)
            contactID = value
        End Set
    End Property

    Private Sub btnSave_CLick(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave.Click
        Dim ObjConn As New OleDbConnection("Provider = Microsoft.Jet.OleDb.4.0;Data Source=C:\Documents and Settings\Administrator\Desktop\DBase\Bank.mdb;")
        Dim sql As String = String.Empty
        Dim dSet As New DataSet
        Dim exist As Boolean
        Dim row As DataRow

        exist = False
        For Each row In dSet.Tables("UserDtabase").Rows
            If CBool(row("Ename").Equals(txtEname.Text)) AndAlso row("Address").Equals(txtAddress.Text) AndAlso row("ContactNumber").Equals(txtContact.Text) Then
                exist = True
            End If
        Next
        If contactID = 0 Then
            sql = "INSERT INTO UserDatabase(Ename,Address,ContactNumber,AmountPaid,DatePaid,PeriodTime)" _
            & "VALUES('" & txtEname.Text & "','" & txtAddress.Text & "','" & txtContact.Text & "','" & txtAmount.Text & "','" & txtDate.Text & "','" & cboPeriod.Text & "')"
        End If
        ObjConn.Open()
        Dim dCMd As New OleDbCommand(sql, ObjConn)
        dCMd.ExecuteNonQuery()
        ObjConn.Close()
        MessageBox.Show("Record Added")
        RetriveRecords()

    End Sub

[18experience]

When creating a table in a database, you can designate a primary key. For instance, in the US, people's names are not unique, but their social security numbers (SSN) are. So, if I wanted to make a table that stored people's names and SSNs, I would make the SSN the primary key. That way you could have a table like this.

SSN Name
111-111-1111 Bob Jones
121-111-1111 Bob Jones
111-811-1111 Lovely Rita
111-222-1111 Bob Jones

So, it doesn't matter what the name is, so long as the SSN is unique. That is how a primary key is used. So, if that can help you, you need to decide what makes each record unique.

Now, even if you use a primary key, if you try to insert a record that the DB won't allow because of primary key constraints, you are going to get an error of some sort. So, you either need to check before inserting, or put a try/catch around the insert and handle that situation if it happens.

It would probably also help if you read just a little about database management.

[GaryMazzone]

You can also place a unique constraint on the combination of fields that make a record unique. Then when you try the insert the database will trhow an error of a duplicate value. You can also create a select statement to perform a test before insert (again using the combination of fields that make a record unique). If there is a return from the database with data then you display a messagebox stating that is is a duplicate record. But I would still create the constraint on the database to ensure that it can't happen in multi-user enviornments.

[Loraine]

So how i gonna make my database secured for duplicate record, because there is a time that the person name "Brad Jones" is not only one, sometimes 2 or 3, but has a different addresses, and there is also a time that the user use the same name but not his true name.

[GaryMazzone]

What about creating a unique index on the fields? What is the database engine? What are the field names? You could create a unique index on Name,address that way both name and address need to be the same before the database refuses the insert.

[18experience]

So how i gonna make my database secured for duplicate record, because there is a time that the person name "Brad Jones" is not only one, sometimes 2 or 3, but has a different addresses, and there is also a time that the user use the same name but not his true name.

This is what I was talking about when I said that YOU have to decide. We don't know what makes each record in your database unique. What if you kept someone's name, address, and telephone number? What has to be unique about that? Because, one of my brother's is named exactly the same as my Dad, so their entries in the table would be identical (unless you add a Jr. to my brother's name).

You can make any combination of the fields in your DB the key. It's up to you.

In this case, it sounds to me like you need to make all three the key, which should (I believe) allow for some of the fields to be identical, just not all three at once.

[Loraine]

Like this, how can you prevent the duplication

[GaryMazzone]

Again what should be unique? EName and Address? EName,Address and ContactNumber? EName,Address,ContactNumber and AmontPaid? EName,Address,ContactNumber,AmountPaid and DatePaid? EName,Address,ContactNumber,AmountPaid,DatePaid and PeriodTime?

What is the Database Type? MSAccess? MS SQL Server? MySQL?, Oracle?

[Loraine]

Oh sorry, im using MSAccess. well if i chose the Name that is not right, sometimes one or two people have a the same name. I think the Address and ContactNumber should be unique

[18experience]

Oh sorry, im using MSAccess. well if i chose the Name that is not right, sometimes one or two people have a the same name. I think the Address and ContactNumber should be unique

Then make those unique. Once again, we cannot possibly know what needs to be unique. It is YOUR db, so YOU are the only person who can say what should be unique.

[Loraine]

I can't use the

exist = False
For each row("Address").Equals(txtAddress.Text) AndAlso row("ContactNumber").Equals(txtContact)
If exist Then
exist = True
End If
Next Row

If exist Then
Messagebox.Show("AlreadyExist")
ElseIf not exist Then
Add the record
End If

Sample For This:

1. Jane Smith - 103 SkyLyte - 31034
2. Cris Layne - 103 SkyLyte - 31034

I realized that i can't use the Address and Contact number in a unique way. COz i can tell that the reason why the Address and Contact Number are the same because they are brothers and sisters. So i cant throw the second just because they have the same Address and Contact Number

[GaryMazzone]

I'm using Access 2003 but should be the same all the way back to Access 95.

Open the table in design mode.
Select the Index tool in the Toobar
In the index name line add a new Name What every you want.
In the field name column Select the First Field Name,
Continue adding fieldnames down in a list.
Set the Unique Property to True


When done this will create the unique index you want.

[Loraine]

When im trying to do that,

Index Name = Unique
Field Names = Ename, Address, ContactNumber
Sort Order = Ascending, Ascending, Ascending

I get this Error:
"The changes that you have made is not successfully because they would create duplicate values in the index

[GaryMazzone]

That mean you need to clean up the database before you create the index. There are already duplicates in the data and the index will not allow that.

[Loraine]

Thank You Very much, so thats the way it is, how fascinating. ^^. Well last question before i go to sleep ^^.

Do you have any better nor fastest way of adding a record. Im using this

       Get
            Return contactID
        End Get
        Set(ByVal value As Int64)
            contactID = value
        End Set

Just to determine what im going to do, if im adding a new record or deleteing or updating. do you have other way to do adding new record

[GaryMazzone]

When adding a new record to a database I simpily write and Insert Into statement in SQL.
Insert Into TableName (Field1,Field2,......) Values (value1,value2....)

Then execute the SQL statement. In VB6 you execute on the connection object. In .Net you use the ExecuteNonQuery Method of the connection object.

For a delete you do the same
Delete From TableName Where SomeField = SomeValue

For Update
Update TableName Set
Field1 = Value1,
Field2 = Value2,
.......
Where SomeField = SomeValue

The SomeField in both the delete and update statements I would normally use the primary key of the table.

[Loraine]

Just like the way that i use on my post #5

[GaryMazzone]

Yes.

[Loraine]

Thanks again, if i finished my database i try to find a shortest way of adding,updating,deleting in a database ^^..