Link to home
Start Free TrialLog in
Avatar of Nugs
Nugs

asked on

Search Function not working....

https://www.experts-exchange.com/questions/21377778/Advanced-search-page.html

Hi there guys... This is a continuation of a previous question... I am trying to addapt the code i found here:

http://www.asp101.com/samples/db_search_aspx.asp 

and i am having little success....

------------------------------------------------------------------------------------------------------------------------------------------
<script language="VB" runat="server">

      Sub btnSearch_OnClick(sender as Object, e as EventArgs)
            Dim strSearch     As String
            Dim strSQLQuery   As String

            ' Get Search
            strSearch = txtSearch.Text
            
            ' If there's nothing to search for then don't search
            ' o/w build our SQL Query and execute it.
            If Len(Trim(strSearch)) > 0 Then
                  ' Set up our connection.
                  Dim oleConn As New OleDb.OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_Conn_Intranet"))

                  ' Set up our SQL query text.
                  strSQLQuery = "SELECT Fld_Title + ' ' + Fld_Body AS Title, Body " _
                        & "FROM TBL_Records " _
                        & "WHERE Fld_Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
                        & "OR Fld_Body LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
                        & "ORDER BY Fld_Title;"

                  ' Create new command object passing it our SQL query
                  ' and telling it which connection to use.
                  Dim Comm As OleDbCommand
                  Comm = New OleDbCommand(strSQLQuery, oleConn)

                  ' Get a DataSet to bind the DataGrid to
            Dim DA As New OleDbDataAdapter()
                  DA.selectCommand=Comm
                  
            Dim DS As DataSet                  
                  DS = New DataSet()
                  DA.Fill(DS)

                  ' DataBind DG to DS
                  dgPaging.DataSource = DS
                  dgPaging.DataBind()

                  OleConn.Close()
            Else
                  txtSearch.Text = "Enter Search Here"
            End If
      End Sub

</script>
-------------------------------------------------------------------------------------------------------------------------------------------------

The page loads, but when i try and do a search i get this error:

---------------------------------------------------------------------------------------------------------------------------------------------------
Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Source Error:

Line 35:             Dim DS As DataSet                  
Line 36:                   DS = New DataSet()
Line 37:                   DA.Fill(DS)
Line 38:
Line 39:                   ' DataBind DG to DS
----------------------------------------------------------------------------------------------------------------------------------------------

Can you guys help me get this search script running... Please?
ASKER CERTIFIED SOLUTION
Avatar of Havagan
Havagan

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nugs
Nugs

ASKER

Ok yeah it was my SQL... I guess the code i was usiong was working with SQl Server or something a Access does not support some of the characters... I altered it like so and it is working..

---------------------------------------------------------------------------------------------------------------------------------------
                  strSQLQuery = "SELECT Fld_Title, Fld_Body " _
                        & "FROM TBL_Records " _
                        & "WHERE Fld_Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
                        & "OR Fld_Body LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
                        & "ORDER BY Fld_Title;"
---------------------------------------------------------------------------------------------------------------------------------------

Thanks for putting my on the right track...