CODEWITHSUNDEEP
sql-servervb.net
David Hall
David Hall

Reputation: 113

Trying to increment a number from SQL DB query

I have a routine that gets a MAX value from an INT column in the SQL DB, and is supposed to increment that number by '1'. The problem is, that when this is run, it seems the first time it works, but each time thereafter, the routine continues to return '1'. The value I am looking for is called maxrptnum. The routine follows:

 Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim conn As New SqlConnection("Data Source=server;Initial Catalog=forms;User ID=user;Password=password")
    Dim cmd As New SqlCommand

    cmd.CommandText = "select max(increment) from forma where rptnum like 'a-@inspinit-%'"
    cmd.Parameters.Clear()
    cmd.Parameters.Add("@inspinit", SqlDbType.Char).Value = inspinit.Text
    cmd.Connection = conn
    conn.Open()
    maxrptnum = cmd.ExecuteScalar() & String.Empty
    If maxrptnum = String.Empty Then
        maxrptnum = 1
        Me.rptnum.Text = ("A" + "-" + Me.inspinit.Text + "-" + maxrptnum)
    Else
        Me.rptnum.Text = ("A" + "-" + Me.inspinit.Text + "-" + (maxrptnum + 1))
    End If

    conn.Close()
    saverpt()
End Sub

The maxrptnum is declared as a string, but the DB type is int. This may be the issue, but not sure how to get around it.

Upvotes: 0

Views: 60

Answers (1)

Steve
Steve

Reputation: 216302

As explained in the comment above you should not generally do this operation. Concurrency on the table could get you in big troubles.
However you problem lies in the query and in the way in which you add your parameter placeholder. Putting it between single quotes transform everything in a literal string.

You should write

cmd.CommandText = @"select max(increment) 
                    from forma where rptnum like @inspinit"

and then, when you add the parameter,

cmd.Parameters.Add("@inspinit", SqlDbType.Char).Value = "a-" + inspinit.Text + "-%"

Finally the code to retrieve the value could be written using directly an integer and not transforming everything in a string

Dim maxrptnum As Integer
Dim result = cmd.ExecuteScalar() 
If result Is Nothing Then
    maxrptnum = 1
Else
    maxrptnum = Convert.ToInt32(result) + 1
End If
Me.rptnum.Text = ("A-" & Me.inspinit.Text & "-" & maxrptnum.ToString())

Upvotes: 1

Related Questions