SqlDataSource and Parameters query RSS

• • Reply
  

  harrylynn

  Member

  284 Points

  294 Posts

  SqlDataSource and Parameters query

  Aug 22, 2010 05:18 PM|LINK

  Hi everyone,

  I'm on the edge wondering is it possible ? I have got 2 questions. Please help me out.

  1. I have a GridView on my page and it uses sqldatasource with parameterized query. What I want to do is, on page load (where nothing has been selected so no parameter supplied), I want it to query everything (something like SELECT * FROM [this_table]) but since my SelectCommand is something like

  SELECT * FROM [this_table] WHERE [this_column] = @someParameters AND [that_column] = @someParameters.

  Can I play around with default value to achieve something like that but how ? Now, when the page loads, it doesn't show anything (No Gridview).
  

  2. On my page, I made something like (username, gender, address, and more) and one single search button. That means, no single control enable auto postback. What I am trying to accomplish is building dynamic query

  (if username specifed -> SELECT * FROM [this_table] WHERE [username] LIKE @username).

  If both username and gender are specified (SELECT * FROM [this_table] WHERE [username] LIKE @username AND [gender] = @gender) and you know the rest. How can I do this using GridView and SqlDataSource ? To my knowledge, I can only specify one SELECT statement in a sqldatasource.  I am stucked and desparately looking for help. Thanks all.
  

• • Reply
  

  sansan

  All-Star

  53942 Points

  8147 Posts

  Re: SqlDataSource and Parameters query

  Aug 22, 2010 06:11 PM|LINK

  you can do this way

    <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
      <asp:RadioButtonList ID="rbListGender" runat="server">
          <asp:ListItem Text="Male" Value="0"></asp:ListItem>
          <asp:ListItem Text="Female" Value="1"></asp:ListItem>
      </asp:RadioButtonList>
      <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
             ConnectionString="<%$ ConnectionStrings:TestDatabaseConnectionString %>"
          SelectCommand="MyProcedureName" SelectCommandType="StoredProcedure">
          <SelectParameters>
              <asp:ControlParameter ControlID="txtUserName" Name="UserName" PropertyName="Text"
                  Type="String" />
              <asp:ControlParameter ControlID="rbListGender" Name="Gender" PropertyName="SelectedValue"
                  Type="String" />
          </SelectParameters>
      </asp:SqlDataSource>

  create a procedure with parameters(UserName, Gender) to append to the query if username is not null and gender condition in where clause if Gender is not null.

  Add a button to search and when you click the button, bind the SqlDataSource.

  SqlDataSource1.DataBind();

  Santhosh
• • Reply
  

  harrylynn

  Member

  284 Points

  294 Posts

  Re: SqlDataSource and Parameters query

  Aug 22, 2010 06:21 PM|LINK

  Thanks. Does that mean I can't do it with ASP.NET but using with stored procedure ? i know this might sound lame but i dun have much experience using stored procedures. could you elaborate a bit more on the last part or at least a little bit of code snippet to get me going ? Thanks a lot.
  

• • Reply
  

  sansan

  All-Star

  53942 Points

  8147 Posts

  Re: SqlDataSource and Parameters query

  Aug 22, 2010 06:31 PM|LINK

  Yes,  you can also do it with Query, But I said Proc as I always prefer not doing the query part in the code behind.

  you can create dynamic sqldatasource with parameters and use select method of sql data source to get the data.

          string strConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
          string strCommand="SELECT * FROM TABLEA ";
          SqlDataSource sds = new SqlDataSource(strConnectionString,strCommand );
          if (string.IsNullOrEmpty(txtUserName.Text.Trim()))
              strCommand += " Where UserName='"+txtUserName.Text+"'";
          GridView1.DataSource=sds.Select(new System.Web.UI.DataSourceSelectArguments());
          GridView1.DataBind();

  Santhosh
  • Marked as answer by harrylynn on Aug 22, 2010 06:52 PM
