Author

asim jan
posted 10 posts
since Sep 21, 2011
from

Creating a custom search stored procedure

Posted on: 10 Feb 2012

hi i am building a online book store for which i want to provide search functionality
there is a text box a dropdown n search button

text box is used to input search string
drop down is used to select by auhtor/by category/ by title

i am able to search the bookdetaisl table thorugh a string based search for whihc i get the string from text box.. returning results irrespective of the value from drop down list..
i pass three parameters to stored procedure comapring the three values from the same textbox with the three columns in my table i.e author,bookname,category

this is general stored procedure where the string is taken from search bar it has nothign to do whether
any value from dropdown is selected or not..

USE [Book_Store]
GO
/****** Object: StoredProcedure [dbo].[searchbook] Script Date: 02/10/2012 21:40:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[searchbook]
@book_name varchar(40) = NULL,
@author varchar(40) = NULL,
@category varchar(40) = NULL

AS
SELECT book_name,
author ,
category

FROM book_details
WHERE (@book_name IS NULL OR book_name LIKE '%' + @book_name + '%')
or (@author IS NULL OR author LIKE '%' + @author + '%')
or (@category IS NULL OR category LIKE '%' + @category + '%')

for allowing by author search / by book title search / by category search.
we will adopt following stategy
first select value from drop down whether by author/ by category / by title
then display accordingly
eg if drop down is by author
we show only those books titles that exist in the table for thatn authr

similarly if dropdown is by category
we show only those books titles that exist in the table for that category column

please let me know how to make it work
this is my snapshot

Pravin Ghadge
posted 367 posts
since Jun 23, 2010
from

Re: creating a custom search stored procedure

Posted on: 10 Feb 2012

Accepted Answer

Hi Asim,

here u have to use if condition in ur sp. & pass 1 extra parameter in code as @Search

ALTER PROCEDURE [dbo].[searchbook]
    @book_name varchar(40) = NULL,
    @author varchar(40) = NULL,
    @category varchar(40) = NULL
     @Search varchar(40)=null

AS

if @Search='book_name'
SELECT book_name,
       author ,
       category

FROM book_details
WHERE (@book_name IS NULL OR book_name LIKE '%' + @book_name + '%')

else if
@Search='author'
SELECT book_name,
       author ,
       category

FROM book_details
WHERE (@author IS NULL OR author LIKE '%' + @author + '%')

else if
@category='category'
SELECT book_name,
       author ,
       category

FROM book_details
WHERE (@category IS NULL OR category LIKE '%' + @category + '%')

asim jan
posted 10 posts
since Sep 21, 2011
from

Re: Creating a custom search stored procedure

Posted on: 15 Apr 2012

ss.rar

this works perfectly..
i am displaying search results in a repeator
and the problem i am facing is that book image is displayed on top with details below the image
i want to display the image on the side..
kindly help
i am pasting code and attatching screen shot
thanks
in advance

<asp:Repeater runat="server" id="Repeater_search">

<itemtemplate>

<br />

<asp:Image ID="Image1" runat="server" Height="200" Width="145" ImageUrl='<%#Eval("Book_Image")%>' />

<b><br /></b>

<b><%# DataBinder.Eval(Container.DataItem, "Book_Name") %></b>

<br>Author: <%# DataBinder.Eval(Container.DataItem,

"Book_Author", "{0:d}") %>

   

<br>Publisher: <%# DataBinder.Eval(Container.DataItem,

"Book_Publisher", "{0:d}") %>

   <br />

</itemtemplate>

<separatortemplate>

<hr>

</separatortemplate>

</asp:Repeater></td>

Satyapriya Nayak
posted 2264 posts
since Mar 24, 2010
from

Re: Creating a custom search stored procedure

Posted on: 15 Apr 2012

Hi Asim,

Try this...

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="WebApplication13.WebForm2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">

    <asp:Repeater runat="server" id="Repeater_search">

    <ItemTemplate>
                <table border="1">
                <tr></tr>
                <tr>
                <td>
                <b><%# DataBinder.Eval(Container.DataItem, "Book_Name") %></b><br>Author: <%# DataBinder.Eval(Container.DataItem, "Book_Author", "{0:d}") %>
                <br>Publisher: <%# DataBinder.Eval(Container.DataItem,"Book_Publisher", "{0:d}") %>
    <br />
                </td>
                <td> <asp:Image ID="Image1" runat="server" Height="200" Width="145" ImageUrl='<%#Eval("Book_Image")%>' /></td>
                </tr>
                </table>
                </ItemTemplate>

    </asp:Repeater>


    </form>
</body>
</html>

Thanks
If this post helps you mark it as answer