Solved

Show data in table to Users in aspx page

Posted on 2008-10-20
16
345 Views
Last Modified: 2013-12-17
I have a SQL Server 2005 table called Backup with the following fields:
RecordKey      varchar(50)      
Created            datetime      
TableName      nvarchar(100)      
FieldName      nvarchar(100)      
OldValue                      nvarchar(MAX)      (contains xml)
NewValue                      nvarchar(MAX)      (contains xml)
Notes            nvarchar(MAX)

This table is filled by my application when a change to data in another table is made. What I do is store the old values of the table being changed and the new values and store them in my Backup table above.

I'm trying to come up with an aspx page that could visible show the details in this backup table to the users. Basically i want them to see the created date, Table Name, OldValues,NewValues.

I was thinking of having a tree view maybe by createdDate..

Any ideas?
0
Comment
Question by:caoimhincryan
  • 8
  • 7
16 Comments
 

Author Comment

by:caoimhincryan
Comment Utility
I would be like something like a datalist control but if i could have it to be like a tree view by CreatedDate:

e.g.
19/10/2008
                  > Details here if exist
20/10/2008
                  > Details here if exist
                  > Details here if exist

Any ideas?
0
 
LVL 3

Expert Comment

by:TechSinger
Comment Utility
Add a SQL Data Source to the page which is invisible during runtime.  Connect the control to your database with a connection string.  If you don't have the connection string created yet, you can go through the wizard to create one while connecting the SQL Data Source.  Creating the connection string will ask ofr the server, dtabase, username and password of the database to connect to.   It will then allow you to choose the table(s) to use for the SQL Data Source.
Once you have added the SQL Data Source, you can then add a GridView to the page and link it to your SQL Data Source.  It's fairly straight forward on how to add columns to the GridView.  You can associate the columns in the GridView to those on the SQL Data Source which you can link to specific columns in the table(s) of the database.
Hope this helps.
0
 
LVL 20

Expert Comment

by:brwwiggins
Comment Utility
0
 

Author Comment

by:caoimhincryan
Comment Utility
>> TechSinger -- I know how to view them in a gridview. I just want to group them by date if you know what I mean?

>> brwwiggins -- Is that link relevant for me do you think? I just had a quick look and I will investigate it further if you think it is. As said above, I want to view the data grouped by Created date.
0
 
LVL 20

Expert Comment

by:brwwiggins
Comment Utility
It is sort of relevant. The idea is that you want to nest controls. I've done something similar using a DataList to list out the dates and then in the item template of the datalist use a gridview. The datasource on the gridview will use the date as a parameter for the select query to populate the gridview
0
 

Author Comment

by:caoimhincryan
Comment Utility
Are you saying to have a datasource for the datalist that just gets distinct dates and then have another datasource for your gridview that selects all the data?
0
 
LVL 20

Expert Comment

by:brwwiggins
Comment Utility
that's what I did. I'm sure there are more efficient ways to do it but that was the easiest method I came up with. Maybe someone has other ideas
0
 

Author Comment

by:caoimhincryan
Comment Utility
how exactly do i get my gridview to use the date as parameter from my datalist?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:caoimhincryan
Comment Utility
I cant seem to get my grid view to use date from my datalist as my select parameter. It doesnt seem to be catching it. I have place the gridview inside the ItemTemplate.
0
 
LVL 20

Expert Comment

by:brwwiggins
Comment Utility
I used a control parameter. Something similar to what is shown below

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.controlparameter(VS.80).aspx


            <asp:SqlDataSource ID="GridViewSource" runat="server" ConnectionString="<%$ ConnectionStrings:YourString %>"

                SelectCommand="SELECT * FROM Table WHERE (SelectedDate = @ParamDate)">

                <SelectParameters>

                    <asp:ControlParameter ControlID="DateFromDatalist" Name="paramDate" PropertyName="Text"

                        Type="String" />

                </SelectParameters>

            </asp:SqlDataSource>

Open in new window

0
 

Author Comment

