[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Recordset problem from SQL Server to VBA

Hi,

I am having a very strange error in my VBA code when grabbing data from a SQL Server database. I have a stored proc that takes in a date and a string and it returns 4 data items. Stored proc code below:

CREATE PROCEDURE FIRXT_GetMonthsData
(
      @Date_dt      datetime,
      @Sector_vc      nvarchar(20)
)
AS BEGIN
      DECLARE @XVol_i      int
      DECLARE @XTrades_i      int
      DECLARE @EVol_i      int
      DECLARE @ETrades_i      int

      SET NOCOUNT ON
      SET @EVol_i = ISNULL((SELECT SUM(Volume_i) FROM FIRXT_EXCHANGE_TRADEDATA WHERE YEAR(@Date_dt) = YEAR(Date_dt) AND MONTH(@Date_dt) = MONTH(Date_dt) AND Sector_vc = @Sector_vc),0)
      SET @ETrades_i = ISNULL((SELECT COUNT(Volume_i) FROM FIRXT_EXCHANGE_TRADEDATA WHERE YEAR(@Date_dt) = YEAR(Date_dt) AND MONTH(@Date_dt) = MONTH(Date_dt) AND Sector_vc = @Sector_vc),0)
      SET @XVol_i = ISNULL((SELECT SUM(Volume_i) FROM FIRXT_XTRAKTER_DYNAMIC XD, FIRXT_GROUPS_TABLE GT WHERE YEAR(@Date_dt) = YEAR(XD.Date_dt) AND MONTH(@Date_dt) = MONTH(XD.Date_dt) AND GT.Name_vc = @Sector_vc AND GT.ISIN_vc = XD.ISIN_vc),0)
SET @XTrades_i = ISNULL((SELECT SUM((HiTickets_i + LoTickets_i)/2) FROM FIRXT_XTRAKTER_DYNAMIC XD, FIRXT_GROUPS_TABLE GT WHERE YEAR(@Date_dt) = YEAR(Date_dt) AND MONTH(@Date_dt) = MONTH(Date_dt) AND GT.Name_vc = @Sector_vc AND GT.ISIN_vc = XD.ISIN_vc),0)
SELECT @XVol_i, @XTrades_i, @EVol_i, @ETrades_i
END
GO

I call this for 6 different dates and dump the results in a spreadsheet. Note that there is data in the database for all the months searched.

When I call this stored proc from within SQL Server, I get the correct numbers. However, when I look at the results coming back to my VBA code, the last 3 months are coming back with 0's. Below is my VBA code.

For lLoop = 1 To 6
        dtDate = Worksheets("Market").Cells(lLoop + 5, 9).Value
        Set cmd = New ADODB.Command
        With cmd
            .CommandText = "FIRXT_GetMonthsData"
            .CommandType = adCmdStoredProc
            .ActiveConnection = myDB
            .Parameters.Append .CreateParameter("@Date_dt", adDate, adParamInput, , dtDate)
            .Parameters.Append .CreateParameter("@Sector_vc", adVarChar, adParamInput, 20, "RSL")
            Set rs = .Execute
        End With
        With rs
            XVol = .Fields(0).Value
            XTicks = .Fields(1).Value
            EVol = .Fields(2).Value
            ETicks = .Fields(3).Value
            If EVol = 0 Then
                ETicks = 0
            End If
            Worksheets("Market").Cells(lLoop + 5, 10).Value = EVol + XVol
            Worksheets("Market").Cells(lLoop + 5, 11).Value = ETicks + XTicks
            .Close
        End With
        Set rs = Nothing
        Set cmd = Nothing
    Next lLoop

Does anyone have any ideas on how I can get the correct data into VBA? It would appear that SQL Server is returning the correct result when the call takes place within SQL Server, but an incorrect result when called externally. Google has come up a blank.

Thanks,
John.
0
GroganJ
Asked:
GroganJ
  • 3
  • 3
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
The stored procedure only returns one record containing four fields. Why are you saying the "last three months are zero"?

Kevin
0
 
GroganJAuthor Commented:
The stored proc is called 6 times - each time with a different date. The first 3 times, it returns the correct results. The last 3 times, it returns 0 for every field - even though there is data in the database and a direct call of the stored proc in the db gives the correct result.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Change these lines:

        dtDate = Worksheets("Market").Cells(lLoop + 5, 9).Value
        Set cmd = New ADODB.Command

to:

        dtDate = Worksheets("Market").Cells(lLoop + 5, 9).Value
        Debug.Print dtDate
        Set cmd = New ADODB.Command

and post the results here (output will be in the Immediate window).

I suspect the last three date cell contain a time component.

Kevin
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
zorvek (Kevin Jones)ConsultantCommented:
On second thought that can't be the problem because you select on the year and the month only.

I would still like to see the results of the above test.

Kevin
0
 
GroganJAuthor Commented:
The dates are all created using dateserial, so no time componant. The debug output did show the problem though - the format of the data was wrong - I had mixed up the month and day references, so instead of using 1 Dec 2010, it was using 12 Jan 2010 - Doh!

Thanks for the help!
0
 
GroganJAuthor Commented:
It wasn't the solution, but his suggestion highlighted the problem to me.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now