?
Solved

Optional list items to stored proc

Posted on 2004-09-06
1
Medium Priority
?
223 Views
Last Modified: 2010-04-15
I'm sending 6 or fewer list item values to a stored procedure for a wine website . The number of values sent will be decided on how specific a result set the user wants. This code works if only one item is selected. However, if a combination of items are selected and sent to the stored proc, all results from each item are displayed.

For example, if I enter Sangiovese for the category, and 1999 for the year...then I will get the correct results, but also all items Sangiovesse regardless of year. It sounds like I may need to perform a self join to filter out what I don't need. Is there a simpler solution?

<form runat="server">
                 <p align="center">                
                 <div align="center">
  <asp:DropDownList id="category" runat="server">
        <asp:ListItem Text=""/>    
         <asp:ListItem Text="Arneis"/>      
          <asp:ListItem Text="Barbera"/>      
          <asp:ListItem Text="Bordeaux"/>      
          <asp:ListItem Text="Cabernet Sauvignon"/>      
       ...  
          <asp:ListItem Text="Shiraz"/>    
          <asp:ListItem Text="Sparkling Blend "/>    
          <asp:ListItem Text="Sweet Blend"/>    
          <asp:ListItem Text="Syrah"/>    
          <asp:ListItem Text="Tempranillo"/>    
          <asp:ListItem Text="White"/>    
          <asp:ListItem Text="White Blend"/>    
          <asp:ListItem Text="Viognier"/>    
          <asp:ListItem Text="Zinfandel"/>    
  </asp:DropDownList>

<asp:DropDownList ID="year" runat="server">
    <asp:ListItem Text=""/>  
    <asp:ListItem Text="1995"  />  
...
___________________________________________________________________-

}