by:caoimhincryan
Comment Utility
I have that but fro some reason its not picking up the value. I wonder am I inserting the gridview in the correct place. Below I am using a gridview to show my dates and then datalist to show all the occurences. does it look correct to you?
<asp:GridView ID="GridView1" runat="server" DataKeyNames="Created" AutoGenerateColumns="False"

            DataSourceID="AuditObjectDataSource">

            <Columns>

                <asp:BoundField DataField="Created" HeaderText="Created" SortExpression="Created" />

                <asp:TemplateField>

                    <ItemTemplate>

                        <asp:DataList ID="DataList1" runat="server" DataSourceID="uiAuditSelectByCreatedDate">

                            <ItemTemplate>

                RecordKey:

                                <asp:Label ID="RecordKeyLabel" runat="server" Text='<%# Eval("RecordKey") %>'></asp:Label><br />

                Created:

                                <asp:Label ID="CreatedLabel" runat="server" Text='<%# Eval("Created") %>'></asp:Label><br />

                Tablename:

                                <asp:Label ID="TablenameLabel" runat="server" Text='<%# Eval("Tablename") %>'></asp:Label><br />

                FieldName:

                                <asp:Label ID="FieldNameLabel" runat="server" Text='<%# Eval("FieldName") %>'></asp:Label><br />

                OldValue:

                                <asp:Label ID="OldValueLabel" runat="server" Text='<%# Eval("OldValue") %>'></asp:Label><br />

                NewValue:

                                <asp:Label ID="NewValueLabel" runat="server" Text='<%# Eval("NewValue") %>'></asp:Label><br />

                Notes:

                                <asp:Label ID="NotesLabel" runat="server" Text='<%# Eval("Notes") %>'></asp:Label><br />

                                <br />

                            </ItemTemplate>

                        </asp:DataList>

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

        </asp:GridView>

Open in new window

0
 
LVL 20

Expert Comment

by:brwwiggins
Comment Utility
I would use the datalist to show the dates and then a gridview to list the details. I've attached a sample of what I did for a different app. My datalist was populated by a SQL datasource but it can be anything.

The image button was just used to show/hide the details when clicked
<asp:DataList ID="DataList1" runat="server" DataSourceID="Datalistsource">

        <ItemTemplate>

            <asp:ImageButton ID="ImageButton1" runat="server" CommandName="Show" ImageUrl="~/Images/expand.gif"

                Style="position: relative" />

            <asp:Label ID="DistCodeLabel" runat="server" Text='<%# Eval("DistCode") %>'></asp:Label>

            <asp:Label ID="Distcode" runat="server" Style="position: relative" Text='<%# Eval("DistrictID") %>'

                Visible="False"></asp:Label><br />

            <asp:GridView ID="GridView1" runat="server" DataSourceID="GridViewSource"

                Visible="False" AutoGenerateColumns="False" DataKeyNames="LicenseKey">

                <Columns>

                    <asp:HyperLinkField DataNavigateUrlFields="LicenseKey" DataNavigateUrlFormatString="licensedetails.aspx?licensekey={0}"

                        DataTextField="LicenseKey" HeaderText="License Key" >

                        <ControlStyle ForeColor="Blue" />

                        <ItemStyle ForeColor="Blue" />

                    </asp:HyperLinkField>

                    <asp:BoundField DataField="CalQty" HeaderText="Qty" SortExpression="CalQty" />

                    <asp:BoundField DataField="QtyAvail" HeaderText="Avail" SortExpression="QtyAvail" />

                    <asp:BoundField DataField="CalNum" HeaderText="Ref Num" SortExpression="CalNum" />

                    <asp:BoundField DataField="CalPurchaser" HeaderText="Purchaser" SortExpression="CalPurchaser" />

                    <asp:BoundField DataField="CalExp" HeaderText="Exp Date" SortExpression="CalExp" />

                    <asp:BoundField DataField="PurchaseType" HeaderText="PurchaseType" SortExpression="PurchaseType" />

                </Columns>

            </asp:GridView>

            <asp:SqlDataSource ID="GridViewSource" runat="server" ConnectionString="<%$ ConnectionStrings:BESMgmtConnectionString %>"

                SelectCommand="SELECT LicenseKey, CalQty, CalNum, CalPurchaser, CalExp, PurchaseType, QtyAvail FROM LicenseKeys WHERE (DistrictID = @DistrictID)">

                <SelectParameters>

                    <asp:ControlParameter ControlID="Distcode" Name="DistrictID" PropertyName="Text"

                        Type="Int32" />

                </SelectParameters>

            </asp:SqlDataSource>

        </ItemTemplate>

        <AlternatingItemStyle BackColor="#F7F7F7" />

        <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />

        <ItemStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />

    </asp:DataList>

Open in new window

0
 

Author Comment

by:caoimhincryan
Comment Utility
I think this is helping. Just one thing, where have you place your datasource control called "Datalistsource"...
<asp:DataList ID="DataList1" runat="server" DataSourceID="Datalistsource">
 It doesnt seem to be the above code.
