0

I want to get a value from a SQL Server column ID and show it on label.text with help the of following code:

Sub getID()
    Dim selquery As String = ("select max(id)+1 from picture1")
    Dim command As New SqlCommand(selquery, con)
    con.Open()
    Label1.Text = (command.ExecuteScalar)
    con.Close()
End Sub

The scenario is to get maximum value from ID make it plus 1, and assign it to label.text, it works correctly when I have at least one record in my SQL Server table but if I have no record in my table picture1 then it shows error

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ghufran Ataie
  • 160
  • 2
  • 11
  • I get a little nervous when I see an ID field being incremented in a client application. ID fields are normally primary keys and should be non-null identity fields. If this is a single user app, it might be OK but it is a bad habit. – Mary Jun 07 '18 at 02:32

3 Answers3

1

You can leverage the ISNULL T-SQL function to deal with this:

SELECT ISNULL(MAX(id), 0) + 1 from picture1
NoAlias
  • 9,218
  • 2
  • 27
  • 46
0

If you have no records in your table, the max(id) part will return null, and you cannot +1 to a null.

To work around that problem use the COALESCE operator, like:

COALESCE(MAX(ID), 0) + 1

If there is a value returned from max(id) it will use that, otherwise it will return 0

JayV
  • 3,238
  • 2
  • 9
  • 14
0

The return value of command.ExecuteScalar is of type Object, so you have to check the value.

This should work:

Dim objResult As Object = command.ExecuteScalar
Dim intResult As Integer = If(TypeOf objResult Is Integer, DirectCast(objResult, Integer), 0)
Label1.Text = intResult.ToString

Also you should switch Option Strict On

MatSnow
  • 7,357
  • 3
  • 19
  • 31