CODEWITHSUNDEEP
asp.netsqlvb.net
Tommy
Tommy

Reputation:

SQL select return value to variable

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

Upvotes: 3

Views: 9013

Answers (3)

Michael Arnell
Michael Arnell

Reputation: 1028

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.

Upvotes: 4

Evernoob
Evernoob

Reputation: 5561

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?

Upvotes: -2

Mehrdad Afshari
Mehrdad Afshari

Reputation: 421968

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

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

Upvotes: 2

Related Questions