Urgent Help Required. Displaying data in a table.

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!!!
Choice-QuoteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tusharashahCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Choice-QuoteAuthor Commented:
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
tusharashahCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

smolamCommented:
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
Choice-QuoteAuthor Commented:
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
Choice-QuoteAuthor Commented:
Thanks Smolam... Just checking your link out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.