How to prepend character to Integer

We have a field named returnNo that returns integer values when queried from the database.

Example of integer returned is 4098.

We would like to add the character T at the beginning of the integer values and then increment the value by 1. The result of appending this character to the integer value and incrementing it by 1 is then inserted into the database.

When I run the code below, it returns only the integer value without the T character and inserts this value to the database.

What am I doing wrong?

Dim retNo As Integer = 0
Dim return_No As String = ""
Dim dteReturned As String = ""
Try
    Dim Sqls As String = "select MAX(returnNo) returnNo from Trials"
    Dim Sqlsr As String = "select DateReturned from Trials where ownerId='" & Session("rOwnerID") & "' "
    Dim prevretcmd As SqlCommand = New SqlCommand(Sqls)
    prevretcmd.Connection = con
    Dim rdr As SqlDataReader = prevretcmd.ExecuteReader()
    If rdr.Read() Then
	dteReturned = rdr("DateReturned").ToString()
	testReturnNo.Text = rdr("ReturnNo").ToString()
	retNo = Convert.ToInt32(testReturnNo.Text.Replace("T", "")) + 1
	returnNo.Text = "T" + retNo.ToString().PadLeft(1, "0"c)
    End If
    rdr.Close()
Catch
    retNo = 1
    returnNo.Text = retNo.ToString()
End Try

You have 2 100% separate SQL queries here, and you mixing up getting the MAX number vs that of pulling one row.

Break this out into more bite sized steps.

This code should work:

Protected Sub Button1_Click(sender As Object, e As EventArgs)

    Dim retNo As Integer = 0
    Dim return_No As String = ""
    Dim dteReturned As String = ""

    Dim cmdSQL As New _
            SqlCommand("select MAX(returnNo) returnNo from Trials")

    Dim rstMAX As DataTable = MyRstP(cmdSQL)
    retNo = rstMAX.Rows(0)(0)

    cmdSQL.CommandText =
            "select DateReturned from Trials where ownerId=@ownerid"
    cmdSQL.Parameters.Add("@ownerid", SqlDbType.Int).Value = Session("rOwnerID")

    Dim rstTrial As DataRow = MyRstP(cmdSQL).Rows(0)
    dteReturned = rstTrial("DateReturned").ToString()
    testReturnNo.Text = retNo.ToString()
    retNo += 1
    returnNo.Text = "T" & retNo.ToString().PadLeft(6, "0")

End Sub

Public Function MyRstP(cmdSQL As SqlCommand) As DataTable

    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST4)
        Using (cmdSQL)
            cmdSQL.Connection = conn
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData

End Function

So, our first query gets the max value from the table.

Then our 2nd query gets the database row (date column) based on id value in session().

We get retNo, display it in a text box.

The we add + 1, and then display in the 2nd text box, padded out to 6 "0"s.

The result looks like this:

So, the max value found in the table was 4098, and we added + 1, and then placed the result with padding of "0"s into that 2nd text box.

Edit: Data saved is not a integer, but padded string

Ok, so the Max() query going to return a string, one padding with a letter, some "0"'s and then the number.

So, we have to modify our code like this:

    Dim rstMAX As DataTable = MyRstP(cmdSQL)
    Dim retNoText As String = ""
    retNoText = rstMAX.Rows(0)(0)
    retNo = retNoText.Substring(1) ' get all characters AFTER first letter skipped

So, we get/grab the max string, T000123, OR K000234 or whatever.

We then igonre the FIRST letter, and then add + 1

This means that you might have this

  A000123
  B000123

Well, then in a sense we have duplicate numbers, since our max query will return the B000123, stirp off the letter B, and then produce a number of 124.

We then format with a T in front.

 T000124

Note 100% sure if this is what you want, but the above small change will work this way.