Determine IDnumber in dataset column

[Chong]

Is there a way to determine the last value in a column before inserting? Here's my situation. I want to insert data into my database but before do so, I want to determine the ID number and increment that number then do the insert. The problem is, how do I determine what that number is in the column. I have dataset1 and dataadapter1 in the form. But I'm not sure how I would go about using the dataset to determine the ID number in the column IDnumb.

Any suggestion will be greatly welcome!

ljCharlie

[Pirate]

If MS Access is your database you're using , then let it handle this for you . No code needed , just set datatype as autonumber .

[Chong]

Many thanks for the help. I have thought of using the autonumber in Access but the problem is, if I ever need to delete a row in the table, the autonumber is not reset back. For example, I have three rows:

IDnubmer 1, 2, 3 but if I delete row three or row IDnumber 3 and then I insert another row into the table, the IDnumber will be 4 instead of 3. And then if I delete row IDnumber 4 and again, insert another row into the table, the IDnumber is 5 instead of 3 or 4.

Do you see my problem? If I can configure this out to work properly then I guess using this autonumber is the easist method.

ljCharlie

[DevGrp]

In cases like that I would just use a guid. Just generate a new one for inserts and when you delete there wont be a problem. I never use auto number for any column. I like to keep things simple.

[Chong]

What do you mean by using a guide? The IDnumber is a primary key and it can not be duplicate so how do I determine that the next IDnumber I create will not be a duplicate?

ljCharlie

[eL_NiNo]

I don't know if it's of any help, but in those case, i do a

sSQL = "SELECT MAX(MyID) FROM MyTable;"
...

try
objdr.read
iMaxId = objDR(0) + 1
catch
iMaxId=0
End try
...

sSQL = "INSERT INTO MyTable (MyID, Field1, ...) VALUES (" & iMaxID & ", ..., ...);"
...

[Chong]

I believed that's exactly what I'm trying to do. But because I use a dataset that I created during design time, I need to get the max number from the IDnumber column. But the problem I have is I couldn't figure out how to get that number from the column IDnumber so I can increment that number for the insert statement.

ljCharlie

[DevGrp]

A GUID is a globally unique identifier that is 128 bits long. The format is usually like this 166769E1-88E8-11cf-A6BB-0080C7B2D682. You could have have a string column as the primary key. Now each time you do any insert, you just generate a new guide and place in that column.

[Chong]

Thanks for the help. How would I generate the guide for new insert?

ljCharlie

[DevGrp]

Guid.NewGuid().ToString()

[Chong]

Thanks! And so everytime when I generate a new guide by using the code you gave me it will be unique, correct?

ljCharlie

[DevGrp]

Originally posted by Chong
Thanks! And so everytime when I generate a new guide by using the code you gave me it will be unique, correct?

ljCharlie

Yeah

[Corinne]

eL_NiNo
In the code you posted can you expalin what objdr is?

sSQL = "SELECT MAX(MyID) FROM MyTable;"
...

try
objdr.read
iMaxId = objDR(0) + 1
catch
iMaxId=0
End try
...

sSQL = "INSERT INTO MyTable (MyID, Field1, ...) VALUES (" & iMaxID & ", ..., ...);"
...

Thanks,
Corinne

[eL_NiNo]

It's a DataReader.

Dim objDR as OleDBDataReader
or
Dim objDR as ODBCDataReader
or
...

[Corinne]

I have a save routine that is working just fine up to this point. I need the application to autosave an ID field that is specified as an int and identity increment is 1. Everything saves fine. My problem comes in when I also need an eight character serial number to be autogenerated ie: LC000001, for the row that was just saved. This field is specified in the SQL table as char. What would be the best way to grab the last serial number in the table and increment it by 1? Can this be done since it is a char field? I'm very confused on where to go to accomplish this.

Thanks,
Corinne

[Corinne]

Anyone?

[pOfa]

eL_Nino I tried to use your code to auto increment my Ticket Number field for an error reporting program I wrote and it doesn't seem to want to auto increment. This is the last error I have to fix before I can finalize my program to version 1.0. I know I probably have to initialize the connection first, however I am unsure of how to correctly do so. It seems to always go to the catch because it keeps trying to insert a 0.

