Solved

Optional list items to stored proc

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Article by: Ivo
Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now