0

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

2
  • 1
    No errors? Hmm. Try using single quotes on stuff like this: DatePart('q'... Commented Aug 23, 2013 at 18:42
  • @LarsTech Still blank. No errors... Commented Aug 23, 2013 at 18:44

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

15 Comments

Whatever approach is best, you really need to take another look at the SQL you claimed works in Access. The db engine will choke when it tries to parse this segment: 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
When you try a subquery in the form of [SELECT ...]. AS alias instead of (SELECT ...) AS alias, Access will be confused by any additional [] characters within the subquery's SELECT.
Build the SELECT as a string variable. Print the variable's content to a text file. Copy the statement from the text file and try it in an Access session as a new query.
@nordeen1 my code doesn't even reach this point. You will have to explain that part.
I can help with Access SQL. I'm lost when it comes to .Net. But I know just enough to tell you can't expect Access SQL to work from .Net if it doesn't work in Access. Create an Access query which you can verify actually works in Access. Then use that same SQL statement in .Net.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.