SQL select return value to variable

Question

working with: ASP.net using VB.net connecting to MS SQL Server

What I'm trying to do is take the result of a SQL select query and place that in a string variable so it can be used in things like a textbox or label. code so far that doesn't work...

Imports System.Data.SqlClient

Partial Class dev_Default Inherits System.Web.UI.Page

Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load


    Dim cnPodaci As New SqlConnection
    cnPodaci.ConnectionString = "Data Source=<server>;Initial Catalog=<DB>;User ID=<UserName>;Password=<Password>"
    cnPodaci.Open()
    Dim cm As New SqlCommand
    cm.CommandText = "SELECT * FROM tbl1"
    cm.Connection = cnPodaci
    Dim dr As SqlDataReader
    dr = cm.ExecuteReader

    TextBox1.Text = dr.GetString(0)


    cnPodaci.Close()

End Sub

End Class

Answer 1

If the query is supposed to return a single value, you can simply use the ExecuteScalar method:

 TextBox1.Text = DirectCast(cm.ExecuteScalar(), String)

Answer 2

The problem is that SELECT queries will return a dataset, or at least a row from a dataset, not a string.

Do you absolutely need the entire result set as a string? Or can what you're trying to do be achieved by referencing a point in an array or dataset?

Answer 3

Although you have executed the query by calling "ExecuteReader" on the command, what is actually returned is an object (a DataReader) that will allow you to iterate over any query results. To do this you must call the "Read" method on the DataReader (this could be called multiple times in the clause of a "while" loop). Modifying your code to something like this should work:

If dr.Read() Then
    TextBox1.Text = dr.GetString(0)
End If

However, bear in mind that this will only work if the first field returned by your query is a string, otherwise a cast exception may be thrown.