CODEWITHSUNDEEP
sqlvb.netms-accessselect-query
gromit1
gromit1

Reputation: 587

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

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?

Upvotes: 0

Views: 511

Answers (3)

Cebland
Cebland

Reputation: 11

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.

Upvotes: 1

Steve
Steve

Reputation: 216253

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

Upvotes: 1

Greg Jones
Greg Jones

Reputation: 397

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.

Upvotes: 1

Related Questions