Here is my code:

' This sub is used to add a error report to the database
            Dim cmSQL As SqlCommand
            Dim strSQL As String
            Dim ssql As String
            Dim sqldr As SqlDataReader
            Dim iTickNum As Integer
Dim SQL_CONNECTION_STRING_TEST As String = "Server=myserver; DataBase=TESTPAERS; uid=****; pwd=****"
 
 
            ssql = "SELECT MAX([Ticket Number]) FROM Errors;"
 
            Try
                sqldr.Read()
                iTickNum = sqldr(0) + 1
            Catch
                iTickNum = 0
            End Try
 
            Try
 
                'Create the SQL Query and insert a row
                strSQL = "INSERT INTO Errors ([Ticket Number], Name, " & _
                "Program, Problem, Department, " & _
                "Frequency, People, Description, Date, Time) " & _
                "VALUES('" & iTickNum & "' , " & _
                "'" & cboName.Text & "' , " & _
                "'" & cboProg.Text & "' , " & _
                "'" & cboProb.Text & "' , " & _
                "'" & cboDept.Text & "' , " & _
                "'" & cboFreq.Text & "' , " & _
                "'" & radYes.Checked & "' , " & _
                "'" & txtDesc.Text & "' , " & _
                "'" & txtDate.Text & "' , " & _
                "'" & txtTime.Text & "')"
 
                cnSQL = New SqlConnection(SQL_CONNECTION_STRING_TEST)
                cnSQL.Open()
 
                cmSQL = New SqlCommand(strSQL, cnSQL)
                cmSQL.ExecuteNonQuery()
 
                ' Close and Clean up objects
                cnSQL.Close()
                cmSQL.Dispose()
                cnSQL.Dispose()
 
            Catch Exp As SqlException
                MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
 
            Catch Exp As Exception
                MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
            End Try
 
            'Show success messagebox, clear form and reset focus.
            MsgBox("You have successfully submitted your " & _
            "error report.", MsgBoxStyle.Information, Me.Text)

[eL_NiNo]

pOfa :

In your try statement, you're not handling the NULL value neither if it can read; I guess your table is empty, thus always return 0 in the Catch.

Try
                sqldr.Read()
                iTickNum = sqldr(0) + 1 ' what if it is null ? Use IsDbNull()... and assign next logical value, in this case 1
            Catch
                iTickNum = 0
            End Try

Edited: Oh and you should use a transaction if available for all these operations....

Good luck!

[pOfa]

pOfa :

In your try statement, you're not handling the NULL value neither if it can read; I guess your table is empty, thus always return 0 in the Catch.

Try
                sqldr.Read()
                iTickNum = sqldr(0) + 1 ' what if it is null ? Use IsDbNull()... and assign next logical value, in this case 1
            Catch
                iTickNum = 0
            End Try

Edited: Oh and you should use a transaction if available for all these operations....

Good luck!

Thank you eL_NiNo for your response but I don't quite understand what you mean as this is new territory for me could you explain more possibly give an example? Thank you so much!

[eL_NiNo]

Ok, try this :

Try
    sqldr.Read()
    If not isdbnull( sqldr(0) ) Then
        ' A value exists, so increase by 1
        iTickNum = sqldr(0) + 1
    Else
        ' No value found in DB, so create one at 1
       iTickNum = 1
    End if
 
Catch
    iTickNum = 0
End Try

[pOfa]

Thank you so much! That did the trick. just for those of you who need the help also here is my code:

Dim cnsql As New SqlConnection(ConnectionString)
            Dim cmSQLReadCom As New SqlCommand("SELECT MAX([Ticket Number]) FROM Errors", cnSQL)
            cnSQL.Open()
 
            Dim sqldr As SqlDataReader = cmSQLReadCom.ExecuteReader
 
            Try
                sqldr.Read()
                If Not IsDBNull(sqldr(0)) Then
                    ' A value exists, so increase by 1
                    iTickNum = sqldr(0) + 1
                Else
                    ' No value found in DB, so create one at 1
                    iTickNum = 1
                End If
 
            Catch
                iTickNum = 0
            End Try
 
            sqldr.Close()
            cnSQL.Close()