SQL Statement...Urgent!!!

[tanhaha_how]

hi,i'm newbie in mysql....can somebody help me????!!!!!

for sql statement....when i used SELECT MAX then +1....means i want to select the max value form database and automatically generate a value +1....

but the problem is...when i delete the record from database....the generated value will show for eg. 7,then i submit again....but inside the database will show for eg. 18....y??

thank you very much!!

[Fumigator]

You can read up on the Posting guidelines here (http://www.codingforums.com/postguide.htm).

Can you provide the query you use?

[tanhaha_how]

here is my code:

strSql = "select max (OrderID) as MaxOrderID from Orders"
Dim myConnection As New SqlConnection("workstation id=R2_STUD12;packet size=4096;integrated security=SSPI;initial catalog=Orders;persist security info=False")

myConnection.Open()

command = New SqlCommand(strSql, myConnection)
Try
Dim reader As SqlDataReader = command.ExecuteReader
While reader.Read

TextBox1.Text = reader.Item("MaxOrderID") + 1

End While
'reader.Close()
Catch ex As Exception
Response.Write(ex.Message)
End Try


thanks for helping:thumbsup:

[Spudhead]

Who else is using this database at the same time as you?

[Fumigator]

Is there any compelling reason you are not taking advantage of the "auto-increment" features on that ID field?

[guelphdad]

Use an auto increment field and don't rely on max(fieldname) to change data in a different field or table. This article (http://www.guelphdad.wefixtech.co.uk/sqlhelp/lastinsertid.shtml) on using last_insert_id along with auto increment should explain things.

[tanhaha_how]

okok....

finally i knew the answer already....is IDENT_CURRENT

thanks for helping....