void makeselection_Click(Object sender, System.EventArgs e) {
     string col;
     string cat;
     string yea;
     string reg;
     string cou;
     string app;
     
     col = color.SelectedItem.Text;
          if (col!="") {
          Session["color"] = color.SelectedItem.Text;
          }
          else {
          Session["color"] = "";
          }
         
     cat = category.SelectedItem.Text;    
          if (cat!="") {
          Session["category"] = category.SelectedItem.Text;
          }
          else {
          Session["category"] = "";
          }
     yea = year.SelectedItem.Text;
          if (yea!="") {
          Session["year"] = year.SelectedItem.Text;
          }    
         else {
          Session["year"] = "";
          }
...
__________________________
//Display the result set on winelist.aspx

void Page_Load(Object sender, EventArgs e) {



        string color = Session["color"].ToString();
     string category = Session"category"].ToString();
     string year = Session["year"].ToString();
        string region = Session["region"].ToString();
     string appellation = Session["appellation"].ToString();
     




             IBuySpy.ProductsDB1 productlist = new IBuySpy.ProductsDB1();
             MyList.DataSource = productlist.gettheitems(color, category, year, region, appellation);
             MyList.DataBind();
-------------------------------------------------------------------
//Method

public SqlDataReader gettheitems(string color, string category, string year, string region, string appellation) {


        SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand myCommand = new SqlCommand("getwinelist", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter parametercategory = new SqlParameter("@category", SqlDbType.NVarChar, 4000);
            parametercategory.Value = category;
            myCommand.Parameters.Add(parametercategory);

SqlParameter parametercolor = new SqlParameter("@color", SqlDbType.NVarChar, 4000);
            parametercolor.Value = color;
            myCommand.Parameters.Add(parametercolor);
               
SqlParameter parameteritemdid1 = new SqlParameter("@itemdid1", SqlDbType.NVarChar, 4000);
            parameteritemdid1.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(parameteritemdid1);          
                     
SqlParameter parameteryear = new SqlParameter("@year", SqlDbType.NVarChar, 4000);
            parameteryear.Value = year;
            myCommand.Parameters.Add(parameteryear);

    SqlParameter parameterregion = new SqlParameter("@region", SqlDbType.NVarChar, 4000);
            parameterregion.Value = region;
            myCommand.Parameters.Add(parameterregion);

    SqlParameter parameterapp = new SqlParameter("@appellation", SqlDbType.NVarChar, 4000);
            parameterapp.Value = appellation;
            myCommand.Parameters.Add(parameterapp);




 myConnection.Open();
            SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
               return result;
     
     
            // Return the datareader result
           



      }

__________________________________________________________________________
stored proc

CREATE PROCEDURE getwinelist

(

@color nvarchar(50),
@category nvarchar(50),
@year nvarchar(50),
@region nvarchar(50),
@appellation nvarchar(50),
@itemdid1 nvarchar(50) OUTPUT  

)
AS

if (@color<> '')
begin
SELECT ltrim(rtrim(name1)) as name1,  ltrim(rtrim(bottleprice)) as bottleprice, ltrim(rtrim(caseprice)) as caseprice, ltrim(rtrim(vintage)) as year, itemdid1
FROM newtable
where color like '%' + @color + '%'

end

else
  if (@category <> '')
begin

SELECT ltrim(rtrim(name1)) as name1,  ltrim(rtrim(bottleprice)) as bottleprice, ltrim(rtrim(caseprice)) as caseprice, ltrim(rtrim(vintage)) as year, itemdid1

FROM
   newtable
where category like '%' + @category + '%'
end


else
 if (@year <> '')
begin
SELECT ltrim(rtrim(name1)) as name1,  ltrim(rtrim(bottleprice)) as bottleprice, ltrim(rtrim(caseprice)) as caseprice, ltrim(rtrim(vintage)) as year, itemdid1

FROM
    newtable
where vintage like '%' + @year + '%'
end

else
 if (@region <> '')
begin
SELECT ltrim(rtrim(name1)) as name1,  ltrim(rtrim(bottleprice)) as bottleprice, ltrim(rtrim(caseprice)) as caseprice, ltrim(rtrim(vintage)) as year, itemdid1

FROM
    newtable
where region like '%' + @region + '%'
end
else
if (@appellation <> '')
begin
SELECT ltrim(rtrim(name1)) as name1,  ltrim(rtrim(bottleprice)) as bottleprice, ltrim(rtrim(caseprice)) as caseprice, ltrim(rtrim(vintage)) as year, itemdid1

FROM
    newtable
where appelation like '%' + @appellation + '%'   //<---purposely mispelled column name
end
GO

0
Comment
Question by:dprasad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 7

Accepted Solution

by:
jj819430 earned 2000 total points
ID: 11994185
ok there are some simple ways to do this.
I always think from the Database up.

so for the stored proc
@color nvarchar(50) = '-1',
@category nvarchar(50) = '-1',
@year nvarchar(50) = '-1',
@region nvarchar(50) = '-1',
@appellation nvarchar(50) = '-1',
@itemdid1 nvarchar(50) OUTPUT = '-1'

now if an item does not get an input, then the value will be '-1' so just do if statements for what you want. It isn't pretty but it works.

Now for the backend code
Always submit the session["variable"].ToString();
This way you don't have to worry about it, and you don't loose much performance here anyways. The only thing to remember is that if it is not selected it needs to be a '-1'

Now for the front peice
  col = color.SelectedItem.Text;
          if (col!="") {
          Session["color"] = color.SelectedItem.Text;
          }
          else {
          Session["color"] = "-1";
          }
and do the same for all of them. That way you will have a negative one for the middle layer.


the full and untested code for this follows, I throw try and catch statements just for caution.
<form runat="server">
                 <p align="center">                
                 <div align="center">
  <asp:DropDownList id="category" runat="server">  
         <asp:ListItem Text="Arneis"/>      
          <asp:ListItem Text="Barbera"/>      
          <asp:ListItem Text="Bordeaux"/>      
          <asp:ListItem Text="Cabernet Sauvignon"/>      
       ...  
          <asp:ListItem Text="Shiraz"/>    
          <asp:ListItem Text="Sparkling Blend "/>    
          <asp:ListItem Text="Sweet Blend"/>    
          <asp:ListItem Text="Syrah"/>    
          <asp:ListItem Text="Tempranillo"/>    
          <asp:ListItem Text="White"/>    
          <asp:ListItem Text="White Blend"/>    
          <asp:ListItem Text="Viognier"/>    
          <asp:ListItem Text="Zinfandel"/>    
  </asp:DropDownList>

<asp:DropDownList ID="year" runat="server">
    <asp:ListItem Text="1995"  />  
...
___________________________________________________________________-

}

void makeselection_Click(Object sender, System.EventArgs e) {
     string col;
     string cat;
     string yea;
     string reg;
     string cou;
     string app;
     
try
{
Session["color"] = color.SelectedItem.Text;
  }
catch(System.Exception NotSelected)
{
  Session["color"] = "-1";

}  
try
{
Session["category"] = category.SelectedItem.Text;
  }
catch(System.Exception NotSelected)
{
  Session["category"] = "-1";
}
try
{
 Session["year"] = year.SelectedItem.Text;
  }
catch(System.Exception NotSelected)
{
   Session["year"] =  "-1";
}        
...
__________________________
//Display the result set on winelist.aspx

void Page_Load(Object sender, EventArgs e) {


//what is this for?
//just do if statements through

if(Session["color"].ToString() != "-1")
{
string color = Session["color"].ToString();
}
else
{
string color = "None Selected";
}
// Repeat
          string category = Session"category"].ToString();
     string year = Session["year"].ToString();
        string region = Session["region"].ToString();
     string appellation = Session["appellation"].ToString();
             IBuySpy.ProductsDB1 productlist = new IBuySpy.ProductsDB1();
             MyList.DataSource = productlist.gettheitems(Session["color"].ToString(),Session["category"].ToString(),Session["year"].ToString(),Session["region"].ToString(),Session["appellation"].ToString());
             MyList.DataBind();
-------------------------------------------------------------------
//Method

public SqlDataReader gettheitems(string color, string category, string year, string region, string appellation) {


        SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand myCommand = new SqlCommand("getwinelist", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter parametercategory = new SqlParameter("@category", SqlDbType.NVarChar, 4000);
            parametercategory.Value = category;
            myCommand.Parameters.Add(parametercategory);

SqlParameter parametercolor = new SqlParameter("@color", SqlDbType.NVarChar, 4000);
            parametercolor.Value = color;
            myCommand.Parameters.Add(parametercolor);
               
SqlParameter parameteritemdid1 = new SqlParameter("@itemdid1", SqlDbType.NVarChar, 4000);
            parameteritemdid1.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(parameteritemdid1);          
                     
SqlParameter parameteryear = new SqlParameter("@year", SqlDbType.NVarChar, 4000);
            parameteryear.Value = year;
            myCommand.Parameters.Add(parameteryear);

    SqlParameter parameterregion = new SqlParameter("@region", SqlDbType.NVarChar, 4000);
            parameterregion.Value = region;
            myCommand.Parameters.Add(parameterregion);

    SqlParameter parameterapp = new SqlParameter("@appellation", SqlDbType.NVarChar, 4000);
            parameterapp.Value = appellation;
            myCommand.Parameters.Add(parameterapp);




 myConnection.Open();
            SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
               return result;
     
     
            // Return the datareader result
           



      }

__________________________________________________________________________
stored proc
// not sure what you are doing here exactly so I am going to assume that you want to select from Table where Anything is = What you selected

CREATE PROCEDURE getwinelist

(

@color nvarchar(50) = '-1',
@category nvarchar(50) = '-1',
@year nvarchar(50) = '-1',
@region nvarchar(50) = '-1',
@appellation nvarchar(50) = '-1',
@itemdid1 nvarchar(50) OUTPUT  

)
AS
Declare @WhereClause as VarChar,
@Started as Bit = 0 -- Just to see if it needs an and

if (@color != '-1' )
begin
--SELECT ltrim(rtrim(name1)) as name1,  ltrim(rtrim(bottleprice)) as bottleprice, ltrim(rtrim(caseprice)) as caseprice, ltrim(rtrim(vintage)) as year, itemdid1
--FROM newtable
--where color like '%' + @color + '%'
if(@Started != 1)
begin
@Started = 1
@WhereClause = @WhereClause + ' color like % ' + @color + ' % '
end
else
@WhereClause = @WhereClause + ' and color like % ' + @color + ' % '
end
--repeat for others
exec 'select * from newtable where ' + @WhereClause

-- basically just dynamically build your select statement.... not the cleanest but it definitely works
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month13 days, 1 hour left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question