CData Excel Add-In for Reckon 2019 - Online Help
Questions / Feedback?

Calling Stored Procedures

CData Excel Add-In for Reckon 2019 - Build 19.0.7354

public bool CallSP(string queryString, object paramNames, object paramValues)

This calls a stored procedure. It returns true on success, false otherwise. You can pass the name and value pair of each parameter to CallSP as two arrays. This is useful when working with many parameters and XML aggregates. In this case, the query string argument to CallSP must be the name of the parameter:

 CallSP("ReportGeneralDetail",nameArray,valueArray)
The following example shows how to pass inputs to a stored procedure call and iterate through any output parameters.
Dim nameArray
nameArray = Array("Name", "Name")
Dim valueArray
valueArray = Array("Trujilo, Ana", "Cook, Brian")
Dim module As New ExcelComModule
module.SetProviderName ("Reckon")
module.SetConnectionString ("User=test;Password=test;URL=http://localhost:8166;")
Query = "INSERT INTO Customers (Name, Name) VALUES (@Name, @Name)"
If module.Insert(Query, nameArray, valueArray) Then
  MsgBox "The insert was successful."
Else
  MsgBox "The insert failed."
End If
module.Close
Dim module As New ExcelComModule
module.SetProviderName ("Reckon")
module.SetConnectionString ("User=test;Password=test;URL=http://localhost:8166;")
Dim nameArray
nameArray = Array("IncludeColumn")
Dim valueArray
valueArray = Array("TXNNUMBER")
StoredProc = "ReportGeneralDetail"
Cursor = Application.Cursor
Application.Cursor = xlWait
If module.CallSP(StoredProc, nameArray, valueArray) Then
  MsgBox "Execution was successful."
  Dim ColumnCount As Integer
  ColumnCount = module.GetColumnCount
  For Count = 0 To ColumnCount - 1
    Application.ActiveSheet.Cells(1, Count + 1).Value = module.GetColumnName(Count)
  Next 
  Dim RowIndex As Integer
  RowIndex = 10 
  While (Not module.EOF)
    For columnIndex = 0 To ColumnCount - 1
      If Conversion.CInt(module.GetColumnType(columnIndex)) = Conversion.CInt(vbDate) And Not IsNull(module.GetValue(columnIndex)) Then
        Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = Conversion.CDate(module.GetValue(columnIndex))
      Else
        Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = module.GetValue(columnIndex)
      End If
    Next
    module.MoveNext
    RowIndex = RowIndex + 1
  Wend
Else
  MsgBox "Execution failed."
End If
Application.Cursor = Cursor
module.Close

 
 
Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 19.0.7354.0