Multi - Term Search with Stored Procedure

Discussion in '.NET' started by MatiTuk, May 3, 2011.

  1. MatiTuk

    MatiTuk

    Joined:
    May 3, 2011
    Messages:
    1
    I am wanting to setup a multi-term search and can be used with a stored procedure that I have created. I then want the results of the search to be posted back in a gridview or other table.

    Here is the stored procedure:

    USE [KAB]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[prSearch]
    -- Add the parameters for the stored procedure here

    --Organization Results

    @orgname VARCHAR(70) = NULL,
    @city NVARCHAR(20) = NULL,
    @medicaidid NVARCHAR(15) = NULL,
    @medicareid NVARCHAR(15) = NULL,
    @orgemail NVARCHAR(52) = NULL,
    @county NVARCHAR(52) = NULL,
    @labels NVARCHAR(52) = NULL,
    @orgnpi INT = NULL,

    -- Contact Results

    @fname NVARCHAR(max) = NULL,
    @lname NVARCHAR(max) = NULL,
    @phone NVARCHAR(50) = NULL,
    @email NVARCHAR(52) = NULL,
    @npi INT = NULL

    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @now DATETIME
    SET @now = GetDate()
    SELECT Org.orgname, Org.city, County.countyname, Org.email, OrgIdentifier.identifier, OrgIdentifier.idtype, Person.fname, Person.lname, OrgContact.phone,
    OrgContact.email AS cemail, OrgLabel.orglabel, PersonPhysician.npi
    FROM Org INNER JOIN
    OrgIdentifier ON Org.orgid = OrgIdentifier.orgid INNER JOIN
    OrgContact ON Org.orgid = OrgContact.orgid INNER JOIN
    Person ON OrgContact.personid = Person.personid INNER JOIN
    OrgLabels ON Org.orgid = OrgLabels.orgid INNER JOIN
    OrgLabel ON OrgLabels.orglabelid = OrgLabel.orglabelid INNER JOIN
    County ON Org.countyid = County.countyid INNER JOIN
    PersonPhysician ON OrgContact.personid = PersonPhysician.personid

    INSERT INTO Search (orgname, city, medicaidid, medicareid, orgemail, county,
    orgnpi, labels, fname, lname, phone, email, npi)
    VALUES (@orgname, @city, @medicaidid, @medicareid, @orgemail, @county,
    @orgnpi, @labels, @fname, @lname, @phone, @email, @npi)

    RETURN @@IDENTITY
    END

    I have created the search page here, but I keep getting hungup on getting my parameters setup to pass through to the stored procedure and then getting the results to post to the Grid View.

    Page Code:

    <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <h1>
    Search<br />
    <table style="width:100%;">
    <tr>
    <td class="style43" colspan="2">
    </td>
    <td class="style46">
    </td>
    <td class="style45" colspan="2">
    </td>
    </tr>
    <tr>
    <td class="style33" colspan="2">
    <span class="style32">Organization Specific:</td>
    </span>
    <td class="style47">
    &nbsp;</td>
    <td class="style31" colspan="2">
    Contact Specific:</td>
    </tr>
    <tr>
    <td class="style38">
    </td>
    <td class="style39">

    </td>
    <td class="style40">
    </td>
    <td class="style41">
    </td>
    <td class="style42">
    </td>
    </tr>
    <tr>
    <td class="style34">
    <asp:Label ID="lblOrganization" runat="server" CssClass="style2">Organization:</asp:Label>
    </td>
    <td class="style17">
    <asp:TextBox ID="txtOrg" runat="server" style="text-align:left" CssClass="style10"></asp:TextBox>

    </td>
    <td class="style28">
    </td>
    <td class="style24">
    <asp:Label ID="lblname" runat="server" CssClass="style2">Name:</asp:Label>
    </td>
    <td class="style25">
    <asp:TextBox ID="txtName" runat="server" style="text-align:left" CssClass="style10"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td class="style35">
    <asp:Label ID="lblCity" runat="server" CssClass="style2">City:</asp:Label>
    </td>
    <td class="style23">
    <asp:TextBox ID="txtCity" runat="server" style="text-align:left" CssClass="style10"></asp:TextBox>
    </td>
    <td class="style26">
    &nbsp;</td>
    <td class="style21">
    <asp:Label ID="lblphone" runat="server" CssClass="style2">Phone:</asp:Label>
    </td>
    <td class="style4">
    <asp:TextBox ID="txtPhone" runat="server" style="text-align:left" CssClass="style10"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td class="style34">
    <asp:Label ID="lblmedicaidid" runat="server" CssClass="style2"
    Text="Medicaid ID#:"></asp:Label>
    </td>
    <td class="style17">
    <asp:TextBox ID="txtmedicaidid" runat="server" style="text-align:left" CssClass="style10"></asp:TextBox>
    </td>
    <td class="style26">
    &nbsp;</td>
    <td class="style21">
    <asp:Label ID="lblCEmail" runat="server" CssClass="style2">Email:</asp:Label>
    </td>
    <td class="style4">
    <asp:TextBox ID="txtCemail" runat="server" style="text-align:left" CssClass="style10"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td class="style34">
    <asp:Label ID="lblmedicareid" runat="server" CssClass="style2"
    Text="Medicare ID#:"></asp:Label>
    </td>
    <td class="style17">
    <asp:TextBox ID="txtmedicareid" runat="server" style="text-align:left" CssClass="style10"></asp:TextBox>
    </td>
    <td class="style29">
    &nbsp;</td>
    <td class="style15">
    <asp:Label ID="lblCNPI" runat="server" CssClass="style2">NPI#:</asp:Label>
    </td>
    <td class="style16">
    <asp:TextBox ID="txtCNPI" runat="server" style="text-align:left" CssClass="style10"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td class="style36">
    <asp:Label ID="lblEmail" runat="server" CssClass="style2" Text="Email:"></asp:Label>
    </td>
    <td class="style19">
    <asp:TextBox ID="txtemail" runat="server" style="text-align:left" CssClass="style10"></asp:TextBox>
    </td>
    <td class="style26">
    &nbsp;</td>
    <td class="style21">
    &nbsp;</td>
    <td class="style4">
    &nbsp;</td>
    </tr>
    <tr>
    <td class="style37">
    <asp:Label ID="lblCounty" runat="server" CssClass="style2" Text="County:"></asp:Label>
    </td>
    <td class="style20">
    <asp:TextBox ID="txtcounty" runat="server" style="text-align:left"
    CssClass="style10"></asp:TextBox>
    </td>
    <td class="style30">
    &nbsp;</td>
    <td class="style12">
    &nbsp;</td>
    <td class="style13">
    &nbsp;</td>
    </tr>
    <tr>
    <td class="style37">
    <asp:Label ID="lblNPI" runat="server" CssClass="style2" Text="NPI#:"></asp:Label>
    </td>
    <td class="style20">
    <asp:TextBox ID="txtnpi" runat="server" style="text-align:left"
    CssClass="style10"></asp:TextBox>
    </td>
    <td class="style30">
    &nbsp;</td>
    <td class="style12">
    &nbsp;</td>
    <td class="style13">
    &nbsp;</td>
    </tr>
    <tr>
    <td class="style37">
    <asp:Label ID="lbllabel" runat="server" CssClass="style2" Text="Labels:"></asp:Label>
    </td>
    <td class="style20">
    <asp:TextBox ID="txtlabel" runat="server" style="text-align:left"
    CssClass="style10"></asp:TextBox>
    </td>
    <td class="style30">
    &nbsp;</td>
    <td class="style12">
    &nbsp;</td>
    <td class="style13">
    &nbsp;</td>
    </tr>
    <tr>
    <td class="style37">
    &nbsp;</td>
    <td class="style20">
    &nbsp;</td>
    <td class="style30">
    &nbsp;</td>
    <td class="style12">
    &nbsp;</td>
    <td class="style13">
    &nbsp;</td>
    </tr>
    <tr>
    <td class="style37" colspan="5">
    <asp:Button ID="btnSubmit" runat="server" Text="Submit" CssClass="btn" />

    </td>
    </tr>
    </table>
    </h1>
    <span class="style48">Search Results:</span><br />
    <asp:GridView ID="gvSearch" runat="server" AutoGenerateColumns="False"
    DataSourceID="SqlSearch" CellPadding="4" ForeColor="#333333"
    GridLines="None">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#E9E7E2" />
    <SortedAscendingHeaderStyle BackColor="#506C8C" />
    <SortedDescendingCellStyle BackColor="#FFFDF8" />
    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
    </asp:GridView>
    </asp:Content>

    Any help you can offer would be great. Thanks!
     
    MatiTuk, May 3, 2011
    #1
    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:
    420
    Xavier MT
    Aug 5, 2003
  2. Matthew Curiale
    Replies:
    1
    Views:
    374
    mattcur@gmail.com
    Feb 11, 2005
  3. Mike P
    Replies:
    0
    Views:
    3,317
    Mike P
    Jun 19, 2006
  4. CSINVA
    Replies:
    1
    Views:
    301
    Eliyahu Goldin
    Nov 4, 2007
  5. Mate
    Replies:
    2
    Views:
    1,316
    Gregory A. Beamer
    Jul 15, 2009
Loading...

Share This Page