auto increment a number

[kanuri1]

hi frnds
i had got a problem,
i want to increase the serial number automatically when page is loaded and it will be inserted into the database at every time when the page is loaded .......
here my coding in page load event is given below,,,,,

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String
        connstr = ConfigurationManager.ConnectionStrings("guest1").ConnectionString()
        Dim conn As New SqlConnection(connstr)
        Dim dr As SqlDataReader
        Dim da As New SqlClient.SqlCommand("select booking_no=booking_no+1 from guesthouse", conn)
        conn.Open()
      
        dr = da.ExecuteReader()
        If dr.Read() Then
            sino.Text = dr(0).ToString
            
        End If
        conn.Close()

pls reply me answer....
here i use vb.net coding.........

here iam using sqlserver 2005 as a database...........

[Oxiegen]

The easiast way to do this would be to first fetch the information from the database then call a seconday method in order to update the database.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String
connstr = 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 connstr As String
connstr = 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 Function

[crishjeny]

Hi

You can refer following code for increment number.It will help you.

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

If you want to more detail about it than refer following link.

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html