SQL statement to go to next available number

Question

I have a database called DUI_Database. Inside that is a table called DUI_Cite_Numbers. In there are two columns- dui_cite_no and status. I have some code that takes a range of numbers from two text boxes and puts them in the table. Also, the status column is either FREE or ALLOCATED. ALLOCATED numbers are used and cannot be reused. FREE are just that- available numbers.

If you looked at the table, it would look like this, assuming I entered a range from D100100 to D100105

    DB Name: DUI_Database
    Table Name: DUI_Cite_Numbers

    dui_cite_no   status
    D100100       FREE
    D100101       FREE
    D100102       FREE
    D100103       FREE
    D100104       FREE
    D100105       FREE

Then once these numbers get used, it would look like this-

    dui_cite_no   status
    D100100       ALLOCATED
    D100101       ALLOCATED
    D100102       ALLOCATED
    D100103       FREE
    D100104       FREE
    D100105       FREE

I need help with the SELECT statement. When a form opens, I need that next FREE dui_cite_no to be used in a textbox on that form. I'm not sure how to accomplish that.

I thought this would work-

    "SELECT TOP(1) dui_cite_no FROM DUI_Cite_Numbers WHERE status = 'FREE'"

It's not working- does that look right?

Because once it's used, it marks it as ALLOCATED in the column-

    "UPDATE DUI_Cite_Numbers SET status = 'ALLOCATED' WHERE dui_cite_no = @nextcit"

I'm getting confused, probably because I've been starting at this for too long.

Here's the entire code chunk:

Public Function Get_Next_DUI_Cit_Number() As String
    ''Get the next available citation number from the database. If there is no free
    ''citation number then return a null string
    Dim nextcit As String = String.Empty
    Using DataConnection As New System.Data.SqlServerCe.SqlCeConnection("Data Source=C:\Program Files\DailyLog DUI\DUI_Database.sdf")
        DataConnection.Open()
        Dim SelectCommand As New System.Data.SqlServerCe.SqlCeCommand("SELECT TOP(1) dui_cite_no FROM DUI_Cite_Numbers WHERE status = 'FREE'", DataConnection)
        Dim DataReader As System.Data.SqlServerCe.SqlCeDataReader = SelectCommand.ExecuteReader()
        If DataReader.Read() Then
            nextcit = DataReader("dui_cite_no").ToString
            Using DataConnection2 As New System.Data.SqlServerCe.SqlCeConnection("Data Source=C:\Program Files\DailyLog DUI\DUI_Database.sdf")
                DataConnection2.Open()
                Dim UpdateCommand As New System.Data.SqlServerCe.SqlCeCommand("UPDATE DUI_Cite_Numbers SET status = 'ALLOCATED' WHERE dui_cite_no = @nextcit", DataConnection2)
                UpdateCommand.Parameters.AddWithValue("@nextcit", nextcit)
                UpdateCommand.ExecuteNonQuery()
                UpdateCommand.Dispose()
                DataConnection2.Close()
            End Using
        End If
        SelectCommand.Dispose()
        DataConnection.Close()
    End Using
End Function

Then when the appropriate form opens, this is the code:

Public Sub frmDUI_Citation_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Me.txt_dui_cite_no.Text = module1.Get_Next_DUI_Cit_Number() 'get the next available citation number and put it in the textbox

End Sub

Is your database MS SQL? When you say it's not working, do you get an error? — greg84
Sql CE. And when I open the form, I'm not getting anything, meaning my textbox isn't populating. I've tried putting the value in a messagebox just to see, but I'm getting nothing. As for errors, my code seems error free. I just added my entire section of code. And yes, I plan on using a central location for my connection string... — Rob Taylor
Can you post the code you're using to run the query against the DB? — greg84
Your function doesn't appear to be retuning the value of nextcit. Correct me if I'm wrong, not too hot on VB :) ...does it just need return nextcit at the end? — greg84

Ken White Ken White · Accepted Answer · 2012-09-15T18:32:08

If you want to make sure they're issued in sequence, you need to add an ORDER BY:

SELECT TOP(1) 
  dui_cite_no 
FROM 
  DUI_Cite_Numbers 
WHERE 
  status = 'FREE'
ORDER BY 
  dui_cite_Number

SQL statement to go to next available number

1 Answers