A Simple Strange Query..........

I am having in writing a simple select command......
here's My Problem

I am using the ASP SQLDataSources in VS2005... and my need is that i need to show Products details in a Gridview.....
Actually through QueryString a StoreID is being fetched.... and the Products under those StoreID are shown.... if there is nothing in query string then it should show all the results... ...... It means that my querystring should be something like this

select * from tblProducts where StoreID = <xyz>

and <xyz> should be some thing that could show all the rows in that table ...... i mean that it should show all the possible results that can be shown through...

select * from tblProducts

Dhaliwal

1) Your select command should be a in stored procedure which takes the StoreId as an argument.

2) You should never use SELECT * in the query - only the columns being used in the Gridview should be included.

3) The tbl prefix on tblProducts is depreciated. Also it should be in the singular as each row contains information on a single product. A better name would be Product.

4) The switch is much easier to achieve that is commonly imagined. Assuming that StoreId is an Integer primary key then the lowest value will be 1. Your stored procedure will then contain

IF @.StoreId > 0

SELECT A, B, C FROM Product Where StoreId = @.StoreId

ELSE

SELECT A, B, C FROM Product

(If should be noted that this is not very suitable for a database with a high volume of queries - in this case you should use two stored procedures one for all records and one for a list select selective by store.)

5) Run the stored procedure getting a dataset and assign the dataset to the GridView.

|||hi there,
thank you very much for such a quick reply..........

I fully agree with you that using Stored procedured is a good Idea/Methodology
Actually i used to do these task through coding each time but i really want to try these new components and see how they works...(under all kind of situations).... so is it possible without using stored procedure......

and 1 more thing i want to ask...... i couldn't get your 3rd point totally....... what r u trying to say in that.... would you please elaborate it... wht the prefixtbl is depreciated...!!!|||

Here is how you can do it:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:yourConnectionString%>"CancelSelectOnNullParameter="false" SelectCommand="SELECT * FROM tblProducts WHERE productID=ISNULL(@.productID,productID)"> <SelectParameters> <asp:QueryStringParameter Name="productID" QueryStringField="productID" /> </SelectParameters> </asp:SqlDataSource>

|||

Dhaliwal:

hi there,
thank you very much for such a quick reply..........

I fully agree with you that using Stored procedured is a good Idea/Methodology
Actually i used to do these task through coding each time but i really want to try these new components and see how they works...(under all kind of situations).... so is it possible without using stored procedure......

and 1 more thing i want to ask...... i couldn't get your 3rd point totally....... what r u trying to say in that.... would you please elaborate it... wht the prefixtbl is depreciated...!!!

Hi Dhaliwal,
I think that the use of a stored procedure would be good because all your logic would be into the stored procedure. By other hand The SqlDataSource control is a very good control, very easy to use but there is something I do not like, it embeds the SQL code into the presentation layer (aspx). I would suggest that you could take a look atwww.sqlnetframework.com. There is a SqlStoreDataSource control which works in the same way that the SqlDataSource control but it doesn't embed the SQL code into the presentation layer. The SqlStoreDataSource control creates a repository where all your SQL code is stored. You can re-use your SQL code in other application (e.g. Windows app). The SqlDataSource control is very easy to use, to learn and it helps you to create an application with a good architecture.

I hope it helps you in your projects.

Luis Ramirez.
www.sqlnetframework.com

|||Hi Luis....

Thanks for expressing your views about howData should be fetched from Database and as i said earlier i agree that using Store Proc is a better method... but i was just practicing and really wanted to try SQLDatasource completely. Moreover i was kean to know about SQL Query that could result in what i was looking for....... and whatLimno provided was exactly i was looking for...... i.e usingIsNULL in select Query (Nice Trick)

And also Thanks alot for sharing views about the SQLDataSource controls(Benifits/Limitations... etc)|||

There was a time when the prefix was used to make it obvious what were the table names, however in the statement

SELECT Avalue FROM Bert WHERE Id = 1

it is obvious that Bert is a table. The use of the tbl was quite unnecessary. Table name can get quite long enough without using tbl!

There are some contexts where some form of prefix is useful, however given a modern utility such as SQL Server Management Studio, which lists tables separatly from say stored procedures, the tbl prefix is unnecessary.

HTH