Cycle through numbers SQL CE

[RobRTaylor]

I hate to ask, but I need some help with code. I don't have any because I don't know where to begin. Here's what I'm after.

I have a Sql CE database attached to the app, and a table called DUI_Cite_Numbers. There is two columns, DUI_Cite_Start and DUI_Cite_End.

I need to start a range of numbers- say D100000 to D100500. When a form1 starts, I need it to take the next number in sequence starting at D100000. When it gets to D100500, I need a prompt to tell me to enter more.

I've been up and down the net looking. I can read and write to the database no problems, but I don't know the best way to get this range of numbers in, and then have the app take the next one in sequence. I can't wrap my head around the logic.

I know the group wants people to try on their own first and post code, but I'm humbly asking for help... I just don't have anything, and don't know where to start. Once I get this portion, the application is complete. I've put this off to last because I've been trying to learn this portion, but not having any luck.

Thanks for any and all help.

[Reverend Jim]

Since you already know how to read and write from/to the database I assume you are wondering how to preserve the number last used by the app. Before I offer a suggestion, what version of VB are you using? Newer versions of VB use

My.Computer.Settings

to save settings between sessions on a per user or per application basis.

[RobRTaylor]

VS2010.

Yes, I can write the two numbers to the columns to the database. I didn't think about using MySettings, but now that you mentioned it, I'm trying to figure out the logic (or method) behind it. Or, I'm not sure how to connect the start and end number of the DB to the My.Settings...

[RobRTaylor]

Here's what I have to save the two textboxes to the DB. Not too complicated.

 Public Sub saveDUICite_Numbers_SQLCE()
        Try
            SetSaveSQL("Insert Into DUI_Cite_Numbers(dui_cite_start, dui_cite_end) " & _
    ("VALUES ('" & frmOptions.txt_DUI_Cite_Start.Text & _
       "','" & frmOptions.txt_DUI_Cite_End.Text & "')"))

            MsgBox("DUI Citation numbers saved. ", MsgBoxStyle.Information, "DUI Citation Numbers Local Database")
            My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Asterisk)
        Catch ex As Exception
            MsgBox("DUI numbers NOT SAVED. Contact Rob at XXX-XXX-XXXX ", MsgBoxStyle.Critical, "DUI Citation Numbers Local Database")
        End Try

    End Sub

    Sub SetSaveSQL(ByRef SQL As String)
        '// MICROSOFT SQL SERVER CE 3.5
        Dim conn As New SqlCeConnection
        Dim cmd As New SqlCeCommand
        conn.ConnectionString = "Data Source=C:\Program Files\DailyLog DUI\DUI_Database.sdf"
        cmd.Connection = conn
        cmd.CommandText = SQL
        Dim da As New SqlCeDataAdapter(cmd)
        Dim dt As New DataTable
        Try
            conn.Open()
        Catch ex As Exception
            MsgBox("Is there a database on your computer? ", MsgBoxStyle.Exclamation, "CRITICAL ERROR")
        End Try
        'da.Fill(da)
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            'MsgBox("You can't save this citation or report- either the citation number has already been issued, or the case number has already been recorded. Use the UPDATE option instead", MsgBoxStyle.Information, "CRITICAL ERROR")
        End Try

        conn.Close()
    End Sub

[Reverend Jim]

I'm confused as to the problem you are trying to solve. It seems to me that whenever someone starts to fill out a new DUI form, they should be issued the next available number. That's just a simple query to the database to allocate and assign the next available number. I don't understand why you need to be prompted to enter more numbers when you can just generate them.

[RobRTaylor]

I probably explained it weird. Thats exactly what I'm after. Once I have the range in the DB, I need to query to the database to allocate and assign the next available number.

I need help with the query, allocation and assignment.

[Reverend Jim]

Your database is going to have multiple records containing dui_cite_start, dui_cite_end. You want to keep track of which dui_cite numbers have been allocated. If your table looked like

dui_cite_no
allocated

where dui_cite_no was the primary key then you could get then next available number as

