Search database returning results to gridview via sqldatareader

Question

I am trying to create a search page, this allows the admin to search through the entries in the database by certain criteria, such as province (like state but diff country)

Here is the code I have so far. The problem is that I am not getting any errors. But I am also not getting any results. The page just posts back and returns to the blank search page. The GridView does not display any results.

here is my code as it stands:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Partial Class administration_Search
    Inherits System.Web.UI.Page

    Protected Sub ProvinceButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ProvinceButton.Click
        'get the search string from text box
        Dim Search As String
        Search = ("%" & ProvinceButton.Text & "%")
        'if the string has value then continue with search
        If Len(Trim(Search)) > 0 Then
            Dim con As String = WebConfigurationManager.ConnectionStrings("fubar").ToString()
            'build sql string 
            Dim s As String
            s = ("SELECT id, surname, name FROM orders WHERE province like @strSearch")
            'declar connection
            Dim c As SqlConnection = New SqlConnection(con)
            'add command
            Dim x As New SqlCommand(s, c)
            'add parameter
            x.Parameters.AddWithValue("@strSearch", strSearch)
            c.Open()
            Dim r As SqlDataReader = x.ExecuteReader
            GV.DataSource = r
            GV.DataBind()
            c.Close()
        Else
            Province.Text = ("Please enter search terms")
        End If
    End Sub
End Class

On the .aspx page I have a textbox (id: province) and a button (id:submit)

Can anyone spot where I am going wrong. Its very frustrating when you dont get errors or results :0)

Thanks!

Answer 1

Your variable names don't match (Search vs. strSearch), but I suspect that's a typo. The bigger problem is that by putting the matching characters in the parameter, they are being quoted. Put them in the SQL statement itself.

Dim strSearch As String
strSearch = ProvinceButton.Text.Trim()

...

s = ("SELECT id, surname, name FROM orders WHERE province like '%' + @strSearch + '%'")

Answer 2

1) I would suggest that you examine the SQL that is being executed.

Put a breakpoint on the line c.Open(). When you get there, examine the SqlCommand x. See what the final version of the SELECT statement is. Copy and paste it into Management Studio, then run it to see if there is a syntax problem.

2) You might also try populating some of the SqlCommand properties. Set x.CommandType = CommandType.Text.

3) I would also suggest that you Close and Dispose of the SqlDataReader at the end, right before you close the Connection c. That won't solve this particular problem, but it is a good practice.

Answer 3

OK

Once i did the amends kindly pointed out by tvanfosson I still had problems;

I had accidentally typed: strSearch = ProvinceButton.Text.Trim() When I should have typed: strSearch = Province.Text.Trim()

Thanks a lot :0)

Answer 4

This is a wild shot in the dark, but does this variable need to have tick marks before the first % and after the last % since it's a string value?

Original:

Search = ("%" & ProvinceButton.Text & "%") 

Revised:

Search = ("'%" & ProvinceButton.Text & "%'")