Rehash of a dynamic Data source for a gridview

Question

I was excited to see where someone was doing exactly what I wanted to do with the sqldatasource and so I adapted it to my purposes but while I can run it without error I also get no data with or without entering data in the text boxes.

Here it the a link to the thread that inspired me Reference link for what I used: You'll note the answer by Paris is what I've adapted:

I've tried both techniques illustrated with the same results so I am really baffled. Here is my aspx code:

     <asp:SqlDataSource ID="InventoryList" runat="server" ConnectionString='<%$ ConnectionStrings:CMDB_testConnectionString %>' SelectCommand="SELECT [AssetID], [AssetType], [AssetName], [AssetShortDesc], [AssetLongDesc], [AssetAddNotes], [AssetManuf], [AssetModel], [AssetTag], [AssetSerialNum], [AssetAcqDate], [AssetDTAssetID], [AssetLocGrp], [AssetLoc1], [AssetLoc2], [AssetLoc3], [AssetParent], [AssetStatus], [AssetPropType], [AssetPrimUser], [AssetEntered], [AssetEnteredBy], [AssetOwner], [AssetCompany], [AssetPriIPAddr], [AssetPriMACAddr], [AssetPriOS], [AssetPriOSSP], [AssetNotes], [AssetAdminGrp], [AssetOrgID], [AssetOperType], [AssetOperStatus] FROM [cmdb_assets] 
     WHERE [AssetName] = CASE @AssetName THEN [AssetName] END AND [AssetType] = CASE @AssetType THEN [AssetType] END AND [AssetManuf] = CASE @AssetManuf THEN [AssetManuf END AND  [AssetModel] = CASE @AssetModel THEN [AssetModel] END">

Followed by:

        <SelectParameters>
        <asp:ControlParameter Name="AssetName" ControlID="AssetNameTbx" Type="String" />
       <asp:ControlParameter Name="AssetType" ControlID="AssetTypeTbx" Type="String" />
       <asp:ControlParameter Name="AssetManuf" ControlID="AssetManufTbx" Type="String" />
       <asp:ControlParameter Name="AssetModel" ControlID="AssetModelTbx" Type="String" />
    </SelectParameters> 
    </asp:SqlDataSource>

As the first thread responder was shooting for, what I want to do is allow input of information in one or more text boxes and then filter the results onto my gridview via this select. Thoughts? Ken...

Thought I would provide an output of the results of the two examples I've used above and the latest that I just tried supplied by another forum member tested on 2014 which also did the same nodata return result. Just for perspective of the result. Really of no value but still. Ken... What the datasource ended up as:

  <asp:SqlDataSource ID="InventoryList" runat="server" ConnectionString='<%$ ConnectionStrings:CMDB_testConnectionString %>' SelectCommand="SELECT [AssetID], [AssetType], [AssetName], [AssetShortDesc], [AssetLongDesc], [AssetAddNotes], [AssetManuf], [AssetModel], [AssetTag], [AssetSerialNum], [AssetAcqDate], [AssetDTAssetID], [AssetLocGrp], [AssetLoc1], [AssetLoc2], [AssetLoc3], [AssetParent], [AssetStatus], [AssetPropType], [AssetPrimUser], [AssetEntered], [AssetEnteredBy], [AssetOwner], [AssetCompany], [AssetPriIPAddr], [AssetPriMACAddr], [AssetPriOS], [AssetPriOSSP], [AssetNotes], [AssetAdminGrp], [AssetOrgID], [AssetOperType], [AssetOperStatus] FROM [cmdbv_Assets_CInTrac] where AssetID=isnull(@AssetID,AssetID) and AssetName=isnull(@AssetName,AssetName) and AssetType=isnull(@AssetType,AssetType) and AssetManuf=isnull(@AssetManuf,AssetManuf) and AssetModel=isnull(@AssetModel,AssetModel) and AssetTag=isnull(@AssetTag,AssetTag) and AssetSerialNum=isnull(@AssetSerialNum,AssetSerialNum) and AssetDTAssetID=isnull(@AssetDTAssetID,AssetDTAssetID) and AssetLocGrp=isnull(@AssetLocGrp,AssetLocGrp) and AssetLongDesc=isnull(@AssetLongDesc,AssetLongDesc) and AssetOrgID=isnull(@AssetOrgID,AssetOrgID) and AssetPriIPAddr=isnull(@AssetPriIPAddr,AssetPriIPAddr) and AssetStatus=isnull(@AssetStatus,AssetStatus)" CancelSelectOnNullParameter="false">

So, just so I'm clear, you basically have a grid with multiple fields, and you have some textboxes that should filter if they have values and not filter if they don't. Is that right? — Nikki9696, Jun 23 '16 at 20:57
you didn't have a `]` in `[AssetManuf`; is it copy paste error? — techspider, Jun 23 '16 at 21:16
@techspider copy paste error correct the ] was in the code. yes. — Ken Carter, Jun 24 '16 at 13:09
And yes, The objective is to provide a series of text boxes that may or may not be filled out with filter text for various fields. If left blank those should be treated as effective * for that column allowing anything to pass rather than filter anything. Therefore if you pulled it up with no text you would see all records in the table. If you entered Laptop in 'Asset Type' you'd get just the a listing of the laptops. if you then added HP to the Asset Mfg box you'd only get HP laptops. — Ken Carter, Jun 24 '16 at 13:14

