CODEWITHSUNDEEP
sql-servervb.net
Ghufran Ataie
Ghufran Ataie

Reputation: 190

Display value from SQL Server on a VB.NET label

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

Upvotes: 0

Views: 1015

Answers (3)

MatSnow
MatSnow

Reputation: 7517

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

Upvotes: 0

NoAlias
NoAlias

Reputation: 9193

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

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

Upvotes: 1

JayV
JayV

Reputation: 3271

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

Upvotes: 0

Related Questions