select top(1) dui_cite_no where allocated = 'Y'

If no record is returned then you are out of citatation numbers and have to add more. Like I asked before, if the only thing you are going to do when you run out is add more, why not just let the database generate the new numbers as needed?

[RobRTaylor]

Can't let the database generate more. Each citation number must be unique throughout the ENTIRE State. Right now, we need to get a block of numbers issued to us by the State, then right now we will just add them to the local PC. If two DUI citations had the same citation number, that would be a nightmare. The accused could possibly win the case if that ever happened, and it would throw the State's system into a tailspin when the citation got entered into their system...

The code looks simple enough. So the

allocated

column needs to be added in addition to the dui_cite_start, dui_cite_end columns? Or get rid of the dui_cite_end and instead rename it allocated?

Then have the

    select top(1) dui_cite_no where allocated = 'Y'

on the frmLoad, loading the textbox with the current cite number?

[Reverend Jim]

Oh. Well that makes sense. In that case you still need (minimum) the dui_cite_no and an allocation field. Then you would need a dialog box to prompt you for a new range of numbers to add when you run out and perhaps a warning for the app to tell you when you are running out of free numbers. I'll get back in a bit. Gotta go shopping.

[RobRTaylor]

Back from shopping? :)

[Reverend Jim]

Sorry. The day kind of got away from me.

So your table would have (as a minimium)

*dui_cite_no    int
 status         varchar(16) (default value is 'FREE')

but possibly also including fields like

 alloc_date     datetime
 alloc_by       varchar(50)

Where "*" indicates a primary key field. I don't know the format of the numbers so I don't know if you would use an int or a some type of text field. The "allocated" field could be a boolean, or perhaps more useful, a datetime field so you could look at the table and determine when a particular number was allocated. perhaps you would also include a field to identify the officer.

To get the next available number your query would look like

SELECT TOP(1) dui_cite_no WHERE status = 'FREE'

and to prevent someone else from grabbing it you could immediately set the status to something like 'PENDING' and at that time enter an alloc_date and alloc_by (officer ID). Then when the citation is completed you could update the status to 'COMPLETED'. You could even allow the officer to save work in progress and keep the status as 'PENDING' until final submission at which point you change status to 'COMPLETED'

All you would need is a dialog box where you could enter the start and end range values of the next block. The code behind that would have a for loop like

For cit_no As Integer From Cint(txtStart.Text) to Cint(txtEnd.Text)
    Dim query As String = "INSERT INTO Citations (dui_cite_no) " & _
                          "VALUES(" & cit_no & ")"
    'execute the query via the method of your choice
Next

[Reverend Jim]

If you don't have a default value set for status then you can set it in the insert as

Dim query As String = "INSERT INTO Citations (dui_cite_no,status) " & _
                      "VALUES(" & cit_no & "'FREE')"

[RobRTaylor]

