CODEWITHSUNDEEP
sqlvb.netms-accessdatagridviewoledb
gromit1
gromit1

Reputation: 587

Convert Access Query to VB.net SQL Statement

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!

Database Example

Upvotes: 0

Views: 2148

Answers (1)

HansUp
HansUp

Reputation: 97111

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.

Upvotes: 1

Related Questions