Hack Forums
[.NET] Problem Displaying Query Results in Data Grid View - Printable Version

+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Software Development (https://hackforums.net/forumdisplay.php?fid=118)
+---- Thread: [.NET] Problem Displaying Query Results in Data Grid View (/showthread.php?tid=6030955)

Problem Displaying Query Results in Data Grid View - Ferrari F40 - Dec 7, 2019

This is for a mutual fund project

So basically the program reads in records from a text file.
The user should select what report they wanna run from a list box.  
Then they’ll type the %’s they wanna use in the report in the text boxes. The reports use LINQ to pull the right records.

The information should then be displayed in a data grid view control

My problem is only the headers appear after the user runs it and not sure why

Would appreciate any feedback

Code
Public Class frmMutualFunds
 'Create structure to contain mutual fund data
Structure MutualFunds
   Dim symbol As String
   Dim description As String
   Dim totalReturnThreeMonth As Double
   Dim totalReturnSixMonth As Double
   Dim oneYearAverageAnnualReturn As Double
   Dim threeYearAverageAnnualReturn As Double
   Dim fiveYearAverageAnnualReturn As Double
   Dim tenYearAverageAnnualReturn As Double
   Dim totalReturnYTD As Double
   Dim averageAnnualReturnSinceInception As Double
 End Structure

 'Create array to hold data as it's read
Dim readData(1000) As MutualFunds '1000 records read in from csv file

 'On form load event
Private Sub FrmMutualFunds_Load(sender As Object, e As EventArgs) Handles MyBase.Load
   'Get a count of array elements
  Dim fundsCount As Integer

   'Place contents of Results (2).csv into readData array
  Dim line As String

   Dim data(fundsCount) As String

   Dim mutualFundData() As String = IO.File.ReadAllLines("Results (2).csv")


   'Iterate for each category of data
  'Skip mutualFundData(0) to avoid header text
  For i As Integer = 1 To fundsCount - 1
     line = mutualFundData(i) 'Each line of data from Results (2).csv
    data = line.Split(","c) 'Splits each line of data at the comma

     'Replace "", --, and % in each record as required
    For replaceChar As Integer = 1 To data.Count - 1
       data(replaceChar) = data(replaceChar).Replace("""", "").Replace("%", "").Replace("--", "0").Trim()
     Next

     readData(i).symbol = data(0) 'Read in symbol from data array index 0
    readData(i).description = data(1) 'Read in description from data array index 1


     'Read items in from data array
    'Convert data from string to double
    readData(i).totalReturnThreeMonth = CDbl(data(2))
     readData(i).totalReturnSixMonth = CDbl(data(3))
     readData(i).oneYearAverageAnnualReturn = CDbl(data(4))
     readData(i).threeYearAverageAnnualReturn = CDbl(data(5))
     readData(i).fiveYearAverageAnnualReturn = CDbl(data(6))
     readData(i).tenYearAverageAnnualReturn = CDbl(data(7))
     readData(i).totalReturnYTD = CDbl(data(8))
     readData(i).averageAnnualReturnSinceInception = CDbl(data(9))

   Next

   'Don't show datagridview
  dgvResults.Visible = False

   'Displays number of funds to inform user funds have been read into program
  fundsCount = mutualFundData.Count - 1
   MessageBox.Show(fundsCount & " funds have been read.")


 End Sub

 'Runs queries based on user selection from listbox

 Private Sub btnRunReport_Click(sender As Object, e As EventArgs) Handles btnRunReport.Click

   'Display datagridview when user runs queries
  dgvResults.Visible = True

   'Remove blank cell at start of rows
  dgvResults.RowHeadersVisible = False

   'Run query based on user selection from list of reports
  Select Case lstQueries.SelectedIndex
     Case = 0
       'Pull records where average annual reaturn since inception is greater than user supplied percentage
      Dim sinceInceptionQuery = From currentReport In readData
                                 Where currentReport.averageAnnualReturnSinceInception > Double.Parse(txtPercentageOne.Text)
                                 Order By currentReport.averageAnnualReturnSinceInception Ascending
                                 Select currentReport.symbol, currentReport.description, currentReport.totalReturnThreeMonth,
                                        currentReport.totalReturnSixMonth, currentReport.totalReturnYTD, currentReport.averageAnnualReturnSinceInception

       'Populate datagridview with results of query
      dgvResults.DataSource = sinceInceptionQuery.ToList()
       dgvResults.CurrentCell = Nothing

     Case = 1
       'Display total return YTD over user supplied percentage
      Dim totalYTDQuery = From currentReport In readData
                           Where currentReport.totalReturnYTD > Double.Parse(txtPercentageOne.Text)
                           Order By currentReport.totalReturnYTD Ascending
                           Select currentReport.symbol, currentReport.description, currentReport.totalReturnThreeMonth,
                                  currentReport.totalReturnSixMonth, currentReport.totalReturnYTD, currentReport.averageAnnualReturnSinceInception

       'Populate datagridview with results of query
      dgvResults.DataSource = totalYTDQuery.ToList()
       dgvResults.CurrentCell = Nothing

     Case = 2
       'Display records where Total Return (3 Month) is between two user supplied percentages
      Dim totalThreeMonthQuery = From currentReport In readData
                                  Where currentReport.totalReturnThreeMonth <= Double.Parse(txtPercentageOne.Text) And currentReport.totalReturnThreeMonth >= Double.Parse(txtPercentageTwo.Text)
                                  Order By currentReport.totalReturnThreeMonth Ascending
                                  Select currentReport.symbol, currentReport.description, currentReport.totalReturnThreeMonth,
                                  currentReport.totalReturnSixMonth, currentReport.totalReturnYTD, currentReport.averageAnnualReturnSinceInception

       'Populate datagridview with results of query
      dgvResults.DataSource = totalThreeMonthQuery.ToList()
       dgvResults.CurrentCell = Nothing

     Case = 3
       'Display records where Total Return (6 Month) is between two user supplied percentages
      Dim totalSixMonthQuery = From currentReport In readData
                                Where currentReport.totalReturnSixMonth <= Double.Parse(txtPercentageOne.Text) And currentReport.totalReturnThreeMonth >= Double.Parse(txtPercentageTwo.Text)
                                Order By currentReport.totalReturnSixMonth Ascending
                                Select currentReport.symbol, currentReport.description, currentReport.totalReturnThreeMonth,
                                  currentReport.totalReturnSixMonth, currentReport.totalReturnYTD, currentReport.averageAnnualReturnSinceInception

       'Populate datagridview with results of query
      dgvResults.DataSource = totalSixMonthQuery.ToList()
       dgvResults.CurrentCell = Nothing

     Case = 4
       'Display records where Total Return YTD is between two user supplied percentages
      Dim totalYTDBetweenQuery = From currentReport In readData
                                  Where currentReport.totalReturnSixMonth <= Double.Parse(txtPercentageOne.Text) And currentReport.totalReturnThreeMonth >= Double.Parse(txtPercentageTwo.Text)
                                  Order By currentReport.totalReturnYTD Ascending
                                  Select currentReport.symbol, currentReport.description, currentReport.totalReturnThreeMonth,
                                  currentReport.totalReturnSixMonth, currentReport.totalReturnYTD, currentReport.averageAnnualReturnSinceInception

       'Populate datagridview with results of query
      dgvResults.DataSource = totalYTDBetweenQuery.ToList()
       dgvResults.CurrentCell = Nothing

   End Select

   'Clear text boxes
  txtPercentageOne.Clear()
   txtPercentageTwo.Clear()
 End Sub


End Class


RE: Problem Displaying Query Results in Data Grid View - itcorp - Dec 7, 2019

can you send me a csv for me to look at in order to help you buddy?
I made you an independent simpe example how would i do this if i were you.

let's just say this is my csv
Code
rank,place,population,lat,lon
1,New York city,8175133,40.71455,-74.007124
2,Los Angeles city,3792621,34.05349,-118.245323
3,Chicago city,2695598,45.37399,-92.888759
4,Houston city,2099451,41.337462,-75.733627
5,Philadelphia city,1526006,37.15477,-94.486114
6,Phoenix city,1445632,32.46764,-85.000823
7,San Antonio city,1327407,37.706576,-122.440612
8,San Diego city,1307402,37.707815,-122.466624
9,Dallas city,1197816,40.636,-91.168309
10,San Jose city,945942,41.209716,-112.003047
11,Jacksonville city,821784,30.2887,-81.391794
12,Indianapolis city (balance),820445,39.766911,-86.149963
13,Austin city,790390,28.973405,-95.961284
14,San Francisco city,805235,28.371795,-82.187909
15,Columbus city,787033,41.258735,-91.374424
16,Fort Worth city,741206,38.0016,-89.066334
17,Charlotte city,731424,39.09931,-76.817799
18,Detroit city,713777,42.408871,-83.002647
19,El Paso city,649121,41.645415,-91.599794
20,Memphis city,646889,35.149681,-90.04892
21,Boston city,617594,41.202364,-112.032315
22,Seattle city,608660,45.520615,-123.873929
23,Denver city,600158,32.96438,-102.829919
24,Baltimore city,620961,39.284664,-76.62022
25,Washington city,601723,38.899101,-77.028999
26,Nashville-Davidson metropolitan government (balance),601222,45.304048,-121.756365
27,Louisville/Jefferson County metro government (balance),597337,45.304048,-121.756365
28,Milwaukee city,594833,43.041809,-87.906837
29,Portland city,583776,45.52186,-123.882594
30,Oklahoma City city,579999,39.05514,-96.816104
31,Las Vegas city,583756,40.4879,-85.609999
32,Albuquerque city,545852,35.084179,-106.648643
33,Tucson city,520116,41.644727,-91.601947
34,Fresno city,494665,38.645741,-77.321863
35,Sacramento city,466488,38.915291,-121.594651
36,Long Beach city,462257,29.748022,-94.827603
37,Kansas City city,459787,39.016682,-96.864303
38,Mesa city,439041,30.686452,-97.700842
39,Virginia Beach city,437994,36.767408,-76.047707
40,Atlanta city,420003,37.691375,-122.454979
41,Colorado Springs city,416427,40.17676,-75.547839
42,Raleigh city,403892,41.132609,-73.977405
43,Omaha city,408958,41.260689,-95.94059
44,Miami city,399457,41.63636,-91.501889
45,Tulsa city,391906,39.095215,-121.613384
46,Oakland city,390724,38.334108,-87.345139
47,Cleveland city,396815,36.640475,-82.582569
48,Minneapolis city,382578,44.979031,-93.264931
49,Wichita city,382368,37.686981,-97.335579
50,Arlington city,365438,41.29525,-88.25278
csv file:
https://resources.oreilly.com/examples/0636920026938/raw/master/chapter_12/us-cities.csv


so this would be my header in this csv which I don’t really care about because I'm interested in the data
Code
rank,place,population,lat,lon

so now what interests me is how to skip header and start reading the data that interest me,so the first thing i need to do is model that will represent my data based on the csv file.


Code
Public Class CSVModel
   Public Property Rank As Integer
   Public Property CityName As String
   Public Property population As Decimal
   Public Property Lat As Double
   Public Property Lon As Double
End Class

now when i have a model that  represent my data it's time to create a logic that will turn the csv data into usages data in app but what should be kept in mind is that the  csv file may be abnormally large and that logic itself must be written accordingly to that.

https://pastebin.com/EmDrPFuF

then we can try our logic

Code
Private Async Sub Btnload_Click(sender As Object, e As RoutedEventArgs)
       Dim op As OpenFileDialog = New OpenFileDialog()
       op.Title = "Select csv file"
       op.Filter = "Text files (Text files (*.csv)|*.csv"
       If op.ShowDialog() = True Then
           csvGrid.DataContext = Await DataManipulacion.[Shared]().LoadCSV(op.FileName)
       End If
   End Sub
now everything is very simple with a few modifications you will have what you are looking for

[Image: 3rz8rqA.png]

example app

https://www.mediafire.com/file/g3qmanuacgw0mrf/csv.rar/file