Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Multi - Term Search with Stored Procedure

Reply
Thread Tools

Multi - Term Search with Stored Procedure

 
 
MatiTuk MatiTuk is offline
Junior Member
Join Date: May 2011
Posts: 1
 
      05-03-2011
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!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Full Text Search query without stored procedure Mate ASP .Net 2 07-15-2009 02:26 PM
Search through data returned from stored procedure CSINVA ASP .Net 1 11-04-2007 09:17 AM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
Creating an SQLDB search using a stored procedure Matthew Curiale ASP .Net 1 02-11-2005 04:08 PM
how to search a database with a stored procedure? wilco ASP .Net 2 08-05-2003 02:50 PM



Advertisments