• • Reply
  

  harrylynn

  Member

  284 Points

  294 Posts

  Re: SqlDataSource and Parameters query

  Aug 22, 2010 06:55 PM|LINK

  Gezz. You the man. . awesome. As  you mentioned just now, I was reading about stored procedures. I guess I better start using those rather than putting all my query in code-behind which sometimes I found it really difficult to trace back everything. If you don't mind please point out any good online tutorials on it. The one I was reading doesn't say anything conditional statement nor looping in a stored procedures. All and all, ya rock. thanks for helping me out.
  

  
  

• • Reply
  

  harrylynn

  Member

  284 Points

  294 Posts

  Re: SqlDataSource and Parameters query

  Aug 22, 2010 07:55 PM|LINK

  Sorry to bring another issue up again. I think it's better to post this problem under this thread. I have written the stored procedure, and it seems to be working fine on MSSQL. When I type exec null,null in MSSQL it does show all the colums from the table but when I run Test Query from Configure Data Source, it is showing nothing. I set both default value for city and username to null. I have no idea what seem to have gone wrong. Can you tell me what could be the problem ? Here is my stored procedure

  CREATE PROCEDURE test_procedure
  @user varchar(1000),
  @city varchar(1000)
  
  
  AS
  
  
  IF @user IS NOT NULL AND @city IS NOT NULL
  BEGIN
  	SELECT * FROM users WHERE username LIKE '%'+@user+'%' AND city = @city
  END
  
  
  IF @user IS NOT NULL AND @city IS NULL
  BEGIN
  	SELECT * FROM users WHERE username LIKE '%'+@user+'%'
  END
  
  
  IF @city IS NOT NULL AND @user IS NULL
  BEGIN
  	SELECT * FROM users WHERE city = @city
  END
  
  
  IF @city IS NULL AND @user IS NULL
  BEGIN
  	SELECT * FROM users
  END

  
  It is really giving me headache now.
  

  • Marked as answer by harrylynn on Sep 04, 2010 02:19 PM
• • Reply
  

  harrylynn

  Member

  284 Points

  294 Posts

  Re: SqlDataSource and Parameters query

  Aug 22, 2010 07:59 PM|LINK

  Ah. Found the culprit anyway. I gotta change the datatype to DBNull.
  

  • Marked as answer by harrylynn on Aug 22, 2010 08:00 PM
• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: SqlDataSource and Parameters query

  Aug 22, 2010 11:09 PM|LINK

  The solution to both your questions is the same:

   SELECT * FROM [this_table] WHERE ([this_column] = @parameter1 OR @parameter1 Is Null) AND ([that_column] = @parameter2 OR @parameter2 Is Null)

  Also, you need to set the CancelSelectOnNullParameter Property of the SQLDataSource to false, otherwise the command will not execute when 1 or more parameters are Null

  No need for any code behind, and you can use this with or without using Stored Procedures

  • Edited by hans_v on Aug 22, 2010 11:12 PM
  • Edited by hans_v on Aug 23, 2010 03:53 PM
• • Reply
  

  sansan

  All-Star

  53942 Points

  8147 Posts

  Re: SqlDataSource and Parameters query

  Aug 22, 2010 11:15 PM|LINK

  hans_v

  SELECT * FROM [this_table] WHERE ([this_column] = @parameter1 OR @parameter1 Is Null)

  you haven't entered any text in the textbox. In that case, why do you need a where clause?

  Santhosh
• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: SqlDataSource and Parameters query

  Aug 23, 2010 03:24 PM|LINK

  sansan

  hans_v

  SELECT * FROM [this_table] WHERE ([this_column] = @parameter1 OR @parameter1 Is Null)

  you haven't entered any text in the textbox. In that case, why do you need a where clause?

   

  Why do you want to create another SQL query in case the user is leaving one or more fields blank, if you can do it simply like this, which is a well known method?

  In SQL Server, there's also another option, the COALESCE function

  SELECT * FROM [this_table] WHERE [this_column] = COALESCE(@parameter1,[this_column]) AND [that_column] = COALESCE(@parameter2,[that_column])