Solved

Vb.Net Database Report

Posted on 2004-04-26
4
395 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
As soon as i click a button on the form,it mnimises. 11 30
Create XML 5 46
Showdialog 8 29
Visual Studio - "Windows Form Designer generated code" 2 39
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

12 Experts available now in Live!

Get 1:1 Help Now