Solved

Unique number identification

Posted on 2003-12-09
17
251 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:intox1221
Comment Utility
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
Comment Utility
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
Comment Utility
i sure am...
0
 

Author Comment

by:intox1221
Comment Utility
TomLaw the clientID has the primary key, and i have indexed it to yes (no duplicates). is that what u mean ???
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 4

Expert Comment

by:TomLaw1999
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes thats fine by me.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now