Display value from SQL Server on a VB.NET label

Question

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

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

score 1 · Accepted Answer · answered Jun 06 '18 at 14:55

1

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

SELECT ISNULL(MAX(id), 0) + 1 from picture1

answered Jun 06 '18 at 14:55

NoAlias

9,218
2
27
46

score 0 · Answer 2 · answered Jun 06 '18 at 14:55

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

MatSnow · Answer 3 · 2018-06-06T15:28:28.487

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

edited Jun 06 '18 at 15:28

answered Jun 06 '18 at 14:55

MatSnow

7,357
3
19
31

Display value from SQL Server on a VB.NET label

3 Answers3