Member Avatar for josh_9

dbconn.Open()

    s = "Select Max(cheque_no) + 1 As NewValue From tblEntry"
    Dim cmd As SqlCommand = New SqlCommand(s, dbconn)
    Dim dr As SqlDataReader = cmd.ExecuteReader()

    If dr.HasRows Then
        dr.Read()
        If IsDBNull(dr("NewValue")) Then
            empId = 1
        Else
            empId = CInt(dr("NewValue")) + 1
        End If
    Else
        empId = 1
    End If
    dr.Close()

    Me.TextBox1.Text = empId
  • 2 Contributors
  • 2 Replies
  • 225 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by josh_9

Dani AI

Generated

Good to see marked it solved after asked for the error. A couple of common, easy-to-miss issues apply to this pattern and are worth calling out so future readers don't run into the same trap.

First, avoid incrementing the value twice (once in the SQL and again in code). Second, aggregate queries (MAX, SUM, etc.) always return a row even when the result is NULL, so checking HasRows alone can be misleading — you must test the column for DBNull. For a single scalar value the simplest and safest pattern is ExecuteScalar inside a Using block and a proper DBNull check:

Using cmd As New SqlCommand("SELECT MAX(cheque_no) FROM tblEntry", dbconn)
    Dim raw = cmd.ExecuteScalar()
    Dim nextCheque As Integer = If(raw Is Nothing OrElse raw Is DBNull.Value, 1, Convert.ToInt32(raw) + 1)
    TextBox1.Text = nextCheque.ToString()
End Using

If the application will be used by multiple clients at once, the MAX+1 approach can produce duplicate numbers under race conditions. Prefer letting the database generate the value (IDENTITY) or use a SQL Server SEQUENCE (NEXT VALUE FOR dbo.YourSeq) or a serialized counter updated inside a transaction with appropriate locking. Also always use Using blocks (or try/finally) so the connection/command/reader are disposed, and test for DBNull before any cast to avoid InvalidCastException.

Recommended Answers

All 2 Replies

Member Avatar for Reverend Jim

What error are you getting and on what line?

Member Avatar for josh_9

reverend Jim thanks for the respond :) but i get it.. thank you a lot

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.