Last post Feb 23, 2015 03:15 AM by Fei Han - MSFT
Feb 21, 2015 06:30 PM|dzakyalfajr|LINK
Hi guys, I need help on how to auto increment value in the database and display it in the textbox
So, the table has 5 fields : Car_Code, Date, Instalment_X, Amount, UserID
what I want is, when the user trying to add new data, the web check the database where UserID=Session (I know how to do this), but how to check the last Instalment (ex: the 3rd instalment), and +1 then display it automatically in the textbox when the user
enter the Car_Code. So, if the user paid the last 3 instalment, then now the user are paying the 4th instalment.
I hope I explained clear enough.. English isn't my first language, Thank you in advance.
Feb 22, 2015 08:51 AM|Rion Williams|LINK
If you needed to increment a specific field in your table (e.g. Instalment_X) when passing in a UserID, you could use something like this :
SET Instalment_X = Instalment_X + 1
WHERE UserId = @UserId
If you needed to return the value after incrementing it, you could use the rarely seen
OUTPUT keyword in SQL :
' Open a database connection to update your value (and return it) '
Using connection As New SqlConnection("Your Connection String")
' Build a query to execute '
Dim query = "UPDATE YourTable SET Instalment_X = Instalment_X + 1 OUTPUT INSERTED.Instalment_X WHERE UserId = @UserID"
' Build a command to execute your query '
Using command As New SqlCommand(query, connection)
' Open your connection '
' Add your parameter(s) '
' Execute your query, returning your new Installment value '
Dim currentInstallment = CInt(command.ExecuteScalar())
' Output the installment in the TextBox '
YourInstallmentTextBox.Text = currentInstallment.ToString()
Additionally, you could always just perform a basic query and increment the value yourself within .NET instead of SQL.
Feb 23, 2015 03:15 AM|Fei Han - MSFT|LINK
Thanks for your post.
how to check the last Instalment
The “Instalment_X” is auto_increment, so you could refer to the following query string to get the last Instalment from your database. Then you could display the result of
Instalment_X + 1 in the textbox.
select max(Instalment_X) from [yourtable]