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.

how can I add InsertCommand in SqlDataSource?

I need to add an 'InsertCommand' to my query via sqldatasource, but i cannot see this option, i only have the 'order', 'where' and 'advanced' option, could you please advice?

Hi,

VB is great :) you can add and you insertcommand in code behind for example:

Private Sub Page_Load(ByVal sender As Object, ByVal e As eventArgs) Handles Page.Load

SqlDataSource1.InsertCommand.Add("Cell_Name", System.CodeType.String, TextBox.Text)

End Sub

Thats it

Hope it helps

------------------------------

Please do not forget to mark as "Answered" the answer which helped you. Thanks

|||

It seems that you want to add an InsertCommand to your SQLDatasource dynamically. Below is the code you can use to create an insert command for an existing sql datasource.

<your sql datasource id>.InsertCommand = "<either your insert query or stored procedure name>"
<your sql datasource id>.InsertCommandType = SqlDataSourceCommandType.StoredProcedure or SqlDataSourceCommandType.Text

You can add the command parameters using <your sql datasource id>.InsertParameters.Add method.

To call the insert method you can to use <your sql datasource id>.Insert().

Hope this will help.

|||

i have tried using this code but the 'SqlDataSource1' and 'System.CodeType' are not recognised; says its not declared.

could you please advice?

|||

Hi,

did you added SqlDataSource control in your aspx page ?

Regards

|||

Yes, and i used the same ID name.