CODEWITHSUNDEEP
.netvb.net
Bruce Smith
Bruce Smith

Reputation: 27

Return string value from sql select command vb.net code behind

As a newbie I'm growing old trying to figure out how to return a string value from an sql select command with vb code behind. The program I have is below. Essentially what I'm trying to do is extract the value in the DisplayName field in table BuildFields (with WHERE conditions) and place it into the text field of a label within a formview template. It doesn't seem like it should be difficult and I'm sure I'm missing something. Does anybody have any ideas or see what I'm doing wrong? This isn't returning anything. Thanks.

Protected Sub FormView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles FormView1.DataBound

    Dim dt As New DataTable()
    Dim conCString As String = ConfigurationManager.ConnectionStrings("conCString").ConnectionString
    Dim lbl As Label = FormView1.FindControl("Label1")
    Dim sqlConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conCString").ConnectionString)
    Dim cmd As New SqlCommand

    cmd.CommandText = "SELECT [DisplayName] FROM [BuildFields] WHERE ([TableID] = N'Capacitors') AND (ColumnID = N'UserField01') ORDER BY [ColumnID]"
    cmd.CommandType = CommandType.Text
    cmd.Connection = SqlConnection

    Try
        SqlConnection.Open()
        Dim result As String

        result = cmd.ExecuteScalar()

        lbl.Text = result

        sqlConnection.Close()
    Catch ex As Exception

    End Try

End Sub

Upvotes: 1

Views: 6055

Answers (1)

Bjørn-Roger Kringsjå
Bjørn-Roger Kringsjå

Reputation: 10001

Turn Option strict ON now. Why? What will happen if/when ExecuteScalar returns a DBNull?

I bet that if you remove the try-catch block then you'll get this error message:

An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

Here's how your code should look like:

Dim result As Object
result = cmd.ExecuteScalar()
lbl.Text = If((TypeOf result Is String), CType(result, String), "(null)")

You should also use the Using keyword when dealing with disposable objects.

Using sqlConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("conCString").ConnectionString)
    Using cmd As New SqlCommand()
        'Code goes here...
    End Using
End Using

And you should never ever swallow exception like that. Always take proper action and/or re-throw the error.

Catch ex As Exception
    'Do something or else:
    Throw
End Try

Upvotes: 1

Related Questions