CODEWITHSUNDEEP
asp.netsqlvb.netdatabase-connection
user356808
user356808

Reputation:

Populate label from single field in sql server

I have a column in a table that contains a message and I want this message to be displayed in a label. Here is my code which currently doesn't populate the label.

Protected conString As String = ConfigurationManager.AppSettings("sqldirectory")
Dim cnn As New SqlConnection(conString)
    Dim cmd As New SqlCommand("select message from [database].[dbo].[table]")
    Dim dr As SqlDataReader

    Try
        cnn.Open()
        dr = cmd.ExecuteReader()
        lblMsg.Text = dr(0).ToString
    Catch ex As Exception
        dr = Nothing
    Finally
        cnn.Close()
    End Try

Upvotes: 1

Views: 2259

Answers (2)

Neil Watson
Neil Watson

Reputation: 2831

I came across this by accident whilst searching for something else. There is an easier way to do this.

I would be tempted to create some common app_code to do this. Create your app_code folder in your project and and a common.vb file with the following code:

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data

Namespace GetDBResult
Public Class DB
    Public Shared Function GetRecord(sql As String) As String
        Dim result As String
        Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("YourConnectionString").ToString)
        connection.Open()

        Dim command As New SqlCommand(sql, connection)

        Try
            If command.ExecuteScalar IsNot System.DBNull.Value Then
                result = command.ExecuteScalar
            Else
                result = ""
            End If
        Catch ex As Exception
            Throw ex

        Finally
            connection.Close()
        End Try

        Return result
    End Function
    End Class
    End Namespace

From there you will be able to call the function from any vb code, making it very easy to get a field from a database to assign it to a label. As follows:

    Dim sql as string = "your sql query which should return one row and column"
    label1.text = DB.GetRecord(sql)

Saves a lot of re-typing.

Oh, remember to import your code in any vb you wish to use your app_code

    Imports GetDBResult

Many thanks, Neil

Upvotes: 0

user356808
user356808

Reputation:

Ok, after playing around, I came up with this which works

Protected conString As String = ConfigurationManager.AppSettings("sqldirectory")
Dim cnn As New SqlConnection(conString)
Dim cmd As New SqlCommand("select message from [database].[dbo].[table]", cnn)
Dim dr As SqlDataReader
Dim msg As String
Try
    cnn.Open()
    dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
    While dr.Read()
        msg = dr("message")
        lblMsg.Text = msg
    End While
Catch ex As Exception
    dr = Nothing
Finally
    cnn.Close()
End Try

Upvotes: 1

Related Questions