How to take data from aspx page to stored procedure?

Question

I have to create an RDLC report in asp.net. For which I am writing a stored procedure using different scalar functions. I have a GUI from which I have to select a criteria and collect data according to that criteria. The GUI is here: enter image description here I have implemented all cases like "All Staff", "All services", etc but the problem comes when I have to select specific records from list box and bring data according to that. For example I select specfic Staff records from list and my report should display the records only having that selected records. How can I handle this? I mean when I select some records from listbox, how can I take these records to the where clause of my stored procedure and how to use it there?

I am using SQL SERVER 2008. My Stored Procedure is:

USE [PC_Rewrite]

GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spGetClients] ( @orderBy varchar(50) )

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

-- Insert statements for procedure here
IF(@orderBy = 'Consumer Name')
BEGIN
    SELECT c.Id, c.LastName, c.FirstName, c.MiddleInit, 
    c.DateOfBirth, dbo.GetAge(c.DateOfBirth, GETDATE()) AS Age, c.Sex, cs.Status, ca.Address, co.Phone, 
    dbo.GetEthnicity(c.Id) AS Ethnicity, dbo.GetDevelopmentalDisabilities(c.Id) AS Disabilities, 
    dbo.GetClientStaffContacts(c.Id) AS Staff, dbo.GetClientContacts(c.Id) AS Contact, 
    dbo.GetClientInsuranceProviders(c.Id) AS InsuranceProvider FROM Client c 
    LEFT OUTER JOIN ClientStatus cs ON cs.Id = c.StatusId  
    LEFT OUTER JOIN(        
        SELECT ca.ParentEntityId, ca.Address
        FROM ContactAddress ca
        INNER JOIN EntityName en ON en.Id = ca.EntityNameId AND en.Name = 'Client' 
        INNER JOIN GeneralLookup gl ON ca.glAddressTypeId = gl.Id AND gl.LookupItem = 'Primary'    
    ) ca ON c.Id = ca.ParentEntityId 
    LEFT OUTER JOIN(        
        SELECT co.ParentEntityId, co.ContactData Phone
        FROM ContactOther co
        INNER JOIN EntityName en ON en.Id = co.EntityNameId AND en.Name = 'Client' 
        INNER JOIN GeneralLookup gl ON co.glContactTypeId = gl.Id AND gl.LookupItem = 'Home'    
    ) co ON c.Id = co.ParentEntityId 
    ORDER BY c.LastName, c.FirstName, c.MiddleInit
END

ELSE IF(@orderBy = 'Consumer Address')
BEGIN
    SELECT c.Id, c.LastName, c.FirstName, c.MiddleInit, 
    c.DateOfBirth, dbo.GetAge(c.DateOfBirth, GETDATE()) AS Age, c.Sex, cs.Status, ca.Address, co.Phone, 
    dbo.GetEthnicity(c.Id) AS Ethnicity, dbo.GetDevelopmentalDisabilities(c.Id) AS Disabilities, 
    dbo.GetClientStaffContacts(c.Id) AS Staff, dbo.GetClientContacts(c.Id) AS Contact, 
    dbo.GetClientInsuranceProviders(c.Id) AS InsuranceProvider FROM Client c 
    LEFT OUTER JOIN ClientStatus cs ON cs.Id = c.StatusId  
    LEFT OUTER JOIN(        
        SELECT ca.ParentEntityId, ca.Address
        FROM ContactAddress ca
        INNER JOIN EntityName en ON en.Id = ca.EntityNameId AND en.Name = 'Client' 
        INNER JOIN GeneralLookup gl ON ca.glAddressTypeId = gl.Id AND gl.LookupItem = 'Primary'    
    ) ca on c.Id = ca.ParentEntityId 
    LEFT OUTER JOIN(        
        SELECT co.ParentEntityId, co.ContactData Phone
        FROM ContactOther co
        INNER JOIN EntityName en ON en.Id = co.EntityNameId AND en.Name = 'Client' 
        INNER JOIN GeneralLookup gl ON co.glContactTypeId = gl.Id AND gl.LookupItem = 'Home'    
    ) co ON c.Id = co.ParentEntityId 
    ORDER BY ca.Address
END

END

Any help would be appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 11:47

1

if your stored procedure looks something like this:

Create PROCEDURE [dbo].[sp]
@ID int
AS
BEGIN
    Select ID, Display, GroupID           
    From SystemTypes
    Where ID = @ID
END

then you can set the ID parameter like this:

SqlCommand command = new SqlCommand("sp",connection);
command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@ID, SqlDbType.Int).Value = **VALUE**;
conn.open();

SqlDataReader reader = command.ExecuteReader();

if you want to use something like a comma seperated values string.. I found this link:

http://www.outsightinteractive.com/blog/comma_delimited_list_as_stored_procedure_parameter

