Solved

Show data in table to Users in aspx page

Posted on 2008-10-20
16
349 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
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

810 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