MS Access SQL Select Statement Returns Correct Fields but No Data in the Rows

Question

I have two Microsoft Access database tables. They are named Historical_Stock_Prices and Balance_Sheets. I need to combine data from each of these tables to create a table called Daily. I need to take the fields Ticker, [Date], and [Close] from Historical_Stock_Prices and I need to take the field Common_Stocks from Balance_Sheets.

I will not be taking every row from the Historical_Stock_Prices and Balance_Sheets though. I will only be taking the rows that are on or before a date selected in a DateTimePicker named dtpDateSelection.

Now the main problem that I have is that Historical_Stock_Prices contains a row for each day. While Balance_Sheets contains a row for each quarter. So for each day in a quarter the figure that comes from Balance_Sheets will be the same.

Here is the code that I have so far:

Dim Date1 As Date = dtpDateSelection.Value

Try
Dim cmd As OleDbCommand = New OleDbCommand("CREATE PROC Daily AS SELECT [H].[Ticker], [H].[Date], [H].[Close], [B].[Common_Stocks] FROM [Historical_Stock_Prices] AS [H] INNER JOIN [Balance_Sheets] AS [B] ON Int(Year([H].[Date])) = Int([B].[Year]) AND Int(Format([H].[Date],'Q')) = Int([B].[Period]) AND CStr([H].[Ticker]) = CStr([B].[Ticker]) WHERE CDate([H].[Date]) = #" & CDate(Date1) & "#", con)
cmd.ExecuteNonQuery()
Catch ex As Exception
Finally
Dim cmda As OleDbCommand = New OleDbCommand("DROP PROCEDURE Daily", con)
cmda.ExecuteNonQuery()
Dim cmdb As OleDbCommand = New OleDbCommand("CREATE PROC Daily AS SELECT [H].[Ticker], [H].[Date], [H].[Close], [B].[Common_Stocks] FROM [Historical_Stock_Prices] AS [H] INNER JOIN [Balance_Sheets] AS [B] ON Int(Year([H].[Date])) = Int([B].[Year]) AND Int(Format([H].[Date],'Q')) = Int([B].[Period]) AND CStr([H].[Ticker]) = CStr([B].[Ticker]) WHERE CDate([H].[Date]) = #" & CDate(Date1) & "#", con)
cmdb.ExecuteNonQuery()
End Try

This code creates the table Daily and the fields Ticker, [Date], [Close] and Common_Stocks within that table. But the code does not load any data into the rows of the table. Any ideas why not?

You're just creating a stored proc (twice for some reason). You need to actually run it to get results. — Comfortably Numb
– Comfortably Numb, Commented Oct 3, 2013 at 18:28
This code creates a query (view) in MS-Access. This code doesn't return anything — Steve
– Steve, Commented Oct 3, 2013 at 18:28
@gromit1 do you need a stored query? Just Create a new SELECT command as New OleDbCommand("SELECT [H].[Ticker], .... and use a DataAdapter to fill a DataTable — Comfortably Numb
– Comfortably Numb, Commented Oct 3, 2013 at 18:31

Greg Jones · Accepted Answer · 2013-10-03 18:34:44Z

1

I'm not sure your code is do what you need it to do. I don't see a CREATE TABLE command that would create the desired tables or a INSERT INTO command that would insert the records into the tables.

answered Oct 3, 2013 at 18:34

Greg Jones

3971 silver badge7 bronze badges

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

1 Comment

gromit1 Over a year ago

I've changed my SQL statement to reflect @Steve answer. In VB.net the query still returns blank and in MS Access it throws a Syntax error in SQL expression message.

Steve · Accepted Answer · 2013-10-03 18:35:35Z

1

Your code tries to create the equivalent of a View. By itself it doesn't return any data. You could return the data from the SELECT statement. Not sure if the syntax is correct but you could try the cmdText directly with MS-Access and verify the results

Dim cmdText = "SELECT [H].[Ticker], [H].[Date], [H].[Close], [B].[Common_Stocks] " & _
              "FROM [Historical_Stock_Prices] AS [H] INNER JOIN [Balance_Sheets] AS [B] " & _
              "ON Year([H].[Date]) = [B].[Year] AND " & _
              "Format([H].[Date],'Q') = [B].[Period] AND " & _
              "[H].[Ticker] = [B].[Ticker] "
              "WHERE [H].[Date] = ?"
Using cmdb = New OleDbCommand(cmdText, con)
    cmdb.Parameters.AddWithValue("@1", CDate(Date1))
    Using da = new OleDbDataAdapter(cmdb)
        Dim dt = new DataTable("Daily")
        da.Fill(dt)
        ' now you have an IN MEMORY DataTable named Daiyly filled with the data '
        ' returned by the SELECT query '
    End Using
End Using

answered Oct 3, 2013 at 18:35

Steve

217k22 gold badges242 silver badges296 bronze badges

6 Comments

gromit1 Over a year ago

When I tried this code I got this error in vb.net Additional information: Type mismatch in expression.

Steve Over a year ago

Take the query as it and put in Query designer of MS-Access. Usually you will be able to find the problem (Probably there is a problem with the Format that returns a string and the Period but it is also possible that other fields used in the join or in the where are not of the exact type expected)

gromit1 Over a year ago

I no longer the error message. But when I load the DataTable into a` DataGridView` the DataGridView is blank.

Steve Over a year ago

But the query in MS-Access with the same value for [H].[Date] returns rows? Try to convert Date1 parameter in a true DateTime using Convert.ToDateTime(Date1)

gromit1 Over a year ago

The query in MS-Access throws a Syntax error in SQL expression.

|

Bleeding Fingers · Accepted Answer · 2013-10-03 19:09:32Z

1

Looks like you're creating a stored procedure that doesn't actually INSERT any data into the table Daily that you mentioned.

You might want to change the first portion CREATE PROC Daily AS to INSERT INTO dbo.Daily. That way, the data will be appended to the table.

edited Oct 3, 2013 at 19:09

Bleeding Fingers

7,1508 gold badges49 silver badges80 bronze badges

answered Oct 3, 2013 at 18:35

Cebland

112 bronze badges

1 Comment

gromit1 Over a year ago

I've changed my SQL statement to reflect @Steve answer. In VB.net the query still returns blank and in MS Access it throws a Syntax error in SQL expression message.

Collectives™ on Stack Overflow

MS Access SQL Select Statement Returns Correct Fields but No Data in the Rows

3 Answers 3

1 Comment

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related