I have a table in my Access database called Historical_Stock_Prices
that is filled with various companies historical stock prices. I need to run a query that will convert the raw data (the stock prices) into quarterly growth rates and display the quarterly growth rates in a DataGridView
.
I've already written the the following query in the SQL View of my Access database and it works within Access.
SELECT MinMaxYrQtrDates.YrQtr, MinMaxYrQtrDates.Ticker, MinMaxYrQtrDates.MaxDate, [Historical Prices].Close, MinMaxYrQtrDates.MinDate, [Historical Prices_1].Open, ([Historical Prices].[Close]/[Historical Prices_1].[Open]-1)*100 AS GrowthRate
FROM [Historical Prices] AS [Historical Prices_1] INNER JOIN ([Historical Prices] INNER JOIN [SELECT Year([Date]) & "-" & DatePart("q",[Date]) AS YrQtr, [Historical Prices].Ticker, Max([Historical Prices].Date) AS MaxDate, Min([Historical Prices].Date) AS MinDate
FROM [Historical Prices]
GROUP BY Year([Date]) & "-" & DatePart("q",[Date]), [Historical Prices].Ticker]. AS MinMaxYrQtrDates ON ([Historical Prices].Date = MinMaxYrQtrDates.MaxDate) AND ([Historical Prices].Ticker = MinMaxYrQtrDates.Ticker)) ON ([Historical Prices_1].Ticker = MinMaxYrQtrDates.Ticker) AND ([Historical Prices_1].Date = MinMaxYrQtrDates.MinDate);
I need to be able to call it from within my program and display the results in a DataGridView
. I've tried to copy the SQL statement from Access and use it as the SQL statement in my code but it doesn't work. I don't get any errors, the DataGridView
is just blank. Here is my code so far:
Imports System.IO
Imports System.Data.OleDb
Public Class Historical_Growth_Rates_Annual
Public tblName As String = "Historical_Stock_Prices"
Private Sub Historical_Growth_Rates_Annual_Load(sender As Object, e As EventArgs) Handles MyBase.Load
If (File.Exists(Nordeen_Investing_3.databaseName)) Then
Nordeen_Investing_3.con.Open()
Dim restrictions(3) As String
restrictions(2) = tblName
Dim dbTbl As DataTable = Nordeen_Investing_3.con.GetSchema("Tables", restrictions)
If dbTbl.Rows.Count = 0 Then
MessageBox.Show("Historical Stock Prices tables does not exist in the database. Please Update")
Else
Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT MinMaxYrQtrDates.YrQtr, MinMaxYrQtrDates.Ticker, MinMaxYrQtrDates.MaxDate, [Historical_Stock_Prices].Close1, MinMaxYrQtrDates.MinDate, [Historical_Stock_Prices_1].Open1, ([Historical_Stock_Prices].[Close1]/[Historical_Stock_Prices_1].[Open1]-1)*100 AS GrowthRate FROM [Historical_Stock_Prices] AS [Historical_Stock_Prices_1] INNER JOIN ([Historical_Stock_Prices] INNER JOIN [SELECT Year([Date1]) & " - " & DatePart('q',[Date1]) AS YrQtr, [Historical_Stock_Prices].Ticker, Max([Historical_Stock_Prices].Date) AS MaxDate, Min([Historical_Stock_Prices].Date) AS MinDate FROM [Historical_Stock_Prices] GROUP BY Year([Date1]) & " - " & DatePart('q',[Date1]), [Historical_Stock_Prices].Ticker]. AS MinMaxYrQtrDates ON ([Historical_Stock_Prices].Date = MinMaxYrQtrDates.MaxDate) AND ([Historical_Stock_Prices].Ticker = MinMaxYrQtrDates.Ticker)) ON ([Historical_Stock_Prices_1].Ticker = MinMaxYrQtrDates.Ticker) AND ([Historical_Stock_Prices_1].Date = MinMaxYrQtrDates.MinDate);", Nordeen_Investing_3.con)
'create a new dataset
Dim ds As New DataSet()
'fill the datset
da.Fill(ds)
'attach dataset to the datagrid
DataGridView1.DataSource = ds.Tables(0)
ds = Nothing
da = Nothing
Nordeen_Investing_3.con.Close()
End If
Else
MessageBox.Show("Database does not exist. Please update.")
End If
End Sub
End Class
I'm really stuck and could use some help! Thanks!
You want that VB.Net code to recreate the same SELECT
statement which works in Access. However, looking at the syntax highlighting with Vim, I think you may actually be creating something else. (It may be like creating a string as the difference of 2 other strings: "string 1" - "string 2"
).
But whether or not I guessed correctly, use a string variable to hold your SELECT
statement. Then print that string to the console or write it to a text file so that you can examine the actual statement you're giving to the db engine.
Or save the working query in Access as a named query object and use that query name from your VB.Net code --- that would absolutely guarantee using the same SQL which is confirmed to work in Access.