0
 

Author Comment

by:caoimhincryan
Comment Utility
I also like your idea of the image button  to show/hide the details when clicked..I'll need that as well. Did you use javascript to show/hide it?
0
 
LVL 20

Expert Comment

by:brwwiggins
Comment Utility
it was just below in the datalist
<asp:DataList ID="DataList1" runat="server" DataSourceID="Datalistsource">

        <ItemTemplate>

            <asp:ImageButton ID="ImageButton1" runat="server" CommandName="Show" ImageUrl="~/Images/expand.gif"

                Style="position: relative" />

            <asp:Label ID="DistCodeLabel" runat="server" Text='<%# Eval("DistCode") %>'></asp:Label>

            <asp:Label ID="Distcode" runat="server" Style="position: relative" Text='<%# Eval("DistrictID") %>'

                Visible="False"></asp:Label><br />

            <asp:GridView ID="GridView1" runat="server" DataSourceID="GridViewSource"

                Visible="False" AutoGenerateColumns="False" DataKeyNames="LicenseKey">

                <Columns>

                    <asp:HyperLinkField DataNavigateUrlFields="LicenseKey" DataNavigateUrlFormatString="licensedetails.aspx?licensekey={0}"

                        DataTextField="LicenseKey" HeaderText="License Key" >

                        <ControlStyle ForeColor="Blue" />

                        <ItemStyle ForeColor="Blue" />

                    </asp:HyperLinkField>

                    <asp:BoundField DataField="CalQty" HeaderText="Qty" SortExpression="CalQty" />

                    <asp:BoundField DataField="QtyAvail" HeaderText="Avail" SortExpression="QtyAvail" />

                    <asp:BoundField DataField="CalNum" HeaderText="Ref Num" SortExpression="CalNum" />

                    <asp:BoundField DataField="CalPurchaser" HeaderText="Purchaser" SortExpression="CalPurchaser" />

                    <asp:BoundField DataField="CalExp" HeaderText="Exp Date" SortExpression="CalExp" />

                    <asp:BoundField DataField="PurchaseType" HeaderText="PurchaseType" SortExpression="PurchaseType" />

                </Columns>

            </asp:GridView>

            <asp:SqlDataSource ID="GridViewSource" runat="server" ConnectionString="<%$ ConnectionStrings:BESMgmtConnectionString %>"

                SelectCommand="SELECT LicenseKey, CalQty, CalNum, CalPurchaser, CalExp, PurchaseType, QtyAvail FROM LicenseKeys WHERE (DistrictID = @DistrictID)">

                <SelectParameters>

                    <asp:ControlParameter ControlID="Distcode" Name="DistrictID" PropertyName="Text"

                        Type="Int32" />

                </SelectParameters>

            </asp:SqlDataSource>

        </ItemTemplate>

        <AlternatingItemStyle BackColor="#F7F7F7" />

        <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />

        <ItemStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />

    </asp:DataList>

    <asp:SqlDataSource ID="Datalistsource" runat="server" ConnectionString="<%$ ConnectionStrings:BESMgmtConnectionString %>"

        SelectCommand="SELECT DISTINCT dbo.DistInfo.DistCode, dbo.DistInfo.DistrictID FROM dbo.LicenseKeys INNER JOIN dbo.DistInfo ON dbo.LicenseKeys.DistrictID = dbo.DistInfo.DistrictID WHERE (dbo.LicenseKeys.Type = 0)">

    </asp:SqlDataSource>

Open in new window

0
 
LVL 20

Accepted Solution

by:
brwwiggins earned 500 total points
Comment Utility
I didn't use javascript. I had a sub in the codebehind that would cause a postback. however you could update it to use an update panel and the ajax toolkit.
    Private Sub Datalist1_ItemCommand(ByVal source As Object, _

        ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles DataList1.ItemCommand

        Dim expButton As ImageButton = e.Item.FindControl("imagebutton1")

        Dim licgridview As GridView = e.Item.FindControl("Gridview1")

        If e.CommandName = "Show" Then

            expButton.ImageUrl = "~/images/collapse.gif"

            expButton.CommandName = "Hide"

            licgridview.Visible = True
 

        End If

        If e.CommandName = "Hide" Then

            expButton.ImageUrl = "~/images/expand.gif"

            expButton.CommandName = "Show"

            licgridview.Visible = False
 

        End If

    End Sub

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now