Gridview set up from code behind

[Cineno]

I have a search box where users can enter a last name or a last name and first name in the format "lastname, firstname"

When the search is made, the search button's click event checks for the comma to determine if it's searching a whole name or just a last name.

It should then go to a results page with a GridView that populates based of a SQL query matching the last name, or lastname and firstname if both were entered.

Since the search could be two different queries, I figured the results page couldn't just have the GridView populated by a regular SqlDataSource, and would have to be populated with code behind to create query's conditions.

I set up the page's on load to populate the GridView based on the querystring:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connection As SqlConnection
Dim comd As SqlCommand
Dim connectionstr As String
Dim dtreader As SqlDataReader
Dim lastName As String
Dim firstName As String

lastName = Request.QueryString("lastName")
firstName = Request.QueryString("firstName")

connectionstr = ConfigurationManager.ConnectionStrings("dbConnectionString").ConnectionString.ToString()
connection = New SqlConnection(connectionstr)
comd = New SqlCommand()
comd.Connection = connection
connection.Open()
If firstName = "" Then
comd.CommandText = "SELECT [Emplid], [Lastname], [Firstname], [Dept], [Title], [SuperID], [HireDate] FROM [LenoxHillList] Where [lastname] like '" & lastName & "%' order by lastname,firstname"
Else
comd.CommandText = "SELECT [Emplid], [Lastname], [Firstname], [Dept], [Title], [SuperID], [HireDate] FROM [LenoxHillList] Where [lastname] like '" & lastName & "%' and [firstname] like '" & firstName & "%' order by lastname,firstname"
End If
dtreader = comd.ExecuteReader
GridView2.DataSource = dtreader
GridView2.DataBind()
connection.Close()
End Sub

That seems to work for the last name search, but the GridView can't do sorting or paging now.

So I guess I have two questions.

1: Is there a way to make a conditional SQLDataSource in the page, or do I need to use code behind for this?

2: When populating a GridView in code behind, what do you need to do to enable paging and sorting? Adding AllowPaging="True" gives me "The data source does not support server-side data paging". And AllowSorting="True" shows an error saying sorting wasn't handled.

Thanks for any help!

 

[jbenson001]

The datasource control is one of your issues, don't use them.
Those controls are only good for the simplest of queries and logic.

Write the data access code yourself in the code behind and bind to a datatable.

Also, instead of writing the SQL in the code-behind, write a stored procedure that takes in 2 parameters, first and last name, and construct the query in the stored procedure.(do not use Dynamic SQL)

Also, allowing the user to enter a format of "firstname, lastname" is not a good idea. You are assuming the enduser is smart and will understand this. Many end users will not understand it and it is not intuitive. Make 2 separate textboxes with validation controls to alert the user that they must at least enter the last name.
You have to make any page you write as "idiot proof" as possible.

 

[Cineno]

Thanks for the help. I'll see if I can get it working

 

[jbenson001]

No problem, good luck and post how things are going.