Unique number identification

I have an application that saves a client's data into a database (Access), his client ID, name address, zip etc. What i need to do is make sure that when a person enters a client ID, which can only be 3 digits in length, my application should check to make sure that the same client ID doesnt already exist in the database. Help.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

When designing the Access database just mke the clientID a unique field. That way you will trigger an error condition if it is entered twice.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dim rs AS ADODB.Recordset
Dim adoConn As ADODB.Connection

'connect to your Access DB

Set rs = adoConn.Execute("SELECT 1 FROM tblName WHERE ClientID = " & txtClientID.Text)

If Not (rs.BOF And rs.EOF) Then
    'client id already exists
    'client id is valid
End If
intox1221Author Commented:
Azra let me try this in my application. So i should just put in messagebox.show if client already exists. Please also help me with another question i am going to post regarding my application. Thanks for ur help... The other posting title should be "syntax error message".
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

intox1221Author Commented:
Azra this is how i am connetcing to the database....
  Dim conConnection As OleDbConnection
        Dim daAdapter As OleDbDataAdapter
        Dim dsData As DataSet
It appears you are using .NET, which I have not had the chance to work with since its ALPHA/BETA days.  However you connect and execute your SQL queries should be applied to my above logic.   You get a resulting dataset and determine if it has any records after executing the SQL statement above.  If you get a record back in your dataset, the ClientID exists, if not, it doesn't.
intox1221Author Commented:
i sure am...
intox1221Author Commented:
TomLaw the clientID has the primary key, and i have indexed it to yes (no duplicates). is that what u mean ???
Hi intox - yes that's right. If someone attempts to enter a duplicate value it will trigger an error that can easily be picked up by an on error goto statement. That will allow you to raise a Msgbox to ask the user to try again.

The error could either be -2147217887 or -2147467259 in VB6 however as your using .NET it might be a different value, easy to check though.
intox1221Author Commented:
what u have suggested seems to work. I have a VB tier class, and in that all the connections are defined along with a custom error handling exception, that provides in a message box what the problem is, what the source is and so on. But could i implement a messagebox.show to simply say that the clientID u provided already exists. That will look a lot more focused to what actually the error is. Also do i need to implement that messagebox in my Dbtier class, or in my form. Also i am using .NET...
Hi Intox, - Unfortunately I have never used .NET, (my experience is limited to VB6). However it should be relatively easy to do this, I suggest:

1) Provide a messagebox to warn of duplication
2) Zero the original inputted value
3) Return the user to the original entry box to try again.
Hi intox1221,
 Here is some example code for Access in .NET Just paste this into a new program an run to see how it works .....

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()

        'This call is required by the Windows Form Designer.

        'Add any initialization after the InitializeComponent() call
    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
            End If
        End If
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents lstResults As System.Windows.Forms.ListBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.lstResults = New System.Windows.Forms.ListBox
        Me.lstResults.Location = New System.Drawing.Point(28, 32)
        Me.lstResults.Name = "lstResults"
        Me.lstResults.Size = New System.Drawing.Size(348, 160)
        Me.lstResults.TabIndex = 0
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(428, 266)
        Me.Name = "Form1"
        Me.Text = "Form1"

    End Sub

#End Region
    Private Sub InitializeData()

        Cursor.Current = Cursors.WaitCursor
        sDBName = "C:\Northwind.mdb"
        sTableName = "Products"


        sSql = ""
        sSql = "SELECT * "
        sSql += "FROM " & sTableName & " "
        sSql += "ORDER BY ProductName"
            'TblAdapter = New SqlDataAdapter(sSql, Connect)            'FOR SQL Server
            'TblAdapter = New Odbc.OdbcDataAdapter(sSQL, SyConnect)    'FOR SyBase Server
            ADOAdapter = New OleDb.OleDbDataAdapter(sSql, Con)         'FOR ADO Access database

        Catch Exp As Data.OleDb.OleDbException
            MsgBox("FillDataSet Procedure Error", _
                    MsgBoxStyle.Critical, "Load Report Error")
        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try
        Cursor.Current = Cursors.Default
    End Sub
    Private Sub PopulateListFromDS(ByVal DSTableList)
        ' A table in a dataset is used to populate a list box with
        ' product name from the products table.
        ' With the dataset the name of the table and field must be known
        ' at design time. If they are misspelled or mistyped
        ' an error will be generated only at runtime.

        Dim s As String
        Dim i As Integer


        For i = 0 To DSTableList.Tables(0).Rows.Count - 1
            ' Check to see if row is flagged as deleted.
            If Not DSTableList.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                ' Get the product name for each record.
                s = DSTableList.Tables(0).Rows(i)("ProductName").ToString()
                ' Add product name to the list box
            End If
        Next i
    End Sub
End Class

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Module Module1
    '********* database Var's ************
    Public sSql As String
    Public sTableName As String
    Public iCounter As Short
    Public myDataRow As DataRow
    Public DSTableList As New DataSet
    Public TblAdapter As Odbc.OdbcDataAdapter '(sSql, SyConnect)
    Public ADOAdapter As OleDb.OleDbDataAdapter

    '**** Server Connection Vars *********
    Public sDBServer As String               '<--- Server Name
    Public sUSERid As String                 '<--- User Id
    Public sDBName As String                 '<--- Database Name
    Public sDBPass As String                 '<--- Password
    Public Con As OleDb.OleDbConnection       '<--- for Access
    Public Connect As SqlConnection           '<--- for msSQL
    Public SyConnect As Odbc.OdbcConnection   '<---- for Sybase
#Region " MY Server Connections "
    Public Sub OpenDBSqlConnection()
        If Len(sDBPass) = 0 Then
            sDBPass = ""
        End If
            Connect = New SqlConnection("Initial Catalog=" & sDBName & ";Data Source=" & sDBServer & ";User ID=" & sUSERid & ";password=" & sDBPass & ";")
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try
    End Sub
    Public Sub OpenDBSyBaseConnection()
            SyConnect = New Odbc.OdbcConnection("DSN=Recapture ASA 7;UID=dba;PWD=sql")
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try
    End Sub
    Public Sub OpenDBADOConnection()
            Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBName & ";password=" & sDBPass & ";")
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try

    End Sub
#End Region

End Module
If intox1221 still needs help I would be happy to try to assist. This should be a really straightforward fix.
The query I initially posted shows how to determine if the record already exists, and that approach should be used if the questioner wants to prompt with a specific message box based on the results of that query.
Yes thats fine by me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.