Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Example of how to do a SQL Server YTD Summary from a Table

Posted on 2004-08-18
9
Medium Priority
?
407 Views
Last Modified: 2012-06-27
Hello Gurus,

This is urgent.

I need a (Best Practice) example of a function on how to do a YTD Summary from a SQL Server Table from an Asp.net web form using VB.  I will then need to place them into 3 variables: YtdHrs, YtdQty and Ytd(OnOrder)
The query I would use would be:

SELECT Sum(Hrs), Sum(Qty), Sum(OnOrder) From Orders Where EnterDate > #12/31/2003#

(I'm not sure if my date syntax is correct. Please correct me if its wrong)

Of particular importance to me is how to extract the information from the returned dataset and placing them into the variables.

Thank you!

Ted
0
Comment
Question by:tcalbaz
[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
  • 6
  • 2
9 Comments
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11831455
Try this: SELECT Sum(Hrs) AS Hours, Sum(Qty) AS Quantity, Sum(OnOrder) As OnOrder From Orders Where EnterDate > '12/31/2003'

Then create your dataset from it and bind it to a datagrid, etc.  you will be able to reference the values using the standard datagrid terminologyr:
eg....
dg.items("Hours")
dg.items("Quantity")
dg.items("OnOrder")

Regards,

Aeros
0
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 11831564
'Set the value of  and SQLConnection SQLDataAdapter
strSQL = "SELECT Sum(Hrs) AS Hours, Sum(Qty) Quantity, Sum(OnOrder) OnOrder From Orders Where EnterDate > #12/31/2003#"
MySQLDataAdapter.Fill(strSQL,"YTD_SUMMARY")

if (MySQLDataAdapter.Tables["YTD_SUMMARY"].Rows.Count >  0) then
   Dim dt As DataTable = MySQLDataAdapter.Tables["YTD_SUMMARY"]
   Dim dr As DataRow = dt.Rows[0]
   Response.Write("Hours: " & dr["Hours"])
   Response.Write("Quantity: " & dr["Quantity"])
   Response.Write("OnOrder: " & dr["OnOrder"])
end if

HTH, :)

Best, nauman
0
 
LVL 1

Author Comment

by:tcalbaz
ID: 11832221
Nauman,
I think your example comes close to what I need to do.  Below is my the actual function I am constructing ased on your idea.  But it is stopping at my Adapter Reference.  Any Suggestions?
Thank You

Ted

-----------------------------------------------------------
    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
        'Set the value of  and SQLConnection SQLDataAdapter
        Dim MyDataSet As DataSet
        MyDataSet = New DataSet("aNewDataSet")

        Dim MyAdapter As SqlClient.SqlDataAdapter
        Dim MyConnect As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("SQLDB_Test"))
        Dim StrSQL As String
        StrSQL = "SELECT Sum(Hrs) AS Hours, Sum(LT) AS LostTime, Sum(DL) AS DaysLost From TBLEHS620 Where EnterDate > #12/31/2003#"

        MyAdapter.Fill(MyDataSet)   <<<<<<<<<<<<<ERROR: Object reference not set to an instance of an object.
        Dim dt As DataTable = MyDataSet.Tables(0)
        Dim dr As DataRow = dt.Rows(0)

        If (MyDataSet.Tables(0).Rows.Count > 0) Then
            Me.txtHrs.Text = dr("Hours")
            Me.txtLostTime.Text = dr("LostTime")
            Me.txtDaysLost.Text = dr("DaysLost")
        Else
            Me.txtHrs.Text = 0
            Me.txtLostTime.Text = 0
            Me.txtDaysLost.Text = 0
        End If

    End Sub
-----------------------------------------------------------
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 25

Expert Comment

by:nauman_ahmed
ID: 11832618
Change  Dim MyDataSet As New DataSet

-Nauman
0
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 11832656
oops sorry, I was wrong. I have the following in C#. Let me do it in VB for more clarity:

private SqlConnection sqlConnection;
private SqlDataAdapter sqlDataAdapter;
private SqlCommand sqlCommand;
sqlConnection.Open();
DataSet ds = new DataSet();
sqlCommand = new SqlCommand();
sqlCommand.CommandText = SQLCommand;
sqlDataAdapter = new SqlDataAdapter(SQLCommand,sqlConnection);
sqlDataAdapter.Fill(ds,TableName);

