Solved

Unique number identification

Posted on 2003-12-09
17
255 Views
Last Modified: 2010-05-01
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.
0
Comment
Question by:intox1221
  • 5
  • 5
  • 3
  • +1
17 Comments
 
LVL 4

Accepted Solution

by:
TomLaw1999 earned 250 total points
ID: 9906933
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.
0
 
LVL 28

Assisted Solution

by:AzraSound
AzraSound earned 250 total points
ID: 9906934
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
Else
    'client id is valid
End If
0
 

Author Comment

by:intox1221
ID: 9907070
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".
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:intox1221
ID: 9907188
Azra this is how i am connetcing to the database....
     
  Dim conConnection As OleDbConnection
        Dim daAdapter As OleDbDataAdapter
        Dim dsData As DataSet
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 9907393
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.
0
 

Author Comment

by:intox1221
ID: 9907485
i sure am...
0
 

Author Comment

by:intox1221
ID: 9912475
TomLaw the clientID has the primary key, and i have indexed it to yes (no duplicates). is that what u mean ???
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9913532
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.
0
 

Author Comment

by:intox1221
ID: 9913611
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...
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9913906
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.
0
 
LVL 27

Expert Comment

by:planocz
ID: 10235366
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()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call
        InitializeData()
        PopulateListFromDS(DSTableList)
    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
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    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.SuspendLayout()
        '
        'lstResults
        '
        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
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(428, 266)
        Me.Controls.Add(Me.lstResults)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region
    Private Sub InitializeData()

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

        OpenDBADOConnection()

        sSql = ""
        sSql = "SELECT * "
        sSql += "FROM " & sTableName & " "
        sSql += "ORDER BY ProductName"
        Try
            '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
            ADOAdapter.Fill(DSTableList)

        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

        lstResults.Items.Clear()

        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
                lstResults.Items.Add(s)
            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
        Try
            Connect = New SqlConnection("Initial Catalog=" & sDBName & ";Data Source=" & sDBServer & ";User ID=" & sUSERid & ";password=" & sDBPass & ";")
            Connect.Open()
            Connect.Close()
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try
    End Sub
    Public Sub OpenDBSyBaseConnection()
        Try
            SyConnect = New Odbc.OdbcConnection("DSN=Recapture ASA 7;UID=dba;PWD=sql")
            SyConnect.Open()
            SyConnect.Close()
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try
    End Sub
    Public Sub OpenDBADOConnection()
        Try
            Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBName & ";password=" & sDBPass & ";")
            Con.Open()
            Con.Close()
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try

    End Sub
#End Region

End Module
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 11087595
If intox1221 still needs help I would be happy to try to assist. This should be a really straightforward fix.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 11088646
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.
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 11120956
Yes thats fine by me.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question