Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Creating an SQLDB search using a stored procedure

Reply
Thread Tools

Creating an SQLDB search using a stored procedure

 
 
Matthew Curiale
Guest
Posts: n/a
 
      02-11-2005
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=(d b)"
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=(d b)"
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
-------------------------------------------------------------------------------
When the search button is pressed, the repeater comes back blank.
Also, how would a DSN connection be setup to a remote database?
Thanks in advance.
 
Reply With Quote
 
 
 
 
mattcur@gmail.com
Guest
Posts: n/a
 
      02-11-2005
Sorry, the .open( ) and .close( ) calls are outside of the if block

Matthew

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Full Text Search query without stored procedure Mate ASP .Net 2 07-15-2009 02:26 PM
Search through data returned from stored procedure CSINVA ASP .Net 1 11-04-2007 09:17 AM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
Update data on SQLDB from TextBox. Dmitri ASP .Net Web Controls 0 11-08-2003 06:54 PM
how to search a database with a stored procedure? wilco ASP .Net 2 08-05-2003 02:50 PM



Advertisments