Hi shailesh,

Besides what Jeff said, you may also be running into a problem with how you pass the value from the QueryString I think tat when there is no valid QueryString available for albumArtist, it will pass "" ( a zero length string) and not NULL. Null and a ZLS are not the same, so you may get unwanted results. IMO, I think it;s better to explicitly set the NULL value: This way, when there is no valid querystring, you're passing DBNull.Value to the database.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: I'm Yours by Prince (Track 9 from the album: For You) What's This?

Thanks again Jeff and Imar. I am certainly making steady progress, thanks to your inputs - I will let you know of the results later tonight. Shailesh.

Please bear with me folks -- I am sure I am missing something here. I tried following your suggestions as follows and this time it is not making any hits at all. The result page simply brings up "no results found".
Am I modifying the code properyly (Imar)
Is my sproc modified properyly (Jeff)

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace perrysplace
{
    /// <summary>
    /// Summary description for PPMusicResults.
    /// </summary>
    public class PPMusicResults : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid dgMusic;
        protected System.Web.UI.WebControls.Label lblItems;
        protected System.Web.UI.WebControls.Label ErrorMsg;
        protected System.Data.SqlClient.SqlConnection cnMusic;

        private void Page_Load(object sender, System.EventArgs e)
        {
            // Put user code to initialize the page here
            if (!Page.IsPostBack)
            {
                bindGrid();
            }

        }

        public void bindGrid()
        {
            SqlCommand cmdSelect = new SqlCommand("PPAlbumSearch", cnMusic);

            cmdSelect.CommandType = CommandType.StoredProcedure;

            cmdSelect.Parameters.Add("@category", Request.QueryString["albumCategory"]);

            if (Request.QueryString["albumArtist"] == null)
            {
             cmdSelect.Parameters.Add("@artist", DBNull.Value);
            }
            else
            {
             cmdSelect.Parameters.Add("@artist", Request.QueryString["albumArtist"]);
            }

            if (Request.QueryString["albumTitle"] == null)
            {
                cmdSelect.Parameters.Add("@title", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@title", Request.QueryString["albumTitle"]);
            }
            if (Request.QueryString["albumLabel"] == null)
            {
                cmdSelect.Parameters.Add("@label", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@label", Request.QueryString["albumLabel"]);
            }
            if (Request.QueryString["albumEvent"] == null)
            {
                cmdSelect.Parameters.Add("@event", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@event", Request.QueryString["albumEvent"]);
            }
            if (Request.QueryString["albumRhythm"] == null)
            {
                cmdSelect.Parameters.Add("@rhythm", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@rhythm", Request.QueryString["albumRhythm"]);
            }
            if (Request.QueryString["albumLevel"] == null)
            {
                cmdSelect.Parameters.Add("@level", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@level", Request.QueryString["albumLevel"]);
            }
            if (Request.QueryString["albumType"] == null)
            {
                cmdSelect.Parameters.Add("@type", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@type", Request.QueryString["albumType"]);
            }


            /*cmdSelect.Parameters.Add("@artist", Request.QueryString["albumArtist"]);
            cmdSelect.Parameters.Add("@title", Request.QueryString["albumTitle"]);
            cmdSelect.Parameters.Add("@label", Request.QueryString["albumLabel"]);
            cmdSelect.Parameters.Add("@category", Request.QueryString["albumCategory"]);
            cmdSelect.Parameters.Add("@event", Request.QueryString["albumEvent"]);
            cmdSelect.Parameters.Add("@rhythm", Request.QueryString["albumRhythm"]);
            cmdSelect.Parameters.Add("@level", Request.QueryString["albumLevel"]);
            cmdSelect.Parameters.Add("@type", Request.QueryString["albumType"]);*/

            DataSet dsMusic = new DataSet();
            cnMusic.Open();
            SqlDataAdapter daMusic = new SqlDataAdapter(cmdSelect);
            daMusic.Fill(dsMusic);
            dgMusic.DataSource = dsMusic;
            dgMusic.DataBind();

            cnMusic.Close();

            if (dgMusic.Items.Count == 0)
            {
                ErrorMsg.Text = "No Items Matched Your Search.";
            }
            else
            {
                lblItems.Text = "Page " + ( dgMusic.CurrentPageIndex+1) + " of " + dgMusic.PageCount;
            }
        }
        public void processCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
        {
            if (e.CommandName.Equals("GetMusicDetails"))
            {
                Server.Transfer("PPMusicDetails.aspx?AlbumID=" +
                    e.CommandArgument.ToString());
            }

        }

        private void pageChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEvent Args e)
        {
            dgMusic.CurrentPageIndex = e.NewPageIndex;
            bindGrid();

        }
}
}

Stored Procedure
CREATE PROCEDURE PPAlbumSearch
(
    @artist nvarchar(120),
    @title nvarchar(120),
    @label nvarchar(120),
    @category nvarchar(60),
    @event nvarchar(60),
    @rhythm nvarchar(60),
    @level nvarchar(60),
    @type nvarchar(60)
)

AS


SELECT
    AlbumID,
    ProductID,
    SongCDispArtist,
    SongWebTitle,
    SongMedia,
    SongLabel,
    AlbumPrice

FROM
    PPAlbumSongs

WHERE
    AlbumCategory=@category
    AND

    (SongCDispArtist LIKE '%' + @artist + '%' OR @artist is null)
    AND
    (SongWebTitle LIKE '%' + @title + '%' OR @title is null)
    AND
    (SongLabel LIKE '%' + @label + '%' OR @label is null)
    AND
    (SongCDispEvent=@event OR @event is null)
    AND
    (SongCDispRhythm=@rhythm OR @rhythm is null)
    AND
    (SongCDispLevel=@level OR @level is null)
    AND
    (SongCDispType=@type OR @type is null)

ORDER BY
    SongCDispArtist
GO
________________

Thanks for your reply.

Hi there,

I think it's best to run your Stored Procedure in the Query Analyzer first. Try it out with a couple of variations, passing null for some parameters. That way, you can see what is causing the problem.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Just a guess. Just got a feeling that still ZLS is escaping from this.
Would this help?

Checking for ZLS itself, rather than checking for null there.
If this doesn't still make sense, a check for its length would also help.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Yeah, you're right. The parameter will only be null when the QueryString is not defined at all:

Consider this code:

Request.QueryString["DoesNotExist"] == null

and these (non-existent) QueryStrings:

MyPage.aspx returns true (there is no DoesNotExist param)

MyPage.aspx?DoesNotExist= returns false; DoesNotExist does exist, but will indeed return a ZLS

MyPage.aspx?DoesNotExist=MyValue returns false; DoesNotExist does exist, and will indeed return MyValue.

So, you should probably mix the two: You need the code that checks for null, because you'll get a null reference exception if the QueryString is not passed at all.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: History by Bush (Track 9 from the album: Razorblade Suitcase) What's This?

Thanks Vijay and Imar -- I will implement your suggestions shortly. something interesting has developed lately -- the database will return nothing if I choose "Ballroom & Round Dance" as my category -- although it will return results for other categories -- "C/W & Clogging & Jukebox", "Contra/Quadrille", "Square Dance". There are about 4000 records that belong to the "Ballroom & Round Dance" and these are all returned when I use the query analyzer to do a simple select. I am just intrigued by this -- why does it not return any search through my sproc which was working fine until a couple of days ago -- it was returning all the records under the "Ballroom & Round Dance" Category. Do I need to use escape characters? I am a total novice as far as SQL server is concerned. Let me know your thoughts when time permits. Shailesh.

Vijay & Imar -- good news -- the sproc seems to be working in the Query Analyzer. bad news -- it is not working when I do a search on the web pages. Here is the modified sproc:
CREATE PROCEDURE AlbumSearch
(
    @artist nvarchar(120) = null,
    @title nvarchar(120) = null,
    @label nvarchar(120) = null,
    @category nvarchar(60),
    @event nvarchar(60) = null,
    @rhythm nvarchar(60) = null,
    @level nvarchar(60) = null,
    @type nvarchar(60) = null
)

AS

SELECT
    AlbumID,
    SongCDispArtist,
    SongWebTitle,
    SongMedia,
    SongLabel,
    AlbumPrice,
    AlbumCDisplayEvent,
    SongEvent,
    AlbumCDisplayLevel,
    SongLevel,
    AlbumCDisplayRhythm,
    AlbumCDisplayType

FROM
    AlbumSong

WHERE
    SongCategory=@category
    AND
    ((SongCDispArtist LIKE '%' + @artist + '%' OR @artist = null)
    AND
    (SongWebTitle LIKE '%' + @title + '%' OR @title = null)
    AND
    (SongLabel LIKE '%' + @label + '%' OR @label = null)
    AND
    (SongCDispEvent=@event OR @event = null)
    AND
    (SongCDispRhythm=@rhythm OR @rhythm=null)
    AND
    (SongCDispLevel=@level OR @level = null)
    AND
    (SongCDispType=@type OR @type = null))

ORDER BY
    SongCDispArtist
GO
_________________
As I said, this is working fine in the query analyzer -- searching through my web page is a different story. I do not know why. I will keep you posted. Shailesh.

Hi shailesh,

There could be various reasons for this problem. You may indeed need to escape the data (as it is passed through the QueryString). But maybe the Category field contains an additional space?

Looking back at your initial code, there are some things I don't understand. For example, why are you using values from the QueryString? Are you trying to accept data from a non-ASP.NET page?

If that's not the case, I think you should change your pages a bit. In the ASP.NET world, you'd add a textbox or list box control on your page which is named txtCategory or lstCategory for example. Then you can use code like this:

cmdSelect.Parameters.Add("@category", txtCategory.Text);

As an added bonus, you get intelli sense support on the txtCategory control, because it's now a strongly typed object, instead of a string returned from the QueryString collection.

If all this doesn't help, look in the source of the original page. Is the category you need already there? Does it come from a drop down? If so, is the value attribute identical to "Ballroom & Round Dance"??

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi Imar
Thanks for your reply. I am using ASP.NET exclusively. I have decided to change things around a little bit and use Server.Transfer instead of querystring. After implementing Server.Transfer, I can see that the values are passed to the music results page. However, the datagrid still comes up empty. This is what I have done for the first page -- the music search options page:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace Perrys
{
    /// <summary>
    /// Summary description for MusicCatalog.
    /// </summary>
    public class MusicCatalog : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.TextBox txtArtist;
        protected System.Web.UI.WebControls.Button btnSearch;
        protected System.Web.UI.WebControls.DropDownList ddlCategory;
        protected System.Web.UI.WebControls.TextBox txtTitle;
        protected System.Web.UI.WebControls.TextBox txtLabel;
        protected System.Web.UI.WebControls.DropDownList ddlEvent;
        protected System.Web.UI.WebControls.DropDownList ddlRhythm;
        protected System.Web.UI.WebControls.DropDownList ddlLevel;
        protected System.Web.UI.WebControls.DropDownList ddlType;
        protected System.Data.SqlClient.SqlCommand cmdSelect;
        protected System.Data.SqlClient.SqlConnection cnMusic;
        protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;

        private void Page_Load(object sender, System.EventArgs e)
        {
            // Put user code to initialize the page here
            if (!IsPostBack)
            {
                cnMusic.Open();
                cmdSelect = new SqlCommand ("Select AlbumCategory From AlbumCategory Order By AlbumCategory", cnMusic);
                SqlDataAdapter daCategory = new SqlDataAdapter(cmdSelect);
                DataSet dsCategory = new DataSet();
                daCategory.Fill(dsCategory);
                ddlCategory.DataSource = dsCategory;
                ddlCategory.DataTextField = "AlbumCategory";
                ddlCategory.DataBind();
                ddlCategory.Items.Insert(0,"");
                ddlCategory.SelectedIndex = 0;

                cmdSelect = new SqlCommand ("Select AlbumEvent From AlbumEvent Order By AlbumEvent", cnMusic);
                SqlDataAdapter daEvent = new SqlDataAdapter(cmdSelect);
                DataSet dsEvent = new DataSet();
                daEvent.Fill(dsEvent);
                ddlEvent.DataSource = dsEvent;
                ddlEvent.DataTextField = "AlbumEvent";
                ddlEvent.DataBind();
                ddlEvent.Items.Insert(0, "");
                ddlEvent.SelectedIndex = 0;

                cmdSelect = new SqlCommand ("Select AlbumRhythm From AlbumRhythm Order By AlbumRhythm", cnMusic);
                SqlDataAdapter daRhythm = new SqlDataAdapter(cmdSelect);
                DataSet dsRhythm = new DataSet();
                daRhythm.Fill(dsRhythm);
                ddlRhythm.DataSource = dsRhythm;
                ddlRhythm.DataTextField = "AlbumRhythm";
                ddlRhythm.DataBind();
                ddlRhythm.Items.Insert(0, "");
                ddlRhythm.SelectedIndex = 0;

                cmdSelect = new SqlCommand ("Select AlbumLevel From AlbumLevel Order By AlbumLevel", cnMusic);
                SqlDataAdapter daLevel = new SqlDataAdapter(cmdSelect);
                DataSet dsLevel = new DataSet();
                daLevel.Fill(dsLevel);
                ddlLevel.DataSource = dsLevel;
                ddlLevel.DataTextField = "AlbumLevel";
                ddlLevel.DataBind();
                ddlLevel.Items.Insert(0, "");
                ddlLevel.SelectedIndex = 0;

                cmdSelect = new SqlCommand ("Select AlbumType From AlbumType Order By AlbumType", cnMusic);
                SqlDataAdapter daType = new SqlDataAdapter(cmdSelect);
                DataSet dsType = new DataSet();
                daType.Fill(dsType);
                ddlType.DataSource = dsType;
                ddlType.DataTextField = "AlbumType";
                ddlType.DataBind();
                ddlType.Items.Insert(0, "");
                ddlType.SelectedIndex = 0;

                cnMusic.Close();
            }


        }

        public string artistName
        {
            get
            {
                return txtArtist.Text;
            }
        }

        public string albumTitle
        {
            get
            {
                return txtTitle.Text;
            }
        }

        public string albumLabel
        {
            get
            {
                return txtLabel.Text;
            }
        }

        public string albumCategory
        {
            get
            {
                return ddlCategory.SelectedItem.Text;
            }
        }

        public string albumEvent
        {
            get
            {
                return ddlEvent.SelectedItem.Text;
            }
        }

        public string albumRhythm
        {
            get
            {
                return ddlRhythm.SelectedItem.Text;
            }
        }

        public string albumLevel
        {
            get
            {
                return ddlLevel.SelectedItem.Text;
            }
        }

        public string albumType
        {
            get
            {
                return ddlType.SelectedItem.Text;
            }
        }

        public void btnSearch_Click(object sender, System.EventArgs e)
        {
            Server.Transfer("musicresults.aspx");


            /*Response.Redirect("musicresults.aspx?albumArtist= " +
                Server.UrlEncode(this.txtArtist.Text) + "&albumTitle=" +
                Server.UrlEncode(this.txtTitle.Text) + "&albumLabel=" +
                Server.UrlEncode(this.txtLabel.Text) + "&albumCategory=" +
                Server.UrlEncode(this.ddlCategory.SelectedItem.Tex t) + "&albumEvent=" +
                Server.UrlEncode(this.ddlEvent.SelectedItem.Text) + "&albumRhythm=" +
                Server.UrlEncode(this.ddlRhythm.SelectedItem.Text) + "&albumLevel=" +
            Server.UrlEncode(this.ddlLevel.SelectedItem.Text) + "&albumType=" +
                Server.UrlEncode(this.ddlType.SelectedItem.Text)); */
        }
}
}
_____________________

This is what I did in the second page -- the music results page.

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace Perrys
{
    /// <summary>
    /// Summary description for MusicResults.
    /// </summary>
    public class MusicResults : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid dgMusic;
        protected System.Web.UI.WebControls.Label lblItems;
        protected System.Data.SqlClient.SqlConnection cnMusic;
        protected System.Web.UI.WebControls.Label lblHead;
        protected System.Web.UI.WebControls.Label Label1;
        protected System.Web.UI.WebControls.Label ErrorMsg;
        protected System.Web.UI.WebControls.Label Label2;
        protected System.Data.SqlClient.SqlDataAdapter daMusic;
        protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
        protected System.Data.DataSet dsMusic;
        public MusicCatalog mc;


        private void Page_Load(object sender, System.EventArgs e)
        {
            // Put user code to initialize the page here
            MusicCatalog mc;
            mc = (MusicCatalog)Context.Handler;
            Label1.Text = mc.albumCategory;
            Label2.Text = mc.albumEvent;
            if (!Page.IsPostBack)
            {
                bindGrid();
            }

        }

        public void bindGrid()
        {
            SqlCommand cmdSelect = new SqlCommand("AlbumSearch", cnMusic);

            cmdSelect.CommandType = CommandType.StoredProcedure;

            MusicCatalog mc;
            mc = (MusicCatalog)Context.Handler;

            cmdSelect.Parameters.Add("@category", mc.albumCategory.ToString());


            if (mc.artistName == null)
            {
             cmdSelect.Parameters.Add("@artist", DBNull.Value);
            }
            else
            {
             cmdSelect.Parameters.Add("@artist", mc.artistName);
            }

            if (mc.albumTitle == null)
            {
                cmdSelect.Parameters.Add("@title", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@title", mc.albumTitle);
            }
            if (mc.albumLabel == null)
            {
                cmdSelect.Parameters.Add("@label", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@label", mc.albumLabel);
            }
            if (mc.albumEvent == null)
            {
                cmdSelect.Parameters.Add("@event", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@event", mc.albumEvent);
            }
            if (mc.albumRhythm == null)
            {
                cmdSelect.Parameters.Add("@rhythm", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@rhythm", mc.albumRhythm);
            }
            if (mc.albumLevel == null)
            {
                cmdSelect.Parameters.Add("@level", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@level", mc.albumLevel);
            }
            if (mc.albumType == null)
            {
                cmdSelect.Parameters.Add("@type", DBNull.Value);
            }
            else
            {
                cmdSelect.Parameters.Add("@type", mc.albumType);
            }

                /*cmdSelect.Parameters.Add("@artist", Request.QueryString["albumArtist"]);
            cmdSelect.Parameters.Add("@title", Request.QueryString["albumTitle"]);
            cmdSelect.Parameters.Add("@label", Request.QueryString["albumLabel"]);
            cmdSelect.Parameters.Add("@category", Request.QueryString["albumCategory"]);
            cmdSelect.Parameters.Add("@event", Request.QueryString["albumEvent"]);
            cmdSelect.Parameters.Add("@rhythm", Request.QueryString["albumRhythm"]);
            cmdSelect.Parameters.Add("@level", Request.QueryString["albumLevel"]);
            cmdSelect.Parameters.Add("@type", Request.QueryString["albumType"]);*/


            cnMusic.Open();
            SqlDataAdapter daMusic = new SqlDataAdapter(cmdSelect);
            daMusic.Fill(dsMusic);
            dgMusic.DataSource = dsMusic;
            dgMusic.DataBind();

            cnMusic.Close();

            if (dgMusic.Items.Count == 0)
            {
                ErrorMsg.Text = "No Items Matched Your Search.";
            }
            else
            {
                lblItems.Text = "Page " + ( dgMusic.CurrentPageIndex+1) + " of " + dgMusic.PageCount;
            }
        }
        public void processCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
        {
            if (e.CommandName.Equals("GetMusicDetails"))
            {
                Server.Transfer("MusicDetails.aspx?AlbumID=" +
                    e.CommandArgument.ToString());
            }

        }

        private void pageChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEvent Args e)
        {
            dgMusic.CurrentPageIndex = e.NewPageIndex;
            bindGrid();

        }
}
}

But as I said, the datagrid comes up empty. May be I need to rewrite the datagrid code. what do you think? Any suggestions will be greatly appreciated.