Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

Vb.Net Database Report

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
linkkoh
Asked:
linkkoh
1 Solution
 
linkkohAuthor Commented:
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
 
TrancedifiedCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now