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
– Mary, Commented Jun 7, 2018 at 2:32

NoAlias · Accepted Answer · 2018-06-06 14:55:23Z

1

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

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

answered Jun 6, 2018 at 14:55

NoAlias

9,2032 gold badges30 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JayV · Accepted Answer · 2018-06-06 14:55:16Z

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

answered Jun 6, 2018 at 14:55

JayV

3,2712 gold badges11 silver badges16 bronze badges

Comments

MatSnow · Accepted Answer · 2018-06-06 15:28:28Z

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 6, 2018 at 15:28

answered Jun 6, 2018 at 14:55

MatSnow

7,5573 gold badges22 silver badges32 bronze badges

Collectives™ on Stack Overflow

Display value from SQL Server on a VB.NET label

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related