How can I bind to an SqlDataSource from Code

My code behind file builds a select statement and I would like to fill an SqlDataSource control with it. Can some show me an example of how I might do that? Maybe something like this?

Me.SqlDataSourceSearchResult.ConnectionString ="ConnectStr"Me.SqlDataSourceSearchResult.SelectCommand ="SelectStatement"

gvSearchResult.DataSource =

Me.SqlDataSourceSearchResult

gvSearchResult.DataBind()

Hello Jackxxx,

If you are going to be doing this in code-behind, I would not bother with the SqlDataSource control. I would bind the data directly to the destination control as follows: -

Dim conn As New SqlConnection("YourConnectionString")
Dim command As New SqlCommand("SELECT * FROM YourTable", conn)
Dim da As New SqlDataAdapter(command)
Dim tblData As New DataTable
da.Fill(tblData)
conn.close()

gvSearchResult.DataSource = tblData
gvSearchResult.DataBind()

Also add the following 2 lines at the top of the code behind if they are not there already: -

Imports System.Data.SqlClient
Imports System.Data

Kind regards

Scotty

|||

Scotty,

I was trying to use the sqldatasource so that I could some how use its built in sort functionality.

|||

Hello Jackxxx,

I see now. In that case, do the following after populating tblData: -

Dim dvData as new DataView(tblData)

dvData.Sort = "MyField DESC" ' you can also apply filtering on a DataView

mygrid.DataSource = dvData ' binds to the DataView, not the DataTable

mygrid.DataBind()

Kind regards

Scotty

|||

I'm really hoping to find a way to bind to the SqlDataSource so that I can take advantage of sorting and paging for multiple fields without all the extra coding.

There must be a way.

|||

Hi Jack,

Suppose your SqlDataSource returns a DataSet, you can

mygrid.DataSource = sqlDatasource.Select()
mygrid.DataBind()

However, if you need to take advantage of sorting and paging, I suggest you bind with the designer.