Creating an SQLDB search using a stored procedure

[Matthew Curiale]

I am trying to create a search page for a database that will display
in a repeater, using a stored procedure. The page_load is fine, and
calls/displays all rows from the db just fine. When I input something
in the search field to display more specific results, the repeater
displays blank. Here are the page_load and btnSearch_click subs:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim CE_DSN As String =
"server=(server);uid=(uid);pwd=(pword);database=(db)"
Dim connection As New SqlConnection(CE_DSN)
Dim cmdClients As New SqlCommand("stored_proc", connection)

If Not IsPostBack Then
connection.Open()
Dim prmOrderClient As New SqlParameter
cmdClients.CommandType = CommandType.StoredProcedure

With prmOrderClient
.ParameterName = "@order_by"
.SqlDbType = SqlDbType.NVarChar
.Value = " comp.companyName asc"
End With
Dim prmSearchClient As New SqlParameter
With prmSearchClient
.ParameterName = "@search"
.SqlDbType = SqlDbType.NVarChar
.Value = ""
End With

cmdClients.Parameters.Add(prmOrderClient)
cmdClients.Parameters.Add(prmSearchClient)

Dim daclients As New SqlDataAdapter(cmdClients)
Dim dsclients As New DataSet

daclients.Fill(dsclients, "stored_proc")

rpClientList.DataSource = dsclients.Tables("stored_proc")
rpClientList.DataBind()
connection.Close()
End If
End Sub
-------------------------------------------------------------------------------
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnSearch.Click
Dim CE_DSN As String =
"server=(server);uid=(uid);pwd=(pword);database=(db)"
Dim connection As New SqlConnection(CE_DSN)
Dim cmdClients As New SqlCommand("stored_proc", connection)
Dim prmOrderClient As New SqlParameter
Dim prmSearchClient As New SqlParameter
Dim sql_search As String = ""
'Dim sql_orderby As String
If Len(Trim(txtClient.Text)) > 0 Then
connection.Open()
sql_search = " comp.companyName like '%" & txtClient.Text
& "%'" ' or domain_id.domain like '%" & txtClient.Text & "%' "
With prmOrderClient
.ParameterName = "@order_by"
.SqlDbType = SqlDbType.NVarChar
If rbClientAsc.Checked Then
.Value = " comp.companyName ASC"
Else
.Value = " comp.companyName DESC"
End If
End With
With prmSearchClient
.ParameterName = "@search"
.SqlDbType = SqlDbType.NVarChar
.Value = sql_search
End With
cmdClients.CommandType = CommandType.StoredProcedure
cmdClients.Parameters.Add(prmOrderClient)
cmdClients.Parameters.Add(prmSearchClient)
Dim daclients As New SqlDataAdapter(cmdClients)
Dim dsclients As New DataSet
daclients.Fill(dsclients, "stored_proc")
rpClientList.DataSource = dsclients
rpClientList.DataBind()
connection.Close()
Else : txtClient.Text = "Enter criteria here"
End If
End Sub

 

[mattcur]

Sorry, the .open( ) and .close( ) calls are outside of the if block

Matthew