score 0 · Accepted Answer · answered Jun 23 '16 at 21:38

0

This seems to work if I understand what you're after. Tested with Adventureworks 2014 (sql server).

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorks2014ConnectionString %>"
        SelectCommand="SELECT [Name], [ProductID], [Color], [Size], [ProductNumber], [Style] FROM Production.[Product]
        where name=isnull(@ProductName,name) and Color=isnull(@Color,Color) and ProductNumber=isnull(@ProductNumber,ProductNumber)" CancelSelectOnNullParameter="false">
        <SelectParameters>
            <asp:ControlParameter Name="ProductName" ControlID="ProductName" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="Color" ControlID="Color" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="ProductNumber" ControlID="ProductNumber" Type="String" ConvertEmptyStringToNull="true" />
        </SelectParameters>
    </asp:SqlDataSource>
    <form id="form1" runat="server">
        <div>
            Product Name:
            <asp:TextBox ID="ProductName" runat="server"></asp:TextBox>
        </div>
        <div>
            Color:
            <asp:TextBox ID="Color" runat="server"></asp:TextBox>
        </div>
        <div>
            Product Number:
            <asp:TextBox ID="ProductNumber" runat="server"></asp:TextBox>
        </div>
        <div><asp:Button ID="submit" runat="server" Text="filter" /></div>
        <div>
            <asp:GridView runat="server" DataSourceID="SqlDataSource1"></asp:GridView>
        </div>
    </form>
</body>
</html>

answered Jun 23 '16 at 21:38

Nikki9696

6,260
1
28
23

I'm on 2008 but will give this a shot and see how it goes... will report back after I've had a chance to test. – Ken Carter Jun 24 '16 at 13:20
Didn't error at all but gave no data back just as the two other examples did. Almost like there is nothing getting from the text boxes to the query. going to do some debugging on that to see if there is something stopping it up there. – Ken Carter Jun 24 '16 at 14:01
Please make sure you check that you have the isnull check as well as the controls set to ConvertEmptyStringToNull (true) and the query set to CancelSelectOnNullParameter (false) – Nikki9696 Jun 24 '16 at 14:03
using AssetModel=isnull(@AssetModel,AssetModel) in the where and in the selectParameters .... Do I also need to add CancelSelectOnNullParameter="false" after that as well then? – Ken Carter Jun 24 '16 at 14:11
Yes, otherwise it won't actually run the query when the parameter is null (it's associated with the selectCommand, not the parameter) – Nikki9696 Jun 24 '16 at 14:20
Seems to choke on CancelSelectOnNullParameter=false when I tried to add that on the end of the statement is that the appropriate placement? – Ken Carter Jun 24 '16 at 14:29
No, please check the code I posted here, it's in there. It belongs to the SqlDataSource so it goes up there – Nikki9696 Jun 24 '16 at 14:53
The nature of the forum layout I had missed that.. thanks for pointing it out... so now.. with that everything is working perfect! – Ken Carter Jun 24 '16 at 15:40
hopping you can catch this and comment. I just got finished making the final edits on my code with your suggestions which are working wonderfully and wanted to ask if I could and what I would need to do to these VAR=isnull(@VAR,VAR) segments to make them function as 'like' or 'contains' ... That would make this page function absoutly perfectly for my purposes, but I am not sure how I'd get it to work. Due to space limitations I will add another comment with the line as it is now so you have tat as reference. – Ken Carter Jul 07 '16 at 18:56
Well it still was too long so I put it as an addendum to the original post... regards, Ken.. – Ken Carter Jul 07 '16 at 18:58

Rehash of a dynamic Data source for a gridview

1 Answers1