problem in auto increment

[kanuri1]

hi,
i was got an error,
iam using a code to auto increment of serial number, but the problem is selecting the serial number from the database it was insremented from first number itself like as
1,2,3 it was incremented from 1 itself,,
i wants to increment the data from 3 it self
here my code is given below,
i am using asp.net with vb.net
data base is sqlserver-2005

Protected Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click
       Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("guest1").ConnectionString())
        Dim SQL As String = "select booking_no from guesthouse"
        'Dim conn As New SqlConnection(connstr)
        conn.Open()
        Dim com As New SqlCommand(SQL, conn)
        Dim dr As SqlDataReader
        dr = com.ExecuteReader()
        If dr.HasRows Then
            dr.Read()
            sino.Text = dr(0).ToString
        End If
        conn.Close()
        UpdateBookingNo()
    End Sub

Private Sub UpdateBookingNo()
       Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("guest1").ConnectionString())
        Dim SQL As String = "update guesthouse set booking_no = booking_no + 1"
        'Dim conn As New SqlConnection(connstr)
        conn.Open()
        Dim com As New SqlCommand(SQL, conn)
        com.ExecuteNonQuery()
        conn.Close()
    End Sub

pls help me..........

[Mind Bird]

use this code i think your problem solve
//this is function to generate your booking_no
Public Function login_id(ByVal str As String) As String

Dim id As String
Dim cmd As New SqlCommand("SELECT MAX(CONVERT(int, SUBSTRING(id,3,3)))+1 FROM tablename", con)
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Open()
Dim obj As [Object] = cmd.ExecuteScalar()
If obj.ToString() = "" Then
id = "Id1"
Else
id = "Id" & obj.ToString()
End If
con.Close()

Return id
End Function

//use this function where you using insert statement

Dim aa As String = Nothing
Dim id As String = login_id(aa)

from
Mind Bird Solutions,
www.mindbirdsolutions.com

[pauldani]

Hi

Try this

select *,identity(int,0,3) as myId
from myTable

Thanks,
Paul dani