?
Solved

Urgent Help Required. Displaying data in a table.

Posted on 2004-11-18
6
Medium Priority
?
198 Views
Last Modified: 2008-02-01
Hi All,

After a little help with the following...

I have a database which contains visitor and order data by data... like this;

DATE               VISITORS         ORDERS
11/17/2004      20                    2
11/18/2004      15                    1
11/19/2004      25                    3

I'd like to diplay this data in a table using ASP.net, in the following 'cross tab' style format;

                    11/17/2004      11/18/2004        11/19/2004  ...and so on for the selected date range
VISITORS      20                   15                     25
ORDERS        2                     1                       3


Here is my VB code so far...

Dim startdt As String = "11/17/2004" 'Session("fromdate")
Dim enddt As String = "11/18/2004" 'Session("todate")

        Dim myConnection As New SqlConnection("data source=;initial catalog=;uid=;password=;")

        Dim motorcycledata As New SqlClient.SqlCommand("select * from counterv2 WHERE product ='motorcycle' AND countdate >= '" & startdt & "' AND countdate <= '" & enddt & "' ORDER BY countdate", myConnection)

        ' this is the motorcycle order data
        Dim damotorcycle As New SqlClient.SqlDataAdapter(motorcycledata)

        'instantiate a dataset
        Dim dsmotorcycle As New DataSet
        Try
            'populate the dataset
            damotorcycle.Fill(dsmotorcycle, "motorcycledatat")

            Dim myDV As DataView
            myDV = dsmotorcycle.Tables("motorcycledatat").DefaultView

            DataGrid1.DataSource = dsmotorcycle
            DataGrid1.DataMember = "motorcycledatat"
            DataGrid1.DataBind()

        Finally

           'check on connection status
            If myConnection.State = ConnectionState.Open Then
                myConnection.Close()
            End If
            'get rid of connection object
            myConnection.Dispose()
   
    End Try


And here is my ASP.net HTML code...

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="dataExported.aspx.vb" Inherits="vts.dataExported" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
      <HEAD>
            <title>dataExported</title>
            <meta content="Microsoft Visual Studio.NET 7.0" name="GENERATOR">
            <meta content="Visual Basic 7.0" name="CODE_LANGUAGE">
            <meta content="JavaScript" name="vs_defaultClientScript">
            <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
      </HEAD>
      <body MS_POSITIONING="GridLayout">
            <form id="Form1" method="post" runat="server">
                  <asp:datagrid id="DataGrid1" style="Z-INDEX: 100; LEFT: 120px; POSITION: absolute; TOP: 96px"
                        runat="server" Height="12px" Width="500px" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px"
                        BackColor="White" CellPadding="3" GridLines="Horizontal" ShowHeader="False">


                        <FooterStyle ForeColor="#4A3C8C" BackColor="#B5C7DE"></FooterStyle>
                        <SelectedItemStyle Font-Size="Small" Font-Names="Tahoma" Font-Bold="True" ForeColor="#F7F7F7" BackColor="#738A9C"></SelectedItemStyle>
                        <EditItemStyle Font-Size="Smaller" Font-Names="Tahoma"></EditItemStyle>
                        <AlternatingItemStyle Font-Size="Smaller" Font-Names="Tahoma" BackColor="#F7F7F7"></AlternatingItemStyle>
                        <ItemStyle Font-Size="Smaller" Font-Names="Tahoma" ForeColor="#4A3C8C" BackColor="#E7E7FF"></ItemStyle>
                        <HeaderStyle Font-Names="Tahoma" Font-Bold="True" ForeColor="#F7F7F7" BackColor="#4A3C8C"></HeaderStyle>
                        <PagerStyle HorizontalAlign="Right" ForeColor="#4A3C8C" BackColor="#E7E7FF" Mode="NumericPages"></PagerStyle>
                  </asp:datagrid>&nbsp;</form>
      </body>
</HTML>



Any help would be very much appreciated!!!
0
Comment
Question by:Choice-Quote
  • 3
  • 2
6 Comments
 
LVL 18

Accepted Solution

by:
tusharashah earned 2000 total points
ID: 12617306
0
 

Author Comment

by:Choice-Quote
ID: 12620347
Tushar,

