Creating an SQLDB search using a stored procedure

Discussion in 'ASP .Net' started by Matthew Curiale, Feb 11, 2005.

  1. 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
    -------------------------------------------------------------------------------
    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.
     
    Matthew Curiale, Feb 11, 2005
    #1
    1. Advertising

  2. Matthew Curiale

    Guest

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

    Matthew
     
    , Feb 11, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. wilco
    Replies:
    2
    Views:
    419
    Xavier MT
    Aug 5, 2003
  2. Mike P
    Replies:
    0
    Views:
    3,313
    Mike P
    Jun 19, 2006
  3. CSINVA
    Replies:
    1
    Views:
    301
    Eliyahu Goldin
    Nov 4, 2007
  4. Mate
    Replies:
    2
    Views:
    1,316
    Gregory A. Beamer
    Jul 15, 2009
  5. Dmitri

    Update data on SQLDB from TextBox.

    Dmitri, Nov 8, 2003, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    126
    Dmitri
    Nov 8, 2003
Loading...

Share This Page