Randomly generating number take 2

Hi i have managed to generate a three digit number randomly and populate it to a textbox. My application connects  to a DB and populates a listview according to whats in the database. What i need to do now is to make sure that the randomly generated number doesnt already exist in the database. So basically i have text fields that let users enter clientID, firstname, lastname etc, from text boxes and upon pressing the save button, saves it to the database. So when  i press the add button, my ClientID text field populates with a randomly generated 3 digit number, i just need to make sure it doesnt generate a number or client ID, already in the database....
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Hi intox1221:
Sounds like an easy one to me.
To be able to answer, though, you should give more information about your database:
MSAccess? SQL?
Is the clientID field indexed? Is it the PrimaryKey?

intox1221Author Commented:
it is the primary key. It is indexed to yes (no duplicates).

These r my connection details. this is the top level class variable in the form.
Dim cstrConnection As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" _
        & System.AppDomain.CurrentDomain.BaseDirectory() & "MailingList.mdb"

I have  Dbtier class set up which handles all the connections. A part of the code is as follows.

    Public Shared Function ReturnData(ByVal pstrConnection As String, ByVal pstrSQL As String) As DataSet
        ' This procedure returns a dataset containing
        ' all records from the passed-in query. The
        ' connection string is needed to connect to
        ' the database.

        'declare the DB connection, data adapter, and data set objects
        Dim conConnection As OleDbConnection
        Dim daAdapter As OleDbDataAdapter
        Dim dsData As DataSet


            'open the connection
            conConnection = New OleDbConnection(pstrConnection)

            'create the data adapter (this manages the data) based on the query
            daAdapter = New OleDbDataAdapter(pstrSQL, conConnection)

            'create the data set and fill it from the data adapter
            dsData = New DataSet

            'close the connection
It does help to know that you are using .NET and an .mdb file

What you need to use is the "Find" function

Just type dsData.Tables(0).Find and press F1 for help.
If the help is not clear to you, then please let me know, and I will guide you through it.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

intox1221Author Commented:
i will take a look at help like u suggest, it always helps. But i would rather u guide me where i need to go... thanks for ur time though....
Something like this:

      Dim dt As DataTable = dsData.Tables(0)
      Dim dc(0) As DataColumn
      dc(0) = dt.Columns("clientId")
      dt.PrimaryKey = dc
      Dim keys(0) As String
      keys(0) = txtClientID.Text
intox1221Author Commented:
what do i declare dsdata as and where. Also does it have to be local or class level variable.
I just took the variable as appears in your code!

Insert my code after the daAdapter.Fill(dsData) line


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
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
No response from intox1221 from 12/10/2003 comment
Award points to Dabas is recommend.
Please leave any comments here within the next seven days.

EE Cleanup Volunteer
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.