Multi - Term Search with Stored Procedure


Joined
May 3, 2011
Messages
1
Reaction score
0
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!
 
Ad

Advertisements


Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top