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.
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.
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.
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.
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
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
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])
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.
sansan
All-Star
53942 Points
8147 Posts
Re: SqlDataSource and Parameters query
Aug 22, 2010 06:11 PM|LINK
you can do this way
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.
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.
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.
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.
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
It is really giving me headache now.
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.
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
sansan
All-Star
53942 Points
8147 Posts
Re: SqlDataSource and Parameters query
Aug 22, 2010 11:15 PM|LINK
you haven't entered any text in the textbox. In that case, why do you need a where clause?
hans_v
All-Star
35986 Points
6550 Posts
Re: SqlDataSource and Parameters query
Aug 23, 2010 03:24 PM|LINK
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])