Incrementing PK (int) before Insert record

NigelBronson · September 19th, 2006, 03:55 PM

Hello all,

I am currently building a Insert record feature, which will increment the PK (CustomerID) by one, and then add the fields. In classic asp it was simple, you just read in the value, store it, add 1 then pass it back. Is it still the same in ASP.NET?

I threw some code up and got this far:

Code:

Public Class InsertCustomer

    Private ReadOnly _conString As String

    Public Function GetCusID() As SqlDataReader
        'create command
        Dim con As New SqlConnection(_conString)
        Dim cmd As New SqlCommand()
        Dim CusId As Integer
        cmd.Connection = con
        cmd.CommandText = "SELECT MAX(CustomerId) From Customers"
        con.Open()
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        While reader.Read
            CusId = CType(reader("CustomerID"), Integer)
            CusId = CusId + 1
            'While GetCusID.Read

        End While
        'Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
    End Function
    Public Sub New()
        _conString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    End Sub



End Class

Is there any simpler way to do it? IE without writing a VB Class?
Much appericiated

dparsons · September 19th, 2006, 04:42 PM

The easiest way to do it would be to set the PK to an auto inc field so the SQL Server incremented the value for you.

Probably what i would do, as opposed to writing a class in code, I would write a stored procedure that would handle everything for me and all i would have to do is pass in the values of my insert statement.

My 2 cents anyway.

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.