Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Optional list items to stored proc

Posted on 2004-09-06
1
Medium Priority
?
224 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

782 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