dynamic query using asp.net and gridview

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

dynamic query using asp.net and gridview

Author

Topic

arbidh
Starting Member

2 Posts

Posted - 2011-04-15 : 15:10:14

Hi All,

I wrote a dynamic query using a stored procedure in ms sql 2005
and have a gridview that I calll through code to display the results
from a dynamic search query. I test my stored procedure in the database explorer however when I call gridview from code it doesn't display any rows. Here is my stored procedure below:



ALTER PROCEDURE dbo.recSpDynamicSearch3

(
   @client_id  varchar(20),
   @unit_id    varchar(20),
   @state_id      varchar(20),  
   @county_id     varchar(20),  
   @req_by     nvarchar(20),  
   @req_date   varchar(20),     
   @comp_date  varchar(20),    
   @order_num  varchar(20)='',
   @doc_num    nvarchar(20)='',
   @book       varchar(10) ,
   @page_num   varchar(10),
   @pages      varchar(10),
   @minutes    varchar(10),
   @debug BIT=NULL
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @strSelect		varchar(2000)
	DECLARE @strSelect_where	varchar(1000)
	DECLARE @strSelectLike	varchar(100)
	DECLARE @strsql_order varchar(100)
	SET @strSelect	= ''
	SET @strSelect_where = ''
	SET @strSelectLike = ''
	
    	

	IF @client_id <> '0' 
		BEGIN
		SET @strSelect_where = @strSelect_where + ' AND n.client_id = ' + @client_id
		END
	IF @unit_id <> '0'
	     BEGIN	
			SET @strSelect_where = @strSelect_where + ' AND n.unit_id = ' + @unit_id
		END
    IF @state_id <> ''
		BEGIN
			SET @strSelect_where=@strSelect_where + ' AND n.state_id= ' + @state_id
        END
    IF @county_id <> ''
		BEGIN
			SET @strSelect_where=@strSelect_where + ' AND n.county_id= ' + @state_id
        END 
     IF @req_by IS NOT NULL
		BEGIN
			--SET @strSelectLike  = ' LIKE ''%' + @req_by + '%'''
			SET @strSelect_where =  @strSelect_where + ' AND n.req_by LIKE N'''+ '%' + @req_by + '%' + '''' 
        END
       IF @req_date<> ''  
		BEGIN
			SET @strSelect_where = @strSelect_where + ' AND n.req_date >= ''' + @req_date + ''' AND n.req_date <= '''  + @req_date + ''' '
				END
		
	     IF @comp_date<> ''  
		BEGIN
			SET @strSelect_where = @strSelect_where + ' AND n.comp_date >= ''' + @req_date + ''' AND n.comp_date <= '''  + @req_date + ''' '
				END
        
        
        
     IF @order_num <> ''
		BEGIN
			SET @strSelectLike  = ' LIKE ''%' + @order_num + '%'''
			SET @strSelect_where =  @strSelect_where + ' AND n.order_num ' + @strSelectLike
		END
	IF @doc_num <> ''
		BEGIN
			SET @strSelectLike  = ' LIKE N''%' + @doc_num + '%'''
			SET @strSelect_where =  @strSelect_where + ' AND n.doc_num ' + @strSelectLike
		END
		
		
	IF @book <> '' 
		BEGIN
			SET @strSelectLike  = ' LIKE ''%' + @book + '%'''
			SET @strSelect_where =  @strSelect_where + ' AND n.book ' + @strSelectLike
		END

	IF @page_num <> '' 
		BEGIN
			SET @strSelectLike  = ' LIKE ''%' + @page_num + '%'''
			SET @strSelect_where =  @strSelect_where + ' AND n.page_num ' + @strSelectLike
		END


    IF @pages <> '' 
		BEGIN
			SET @strSelectLike  = ' LIKE ''%' + @pages + '%'''
			SET @strSelect_where =  @strSelect_where + ' AND n.pages ' + @strSelectLike
		END

	IF @minutes <> '' 
		BEGIN
			SET @strSelectLike  = ' LIKE ''%' + @minutes + '%'''
			SET @strSelect_where =  @strSelect_where + ' AND n.minutes ' + @strSelectLike
		END


	SET @strSelect_where = right(@strSelect_where,  len(@strSelect_where)- 4)


IF @strsql_order = ''
	BEGIN
		SET @strsql_order = 'ORDER BY req_date DESC, county_id ASC, doc_num ASC'
	END

SET @strSelect = 'SELECT  n.client_id, n.unit_id, n.user_id, n.state_id, n.county_id, n.req_by, n.req_date, n.comp_date, n.order_num, n.doc_num, n.book, n.page_num, n.tracking_id, n.pages, 
                         n.minutes
                       FROM dbo.recTblNewSearch2 n INNER JOIN
                         dbo.cusTblUser ON n.user_id = dbo.cusTblUser.user_id LEFT OUTER JOIN
                         dbo.tblUnit ON n.uID = dbo.tblUnit.uID 
                        WHERE  (1 = 1)  AND ' + @strSelect_where
                  
                 
	--PRINT @strSelect
	EXEC (@strSelect)
	
	
	
	SET NOCOUNT OFF
END

-------------------------

here is my code in asp.net
// i call the eventhandler for my search button below



 protected void b_search_Click(object sender, EventArgs e)
        {   

            sqladapt = new SqlDataAdapter();
            sqladapt = new SqlDataAdapter("recSpDynamicSearch3", mycon);

           sqladapt.SelectCommand.CommandType= CommandType.StoredProcedure;
            





          

            



            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@client_id", SqlDbType.NVarChar, 20));
            if (c_mBase.IsSPLUser== true)
            {
                sqladapt.SelectCommand.Parameters["@client_id"].Value = client.SelectedItem.Value;
            }
            else
            {
                sqladapt.SelectCommand.Parameters["@client_id"].Value = i_mClient_id.ToString();
            }
            
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@unit_id", SqlDbType.NVarChar, 20));
            sqladapt.SelectCommand.Parameters["@unit_id"].Value = unit.SelectedItem.Value;
            //sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@user_id", SqlDbType.NVarChar, 20));
           //sqladapt.SelectCommand.Parameters["@user_id"].Value = Session["user_level"].ToString(); ;
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@state_id", SqlDbType.NVarChar, 20));
            sqladapt.SelectCommand.Parameters["@state_id"].Value = state.SelectedItem.Value;
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@county_id", SqlDbType.NVarChar, 20));
            sqladapt.SelectCommand.Parameters["@county_id"].Value = county.SelectedItem.Value;
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@req_by", SqlDbType.VarChar, 20));
            sqladapt.SelectCommand.Parameters["@req_by"].Value = reqby.Text;
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@req_date", SqlDbType.NVarChar,20));
            sqladapt.SelectCommand.Parameters["@req_date"].Value = (reqdate.Text).Trim();
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@comp_date", SqlDbType.NVarChar,20));
            sqladapt.SelectCommand.Parameters["@comp_date"].Value = (date_compltd.Text).Trim();
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@order_num", SqlDbType.NVarChar,20));
            sqladapt.SelectCommand.Parameters["@order_num"].Value = (order.Text).Trim();
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@doc_num", SqlDbType.NVarChar, 30));
            sqladapt.SelectCommand.Parameters["@doc_num"].Value = (doc.Text).Trim();
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@book", SqlDbType.NVarChar,20));
            sqladapt.SelectCommand.Parameters["@book"].Value = (book.Text).Trim();
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@page_num", SqlDbType.NVarChar,10));
            sqladapt.SelectCommand.Parameters["@page_num"].Value = (pg.Text).Trim();
            //sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@tracking_id", SqlDbType.NVarChar,30));
            //sqladapt.SelectCommand.Parameters["@tracking_id"].Value = rand.Next(1, 10000);
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@pages", SqlDbType.NVarChar,10));
            sqladapt.SelectCommand.Parameters["@pages"].Value = (pages.Text).Trim();
            sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@minutes", SqlDbType.NVarChar,10));
            sqladapt.SelectCommand.Parameters["@minutes"].Value = (minutes.Text).Trim();
            //sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@sql", SqlDbType.NVarChar, 100));
            //sqladapt.SelectCommand.Parameters["@sql"].Value = s
            //sqladapt.SelectCommand.Parameters.Add(new SqlParameter("@param", SqlDbType.NVarChar, 100));
            //myCommand.SelectCommand.Parameters["@param"].Value = SortOrder.Text;
            //create new dataset to hold the record
            //sqladapt.SelectCommand.Connection.Open();

            DataTable dtResult = new DataTable();
            sqladapt.Fill(dtResult);
            Session["dtresult"] = dtResult;
            
            //dtResult =
                //DataSet ds = new DataSet();

           //sqladapt.Fill(dtResult,"Table");


           //

            GridView1.DataSource = dtResult;
           GridView1.DataBind();
            //mygrid.DataBind();
           //Grid_PreRender(mygrid);
           //mygrid.Visible = true;
           GridView1.PagerSettings.Visible = false;
            if(dtResult.Rows.Count>0)
            {
                GridView1.Visible = true;
            }

            else
            {
                lblNoRec.Visible = true;
               GridView1.Visible=false;
           }
            //GridView1.DataBind();

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-15 : 15:14:24

Total guess here, I'm not a programmer, but try dropping the SET NOCOUNT OFF at the end of the sproc.

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-15 : 16:10:04

I used to be a C# programmer in a previous life before I got a real job as a T-SQL programmer. Looking through the code, the only thing that I could see is that your Command.Parameters are of type SqlDbType.NVarChar, but the stored proc is expecting varchar in most cases.

Also, Parameter.AddWithValue may be easier and less error-prone to use than adding the parameter with specified type and length and then assigning the value.

arbidh
Starting Member

2 Posts

Posted - 2011-04-19 : 11:37:31

Thank you for your reply.

I have tried all that and still get no results displayed.
I get the results displayed inside the server explorer
and when I do the query on the datasource, however
I get no results when I use C# and gridview. Can you please
let me know what alternative ways I can use a dynamic search
query in a stored procedure.

I have tried doing just one query with no dynamic query and
it worked but it wont work when I have several where clauses

I am doing this for work and I am not sure why it doesn't work

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-19 : 11:53:38

If you are sure that all the parameters are being sent in correctly, you could try the folloing:

In your test environment, run SQL Server Profiler (you can access it from the Start Menu or from SSMS under Tools). Then run your C# program. You will see the query that comes into the SQL server in the profiler. Copy that query and see if you can run it from an SSMS query window. I suspect that may not return any results either.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources