Solved

Show data in table to Users in aspx page

Posted on 2008-10-20
16
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 

Author Comment

by:caoimhincryan
ID: 22757728
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
ID: 22757891
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
ID: 22757967
0
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 

Author Comment

by:caoimhincryan
ID: 22758573
>> 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
ID: 22758671
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
ID: 22759005
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
ID: 22759047
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
ID: 22759067
how exactly do i get my gridview to use the date as parameter from my datalist?
0
 

Author Comment

by:caoimhincryan
ID: 22759246
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
ID: 22759339
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
ID: 22759422
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
ID: 22759512
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
ID: 22759671
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
ID: 22759776
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
ID: 22759807
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
ID: 22759847
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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