Pass Sql Select result to a simple Label.

[Phillip Vong]

I'm new and trying to learn. This is in VB.NET 2. I have a simple page
with one Textbox1, one Label1 and one Button1. The on_click Button one goes
and get's a simple data from the SQL db with the parameter from Textbox1.
What do I add to this code below to say that Label1 equals the result of my
Select statement? CroftUser being the result.
Thanks!
Newbie
-------------------------------

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim ds As New SqlDataSource()

ds.ConnectionString =
ConfigurationManager.ConnectionStrings("CroftDBCon nectionString").ConnectionString

ds.SelectCommandType = SqlDataSourceCommandType.Text

ds.SelectCommand = "Select CroftUser From [User] Where (Password =
@Password)"

ds.SelectParameters.Add("Password", TextBox1.Text)

End Sub

[=?Utf-8?B?SmFzb24gVmVybWlsbGlvbg==?=]

Philip,

If you are trying to populate a control directly via code, I think it is
easier fetch data from the Database using a System.Data.SqlClient.SqlCommand
object instead of the SqlDataSource.

Below is the code for a web page called "SimpleBinding".

Hope this helps,
Jason Vermillion

' alias to the SqlClient namespace
Imports System.Data.SqlClient

Partial Class SandBox_SimpleBindingVB
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As SqlConnection = New SqlConnection()
Dim cmd As SqlCommand = New SqlCommand()
Dim dr As SqlDataReader

Label1.Text = ""

cn.ConnectionString =
ConfigurationManager.ConnectionStrings("CroftDBCon nectionString").ConnectionString
cmd.Connection = cn
cmd.CommandText = "Select CroftUser From [User] Where (Password =
@Password)"
cmd.Parameters.AddWithValue("@Password", Me.TextBox1.Text)

'Open the connection to the database
cn.Open()
' Execute the sql.
dr = cmd.ExecuteReader()
' Read all of the rows generated by the command (in this case only
one row).
Do While dr.Read()
Label1.Text = dr.Item("CroftUser").ToString()
Loop
' Close your connection to the DB.
dr.Close()
cn.Close()
End Sub
End Class

[Phillip Vong]

Jason, thanks SOOO much. That worked perfectly. Can you explain to me why
SqlClient is better in doing this than trying to create a SqlDataSource?

"Jason Vermillion" <Ja*************@discussions.microsoft.comwrote in
message news:9E**********************************@microsof t.com...

Philip,

If you are trying to populate a control directly via code, I think it is
easier fetch data from the Database using a
System.Data.SqlClient.SqlCommand
object instead of the SqlDataSource.

Below is the code for a web page called "SimpleBinding".

Hope this helps,
Jason Vermillion

' alias to the SqlClient namespace
Imports System.Data.SqlClient

Partial Class SandBox_SimpleBindingVB
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As SqlConnection = New SqlConnection()
Dim cmd As SqlCommand = New SqlCommand()
Dim dr As SqlDataReader

Label1.Text = ""

cn.ConnectionString =
ConfigurationManager.ConnectionStrings("CroftDBCon nectionString").ConnectionString
cmd.Connection = cn
cmd.CommandText = "Select CroftUser From [User] Where (Password =
@Password)"
cmd.Parameters.AddWithValue("@Password", Me.TextBox1.Text)

'Open the connection to the database
cn.Open()
' Execute the sql.
dr = cmd.ExecuteReader()
' Read all of the rows generated by the command (in this case only
one row).
Do While dr.Read()
Label1.Text = dr.Item("CroftUser").ToString()
Loop
' Close your connection to the DB.
dr.Close()
cn.Close()
End Sub
End Class

[=?Utf-8?B?SmFzb24gVmVybWlsbGlvbg==?=]

Phillip,

Jason, thanks SOOO much. That worked perfectly. Can you explain to me why
SqlClient is better in doing this than trying to create a SqlDataSource?

I would not say that using binding with the SqlDataSource is better or
worse, it just depends on what you are trying to do.

When you use a SqlDataSource and bound controls, you get a lot of database
functionality with only a few lines of code and without having to know much
about the way that the underlying Ado.Net works. This is especially true
when working with DataGrids, DataLists, and DataRepeaters. You get paging,
sorting, auto generating columns, and all kinds of other functionality that
would take a long time to code from scratch.

On the flip side, when you use the SqlDataSource (or any of the other data
source controls) you lose flexibility and a bit or performance that you get
when you work directly with the Data.SqlClient objects that are running under
the hood. When you start working with SqlDataSources, I think you'll find
that there are often some cases that come up when you find that the built-in
functionality is not adequate for you what you are trying to accomplish and
you might need to work directly with ado.net (SqlClient, etc.).

I would recommend at least knowing some of the basics of DataSets,
DataTables, DataViews, DataReaders, and DataAdapters.

Here are some links that might be helpful.
Live demos with source code showing hot to use SqlDataSources and Data bound
controls:
http://quickstarts.asp.net/QuickStar...a/default.aspx
overview of data binding
http://msdn2.microsoft.com/en-us/lib...59(VS.80).aspx
ado.net
http://msdn2.microsoft.com/en-us/library/e80y5yhx.aspx

Also, here is some code that just uses SqlDataSources and binding for your
original question. I think the trick was to wrap the User name Label in a
form view control.

Hope this helps,
Jason

Protected Sub cmdGetUserInfo_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles cmdGetUserInfo.Click
SqlDataSource1.Select(DataSourceSelectArguments.Em pty)
End Sub
<asp:TextBox ID="txtPwd" runat="server"></asp:TextBox>
<asp:Button ID="cmdGetUserInfo" runat="server" Text="Button" />
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate>
<asp:Label ID="lblUserName" runat="server"><%# Eval("CroftUser")
%></asp:Label>
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:CroftDBConnectionString %>"
ProviderName="<%$ ConnectionStrings:CroftDBConnectionString.Provider Name %>"
SelectCommand="Select CroftUser From [User] Where (Password = @Password)">
<SelectParameters>
<asp:ControlParameter ControlID="txtPwd" Name="Password"
PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>