https://web.archive.org/web/20211020153409/https://www.4guysfromrolla.com/webtech/031004-1.shtml

Passing List<> to SQL Stored Procedure

edited May 23 '17 at 11:47

Community

1
1

answered May 17 '11 at 05:54

Mithir

2,355
2
25
37

I think this [link](http://www.outsightinteractive.com/blog/comma_delimited_list_as_stored_procedure_parameter) can help me: – asma May 17 '11 at 08:08
First and secod links are great ... These are the same what I was looking for. Thanks a LOT Mithir. – asma Jun 02 '11 at 07:31

score 0 · Answer 2 · answered May 17 '11 at 05:38

Does your stored procedure already exist? If so, are there parameters you have to use? Are you free to change the stored procedure as you see fit?

There are a number of options on how you can call the stored procedure. Here are two: you can create an SqlCommand object and set it to use the stored procedure, then create SqlParameters for the various clauses in your where clause. Eg A parameter for each of the options on your shown screen. In the case of the list box, you may pass in a comma delimitered string but that depends on your db schema.

Another method is to use Linq To Sql and drag-drop the sproc onto the designer and have it create a method for you, then call it with the method parameters generated.

Sorry to not be more specific but it is affected by how stored procedure works.

I can send a comma separated list of listbox selected items to the stored procedure as a parameter, but the problem is, how can I use that string in where clause of my SP. — asma, May 17 '11 at 06:16

score 0 · Answer 3 · answered May 17 '11 at 06:34

0

i think you want to pass your search criteria to your store procedure right? use something like this..

//in code behind

CommandType.StoredProcedure;

//pass each parameter for your criteria like below Command.Parameters.Add("@ID, SqlDbType.string).Value = staff;
Command.Parameters.Add("@ID, SqlDbType.string).Value = service;

//now in the store procedure declare sql as varchar(200) set sql = "select * from tablename"

//check parameters are blank or has a value if(@paraname1 != "") set sql = sql + "where...parameter1"; end if

if(@paraname2 != "") set sql = sql + "where...parameter2"; end if

this is what you were looking for?

answered May 17 '11 at 06:34

Shaishav7

139
1
11

I can pass the search criteria easily to SP. But I have no idea how to use that criteria in where clause. I will pass a string like "abc,def,ghi..." something like this. How can I use it in where clause. Like where Staff.Name = 'abc' or Staff.Name = 'def' or Staff.Name = 'ghi' – asma May 17 '11 at 06:54
then pls find my another answer// – Shaishav7 May 17 '11 at 07:04

Shaishav7 · Answer 4 · 2011-05-17T08:10:13.767

set @query = 'select Resume.ResumeID,Resume.Name,Resume.DepartmentID,Department.DepartmentName, Resume.CurrentCompany ,Resume.CurrentLocation ,Resume.Phone,Resume.Email, Resume.KeySkills,Resume.TotalExpYears,Resume.TotalExpMonths,Resume.CandidateTextResume,Resume.ResumeStatus, Resume.IsActive from [Resume] inner join [Department] on [Resume].DepartmentID=[Department].DepartmentID where [Resume].IsActive =1 '

if(@DepartmentName != '')
begin 
        select  @query = @query + ' And [Department].DepartmentName like ''%'  + @DepartmentName + '%'''
end  

if(@TotalExpYears != '')
begin 
        select  @query = @query + ' And TotalExpYears like ''%'  + @TotalExpYears + '%'''
end  

if(@TotalExpMonths != '')
begin 
        select  @query = @query + ' And TotalExpMonths like ''%'  + @TotalExpMonths + '%'''
end

if(@KeySkills != '')
begin 
        select  @query = @query + ' And KeySkills like ''%'  + @KeySkills + '%'''
end

if(@CurrentLocation != '')
begin 
        select  @query = @query + ' And CurrentLocation like ''%'  + @CurrentLocation + '%'''
end

if(@CandidateTextResume != '')
begin 
        select  @query = @query + ' And CandidateTextResume like ''%'  + @CandidateTextResume + '%'''
end

if(@CurrentCompany != '')
begin 
        select  @query = @query + ' And CurrentCompany like ''%'  + @CurrentCompany + '%'''
end
if(@ResumeStatus != '')
begin
    select  @query = @query + ' And ResumeStatus like ''%'  + @ResumeStatus + '%'''
end

============== @strId as varchar(100)//strId is comma seperated ids 'select * FROM tblSchool where intSchoolId IN ('+ @strId +')'

================= Now is it fine?

I'm not getting that how is it fulfilling my requirement? I mean where is the comma separated string that I am sending as a parameter to the SP? — asma, May 17 '11 at 07:52

How to take data from aspx page to stored procedure?

4 Answers4