Solved

Optional list items to stored proc

Posted on 2004-09-06
1
222 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 500 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

729 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