Many thanks, the link was a great help... almost there!

I would like to customise the column names... I've tried doing this using the DataGrid 'Property Builder'... I switch off auto generate column names at run time and type in my own custom column list... when I run the script I get a message saying that the column names can not be found in my data source??? Any ideas???

Here is the VB.asp code so far;

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here

        dsTest_BindData()

    End Sub

    Private Function dsTest_BindData()

        Dim startdt As String = "11/17/2004" 'Session("fromdate")
        Dim enddt As String = "11/18/2004" 'Session("todate")

        Dim myConnection As New SqlConnection("data source=;initial catalog=;uid=;password=;")
        Dim motorcycledata As New SqlClient.SqlCommand("select countdate, count, qcount from counterv2 WHERE pid ='motorcycle' AND countdate >= '" & startdt & "' AND countdate <= '" & enddt & "' ORDER BY countdate", myConnection)

        Dim damotorcycle As New SqlClient.SqlDataAdapter(motorcycledata)

        Dim ds As New DataSet
        Dim new_ds As New DataSet
        Dim my_DataView As DataView



        Try

            damotorcycle.Fill(ds, "motorcycle")

            'Call the Flip DataSet()
            new_ds = FlipDataSet(ds)
            my_DataView = new_ds.Tables(0).DefaultView

            DataGrid1.DataSource = new_ds
            DataGrid1.DataMember = "motorcycle"
            DataGrid1.DataBind()

        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
    End Function


    Private Function FlipDataSet(ByVal my_DataSet As DataSet) As DataSet
        Dim ds As New DataSet
        Dim dt As DataTable

        Try



            For Each dt In my_DataSet.Tables
                Dim table As New DataTable
                Dim i As Integer
                Dim j As Integer
                Dim k As Integer

                For i = 0 To dt.Rows.Count
                    table.Columns.Add(Convert.ToString(i))
                Next
                Dim r As DataRow
                For k = 0 To dt.Columns.Count - 1
                    r = table.NewRow()
                    r(0) = dt.Columns(k).ToString()
                    For j = 1 To dt.Rows.Count
                        r(j) = dt.Rows(j - 1)(k)
                    Next
                    table.Rows.Add(r)
                Next

                ds.Tables.Add(table)
            Next



            Return ds

        Catch ex As Exception
            Response.Write(ex.Message)
        End Try


    End Function

End Class


and then the HTML code;

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="caDataVwr.aspx.vb" Inherits="vts.caDataVwr"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
      <HEAD>
            <title>caDataVwr</title>
            <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
            <meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
            <meta content="JavaScript" name="vs_defaultClientScript">
            <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
      </HEAD>
      <body MS_POSITIONING="GridLayout">
            <form id="Form1" method="post" runat="server">
                  <asp:datagrid id="DataGrid1" style="Z-INDEX: 101; LEFT: 240px; POSITION: absolute; TOP: 144px"
                        runat="server" ShowHeader="False" CellPadding="10"></asp:datagrid></form>
      </body>
</HTML>


Regards,
Craig
0
 
LVL 18

Expert Comment

by:tusharashah
ID: 12624928
Hello Craig,

Do you have switched of AutoGenerated column? I dont see it in the HTML code you have provided. Nor do I see any bound columns added to your DataGrid.
Your DataGrid will be defined something like following in your HTML

<asp:datagrid id="DataGrid1" runat="server" AutoGenerateColumns="False" style="Z-INDEX: 101; LEFT: 240px; POSITION: absolute; TOP: 144px" ShowHeader="True" CellPadding="10">


Also add Bound column to your DataGrid if you have switched of AutoGenerated Column and use the same column name.

-tushar
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:smolam
ID: 12625515
Hi Craig, another solution which works very effectively for me is to do it via a SQL stored procedure:

http://www.sqlteam.com/item.asp?ItemID=2955
0
 

Author Comment

by:Choice-Quote
ID: 12643531
The code is shown with AutoGenerateColumns enabled... although I have tried setting this to false, and binding my columns... this is when I get the error detailed above.

Craig
0
 

Author Comment

by:Choice-Quote
ID: 12643535
Thanks Smolam... Just checking your link out.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

839 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