Solved

Vb.Net Database Report

Posted on 2004-04-26
4
402 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
ID: 10924474
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
ID: 11054715
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

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

778 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