Thanks. I'm going to work with this and see what I can figure out. The database is just going to be on the users PC, I'm not using any online database, so there's no issues with sharing numbers with everyone else, or assigning a status to it or anything. I need to keep this simple, I'm a cop, not really a programmer (but I'm trying). Each officer just gets their own block of numbers, like D155750 to D156000. That gives them a range of 250 citations to write.

Here's some more background on the situation.

I have on one form two text boxes, to enter the beginning and ending block of numbers, and a button to save them to the local DB.

How I have it set up is the Microsoft Sql CE 3.5 database on the users laptop. So, I can write values to the rows easy enough. To retreive them, I'm using a DataGridViewer to show the rows. Then from there (on the actual cite and report forms) the user can retreive a cite or report by clicking a row and it will fill in the text boxes with the following code sample:

        txt_subj_surname.Text = DataGridView1.Item(0, e.RowIndex).Value.ToString
        txt_subj_givenname.Text = DataGridView1.Item(1, e.RowIndex).Value.ToString

I tell you this just as info only, has really nothing to do with the cite numbers...

The DB has:

*dui_cite_start (nvarchar 10)(Primary Key)
dui_cite_end    (nvarchar 10)
status          (nvarchar 16)

Do I need the dui_cite_end value entered in the DB? Without it, how does the app know there to end the series?

I think I understand where you are going with this. This will take me a few days to wrap my head around and see if I can get it to work.

[RobRTaylor]

    Public Sub saveDUICite_Numbers_SQLCE()
        '// MICROSOFT SQL SERVER CE 3.5
        Dim conn As New SqlCeConnection
        Dim cmd As New SqlCeCommand
        conn.ConnectionString = "Data Source=C:\Program Files\DailyLog DUI\DUI_Database.sdf"
        cmd.Connection = conn
        Dim da As New SqlCeDataAdapter(cmd)
        Dim dt As New DataTable
        For cit_no As Integer From Cint(frmOptions.txt_DUI_Cite_Start.Text) to Cint(frmOptions.txt_DUI_Cite_End.Text)
            cmd = New SqlCeCommand("Insert Into DUI_Cite_Numbers(dui_cite_start, dui_cite_end, status) " & _
    ("VALUES ('" & frmOptions.txt_DUI_Cite_Start.Text & _
    "','" & frmOptions.txt_DUI_Cite_End.Text & "'FREE')"), conn)
            If conn.State = ConnectionState.Closed Then conn.Open()
            cmd.ExecuteNonQuery()
        Next
        conn.Close()
    End Sub

[Reverend Jim]

It's easier to get the idea if you don't think in terms of computers. Imagine you are in charge of several officers and you are not the one handing out citations. You have a pad of forms. Each form has a table with blank rows and columns labeled Citation Number, Status and Date Issued. You tear off the first form and fill in only the Citation Number column with 25 numbers. You give that to the first officer. You fill out the next form in the same way with another block of 25 (different) numbers and give it to the second officer. You do this for all of your officers. Each officer now has an allocated block of numbers. When an officer has used up all of the numbers (or is running low), he/she comes to you for another sheet of numbers. Now replace "sheet" with "database". You don't have to record START and END numbers as two columns because they are already recorded in the first and last rows.

[Reverend Jim]

Funny thing, I have a brother-in-law who is in law enforcement. His name is also Robert Taylor.

[RobRTaylor]

Thanks for the info Reverend Jim. I'll try your suggestion and remove the end number- just have the start and status. If it makes any difference, each computer has no idea what set of numbers the other computer has. So one guy might get D155750 to D156000 and one might get D172000 to D172250.

You said " fill in only the Citation Number column with 25 numbers". Thats true, but not manually one number at a time till all the columns are filled. Thats why my thinking keeps reverting back to the range so the computer knows when to stop and ask for more numbers.

[RobRTaylor]

QQ-

 For cit_no As Integer From Cint(frmOptions.txt_DUI_Cite_Start.Text) to Cint(frmOptions.txt_DUI_Cite_End.Text)

"From" is giving me a Syntax error. I can't figure out why, the code makes sence- "From" and "To"

[Reverend Jim]

Sorry. A little of the old syntax slipped in. Replace "From" with "=". That's the problem with being a dinosaur. Because your citation numbers are actually strings you will have to separate the "D" from the rest of the number in order to generate the new numbers. Look at this

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim citStart As Integer = CInt(txtStart.Text.Substring(1))
        Dim citEnd As Integer = CInt(txtEnd.Text.Substring(1))
        Dim prefix As String = txtStart.Text.Substring(0, 1)

        For citnum As Integer = citStart To citEnd
            Dim newcit As String = prefix & citnum
            TextBox1.AppendText(newcit & vbCrLf)
        Next

    End Sub

txtStart.Text.Substring(1) throws away the first character (the "D") and keeps the remaining string (the number part). Cint takes the number portion and converts it to an integer. We do this for both the start and end strings. Then we save the leading character ("D") in prefix.

The loop then generates the series of numbers, slaps the "D" back on the start and pops it into a multiline textbox. Of course, you would just use newcit in a query to insert the new records

[RobRTaylor]

Problem solved. Thanks for everyones help!