[Jerrrum]

Hello, I am in the process of creating a program that is linked to a database. I want to automatically generate a reference number that is not already present in this database, to do this i know i am going to need to search the database to check whether that number is already present or not. But I am unsure of the best way to go about this. I am using an access database and vb.net. Thanks.

[CharlieMay]

Use a SELECT statement to Query the database for the value you want, Store it to a variable and manipulate your variable for when you're ready to save it back to the database.

Your post gives us no point of reference as to your abilities but since you stated you're in the process of using a database, we have to think you at least know how to SELECT data from it. Otherwise, post your code and show us where you're stuck.
OleDB Basics in VisualBasic.Net

[Jerrrum]

My abilities aren't great as i have been programming for about half a year.
I haven't currently got any code for this section of my program as i am not sure what to do. I have an idea but cant get it down in code.

I have got the text box containing the desired reference number and a database that contains records also with reference numbers.

I want to do some sort of SELECT or LOOP to find out whether that reference is already present in the database or not as i want all of the references to be unique.

The thing i'm struggling with is getting my head around how to search the database to see if that reference number already exists or not.

Thanks for the interest.

[Jerrrum]

My abilities aren't great as i have been programming for about half a year.
I haven't currently got any code for this section of my program as i am not sure what to do. I have an idea but cant get it down in code.

I have got the text box containing the desired reference number and a database that contains records also with reference numbers.

I want to do some sort of SELECT or LOOP to find out whether that reference is already present in the database or not as i want all of the references to be unique.

The thing i'm struggling with is getting my head around how to search the database to see if that reference number already exists or not.

Thanks for the interest.

[CharlieMay]

Have you clicked on the link to the tutorial I provided? I recommend at least working through it to give you a better understanding of the basics. While it won't completely cover your question it will give you the ability to provide an attempt and give us something to work off of. This site isn't one that just hands out solutions, we take pride in helping you learn and not just become a copy-paste coder. There are a lot of volunteer individuals on this site that can help you but we need to see an effort on your part.

[Jerrrum]

I will give it a go and see what i come out with, thanks.

[Jerrrum]

        
Dim i As Integer = 1000
Dim x As Integer = 0

        Do Until x = MaxRows

            If txtCustRefNum.Text = ds.Tables("CustomerDetails").Rows(x).Item("CustomerReference") Then
                txtCustRefNum.Text = (i + 1)

            ElseIf txtCustRefNum.Text Is Not ds.Tables("CustomerDetails").Rows(x).Item("CustomerReference") Then
                x = x + 1
            End If
            
        Loop

I have come up with this so far. Does it make any sort of sense or do you want me to explain what i am doing in each part of the code?

[Jerrrum]

        MaxRows = ds.Tables("CustomerDetails").Rows.Count

        Do Until x = MaxRows

            If txtCustRefNum.Text = ds.Tables("CustomerDetails").Rows(x).Item("CustomerReference") Then
                txtCustRefNum.Text = ("CR" & i + 1)
                x = 0
            ElseIf txtCustRefNum.Text = ds.Tables("CustomerDetails").Rows(x).Item("CustomerReference") = False Then
                x = x + 1
            End If
            
        Loop

This code works for the first two entries in the database, when it gets to the third one it just freezes the whole program. Any idea why this may happen?

[Jerrrum]

YES! Have finally figured it out. Just had to take the i+1 out of the bracket.

i = i + 1
                TextBox1.Text = (i)