Solved

Vb.Net Database Report

Posted on 2004-04-26
4
390 Views
Last Modified: 2007-12-19
Hello Experts,

I have a Database and i have it loaded into a Form Via an DataGrid.  It contains Field such as a Persons Name and Sallary they are making.

I want to be able to make two new Forms which

1.  Will List all the Names from the Database and Sallary, in which i can do additons/Subractions/divison... with the sallary number before it gets displayed.  Prefferably using a ListBox, but others is fine.  I don't want to use Crystal Reports unless it would be a lot easier

2.  I want to be able to do a Bar Graph showing for example the Maximum, Minimum and Average Sallary

Thank you for your Time

~Linkkoh
0
Comment
Question by:linkkoh
4 Comments
 
LVL 2

Author Comment

by:linkkoh
Comment Utility
Alright I have answered most of the questions by myself now:

Dim iRow As Integer
        Dim iCol As Integer

        'Putting the Values into the Array
        rowcount = Employee1.Tables(0).Rows.Count - 1
        For iRow = 0 To rowcount
            Data1(iRow) = Employee1.Tables(0).Rows(iRow).Item(1) & " " & Employee1.Tables(0).Rows(iRow).Item(2)
            data2(iRow) = Employee1.Tables(0).Rows(iRow).Item(4) / 26
        Next


Now But i still need to know how to make a Bar Chart.  I have calculated Average, Max, Min now i just need to graph them.  Any suggestions??
0
 
LVL 1

Accepted Solution

by:
Trancedified earned 75 total points
Comment Utility
Use Excel? They have bar graphs and such, but u have to write your results into Excel, better than crystal repots in my opinion.
It will fire up a SaveFile dialog box so you can navigate to where you want to save your file, write to Excel, then prompt if you want to open it.

            Dim strSQL As String
            strSQL = "SELECT * FROM TD4"  'Replace this w/ your actual query

            objAdapter = New SqlDataAdapter(strSQL, objConn)
            objDataset = New DataSet
            objAdapter.Fill(objDataset)

            'Opens the Save Dialog box
            Dim objSaveDialog As New System.Windows.Forms.SaveFileDialog

            'If the user omits Extension, add for them. Specify
            'the default extension to use as well.
            objSaveDialog.AddExtension = True
            objSaveDialog.DefaultExt = ".xls"

            'Prompt the user to create a file if it doesn't exist or overwrite if it does exist.
            objSaveDialog.CreatePrompt = False
            objSaveDialog.OverwritePrompt = True  'In case you want to keep this file or else overwrite it

            'Show the help button
            objSaveDialog.ShowHelp = False

            'Set the Initial Directory
            objSaveDialog.InitialDirectory = "C:\"

            'Set the Filter of File types to be opened
            objSaveDialog.Filter = "Microsoft Excel File (*.xls)|*.xls|" & _
                "All files (*.*)|*.*"

            'Set the Default Filter Index to the first item in the .Filter
            objSaveDialog.FilterIndex = 1

            'Verify the file and path exist
            objSaveDialog.CheckPathExists = True

            'Return the path of the file a shortcut references if it is a shortcut .lnk file the dialog found.
            'Set to False to return the actual location of the
            '.lnk file instead.
            objSaveDialog.DereferenceLinks = True

            'Initial filename to be used
            objSaveDialog.FileName = "test.xls"

            'Title of the Dialog
            objSaveDialog.Title = "Save As"

            'Allow only Valid File Names (no ",|,<,> characters)
            objSaveDialog.ValidateNames = True

            'If you want the dialog to open using the last directory you were in, then set this to
            'False and comment the .InitialDirectory.
            objSaveDialog.RestoreDirectory = False

            If objSaveDialog.ShowDialog = DialogResult.Cancel Then

                objSaveDialog.Dispose()

            Else
                'Open the file as a stream to read or write
                Dim objExcel As Object
                Dim objBook As Object
                Dim objSheet As Object
                objExcel = CreateObject("Excel.Application")
                objBook = objExcel.Workbooks.Add
                objSheet = objBook.Worksheets(1)

                'Set up the Query Table and tell it where to find the data.
                Dim objQryTable As Object
                objQryTable = objSheet.QueryTables.Add("OLEDB;Provider=sqloledb;Data Source=MyServerName" & _
                    ";Initial Catalog=MySQLDatabase" & _
                    ";Trusted Connection=Yes;Integrated Security=SSPI;", objSheet.Range("A1"), strSQL)
                objQryTable.RefreshStyle = 2 ' x1InsertEntire Rows = 2
                objQryTable.Refresh(False)

                objExcel.DisplayAlerts = False 'Must be false or else you are being asked twice if you want to overwrite existing .xls file
                objBook.SaveAs(objSaveDialog.FileName)
                'Clear everything so you can display it to the user

                objQryTable = Nothing
                objSheet = Nothing
                objBook = Nothing
                objExcel.Quit()
                objExcel = Nothing

               'Would you like to open the file you just saved?
                Dim Response As String
                Response = MsgBox("Would you like to open " & objSaveDialog.FileName & "?", MsgBoxStyle.Information.YesNo)

                If Response = vbYes Then 'This will open Excel with the new workbook you just created. This step is not necessary, but I find it nice.
                    Dim xlApp As Excel.Application
                    Dim xlMappe As Excel.Workbook
                    xlApp = New Excel.Application
                    xlApp.Visible = True
                    xlMappe = xlApp.Workbooks.Open(objSaveDialog.FileName)
                Else 'If vbNo
                    'Do nothing, just get's rid of the Msgbox.
                End If

            End If

Good luck!

Chris
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now