Solved

Optional list items to stored proc

Posted on 2004-09-06
1
219 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
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

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