0
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 11832690
'Set the SQLConnection
Dim MYDataAdapter As SQLDataAdapter
Dim MyDataSet As New DataSet
Dim MySQLCommand As new SQLCommand
MySQLCommand.CommandText = "SELECT Sum(Hrs) AS Hours, Sum(LT) AS LostTime, Sum(DL) AS DaysLost From TBLEHS620 Where EnterDate > #12/31/2003#"

MyDataAdapter = new SQLDataAdapter(SQLComamnd,MySQLConnection)
MyDataAdapter.Fill(MyDataSet,"YTD_SUMMARY")

Please verify if this is working.....

Best, Nauman
0
 
LVL 25

Accepted Solution

by:
nauman_ahmed earned 2000 total points
ID: 11832870
Also to not confuse the readers: the C# code I have is written as the following function that returns a dataset. tcalbaz you can use the same approach so that you dont have to define the data access layer again and again. Actually I have defined a whole separate class for just Data Access. I call it DatabaseManagement and whenever I need to get the db data, I call it in the following way:

DataSet ds = new DataSet();
DatabaseManagment dbManagement = new DatabaseManagement();
string strSQL = "SELECT * FROM CUSTOMERS";
ds = dbManagement.GetRecords(strSQL,"CUSTOMERS");
DataTable dt = ds.Tables["CUSTOMERS"];
.
.
.
.
//or for a more quick approach....
DataTable dt = dbManagement.GetRecords(strSQL,"CUSTOMERS").Tables["CUSTOMERS"];


This is the function I have defined in the class:

public DataSet GetRecords(string SQLCommand,string TableName)
            {
                  try
                  {
                        sqlConnection.Open();
                        DataSet ds = new DataSet();
                        sqlCommand = new SqlCommand();
                        sqlCommand.CommandText = SQLCommand;
                        sqlDataAdapter = new SqlDataAdapter(SQLCommand,sqlConnection);
                        sqlDataAdapter.Fill(ds,TableName);
                        
                        return ds;
                  
                  }
                  catch(Exception ex)
                  {
                        throw ex;
                  }
                  finally
                  {
                        sqlConnection.Close();

                  }
                  
            }

Make your life easier dude.....;)

-Nauman
0
 
LVL 1

Author Comment

by:tcalbaz
ID: 11834954
Nauman,
Thanks you for your assistance getting me on the right track.
Here is the final function completed:

  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
        Dim Lastyr As String

        'Year to Date Summary Function
        Dim myQuery As String
        Lastyr = CStr(Now.Year - 1)
        myQuery = "SELECT Sum(Hrs) AS Hours, Sum(LostTime) AS LostTime, Sum(DaysLost) AS DaysLost From TBLEHS620_2 "
        myQuery = myQuery & " WHERE (DateEntered > CONVERT(DATETIME, '" & Lastyr & "-12-31 23:59:59', 102))"

        Dim conn As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("SQLDB_Test"))
        conn.Open()
        Dim ds As New DataSet
        Dim adapter As New SqlClient.SqlDataAdapter
        Dim MyCmd As SqlClient.SqlCommand
        adapter.SelectCommand = New SqlClient.SqlCommand(myQuery, conn)
        adapter.Fill(ds)
        Dim dt As DataTable = ds.Tables(0)
        Dim dr As DataRow = dt.Rows(0)
        If (ds.Tables(0).Rows.Count > 0) Then
            Me.txtHrs.Text = dr("Hours")
            Me.txtLostTime.Text = dr("LostTime")
            Me.txtDaysLost.Text = dr("DaysLost")
        Else
            Me.txtHrs.Text = 0
            Me.txtLostTime.Text = 0
            Me.txtDaysLost.Text = 0
        End If

    End Sub
---------------------------------------------
All the best

Ted
0
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 11835054
Thanks ted :) I am glad that my solution worked for you....;) But remember .net is all about object oriented....Always think of reusing the code.....

Cheers, Nauman
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

722 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