I am getting the Syntax Error or Access Violation when I attempt to run my program. I have an idea as to what the problem is but can't figure out how to fix it.

I am using the command object to execute a stored procedure that has 4 input parameters and 2 output parameters.

Here is my code:

    Call CrossingFiles                          'Calls function
    
    Application.ActiveWorkbook.Save
    Application.DisplayAlerts = False
    Application.ActiveWorkbook.Close
    
    Set MyXL = Nothing
    
    Unload OpenFile
    
ErrorHandler:         ' Error-handling routine.
Dim StrErr As String
StrErr = Err.Number & " - " & Err.Description
    If Err = 364 Then
      Exit Sub
    End If
    MsgBox (StrErr), vbOKOnly, Error
    
End Sub

Sub CrossingFiles()

    Dim wsData As Worksheet
    Dim fund As String
    Dim trans_type As String
    Dim security_id As String
    Dim shares As String
    Dim strRangeA As String
    Dim strRangeB As String
    Dim strRangeC As String
    Dim strRangeE As String
    Dim prmfund As ADODB.Parameter
    Dim prmtrans As ADODB.Parameter
    Dim prmsec As ADODB.Parameter
    Dim prmshare As ADODB.Parameter
    Dim prmFlg As ADODB.Parameter
    Dim prmErr As ADODB.Parameter
    Dim strSQL As String
    Dim strDesc As String
    Dim cmd As Command
    Dim i As Integer
    Dim adoConn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim txtLog As String
    Dim flag As String
    Dim desc As String
    Set wsData = ActiveSheet
    Set adoConn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command
    i = 1
    txtLog = " "
    flag = " "
    desc = " "
        
    wsData.Rows(1).Delete
      
    adoConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=ODBCsrc;Initial Catalog=main"
    adoConn.Open
                      
    'This will go through the worksheet, row by row, and send the necessary data to the DB
    Dim MyColumns_Range As Range
    Set MyColumns_Range = Range(wsData.Cells(1, "A"), wsData.Cells(1, "A").End(xlDown))
    For Each c In MyColumns_Range
        strRangeA = "A" & i
        strRangeB = "B" & i
        strRangeC = "C" & i
        strRangeE = "E" & i
        
        fund = Range(strRangeA).Value
        trans_type = Range(strRangeB).Value
        security_id = Range(strRangeC).Value
        shares = Range(strRangeE).Value
          
        Set cmd.ActiveConnection = adoConn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "Execute stored_proc '" & fund & "', '" & trans_type & "', '" & security_id & "', '" & shares & "', "
        
        'Declare the output parameters
        Set prmFlg = cmd.CreateParameter("flg", adVarChar, adParamOutput, 1, "")
        cmd.Parameters.Append prmFlg
            
        Set prmErr = cmd.CreateParameter("Err", adVarChar, adParamOutput, 255, "")
        cmd.Parameters.Append prmErr
        'adoConn.Execute strSQL, , adCmdText

        'rst.Open strSQL, adoConn, adOpenStatic, adLockReadOnly
        Set rst = cmd.Execute
        'strDesc = rst.Fields("flag") & " " & rst.Fields("desc")
        strDesc = prmFlg.Value + " " + prmErr.Value
        txtLog = txtLog + strDesc
        'rst.Close

        i = i + 1
            
        fund = " "
        trans_type = " "
        security_id = " "
        shares = " "
        strDesc = " "
        cmd.CommandText = " "
        Set prmFlg = Nothing
        Set prmErr = Nothing
    Next c
        
    Set wsData = Nothing
    adoConn.Close
    
    'saves any changes made to the workbook and turns off the prompts ('are you sure')
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True

End Sub

I have a watch set for the cmd.CommandText to see what it is set for (before I get the error). This is what it is set for:

"{ call Execute stored_proc '520150472', 'BUY', '36144810', '700', (?, ?) }"

Note the (?,?). Why are there parantheses and why are there question marks? Even when I type this into SQL I get the same error. Why? What can I do to fix it?

T-SQL EXECUTE

____________________________
If you find the answer helpful, please mark this topic as solved.

I don't understand what your reply means. Can I get it to display execute instead of Call?

Its a link, you need to click on it  

Steve

Ok so I've modified my code and taken out the Execute before the stored proc because otherwise it puts in Call Execute stored_proc. I also created all of the parameters  using the cmd object. I no longer get the error, however now, NOTHING happens. I am not getting anything returned. The article makes it seem that I need the words OUTPUT in my statement, but can I do that using the command object or if the parameters are set as output parameters does it know this? Here is my altered code (for the CrossingFiles function):

Sub CrossingFiles()

    Dim wsData As Worksheet
    Dim fund As String
    Dim trans_type As String
    Dim security_id As String
    Dim shares As String
    Dim strRangeA As String
    Dim strRangeB As String
    Dim strRangeC As String
    Dim strRangeE As String
    Dim prmfund As ADODB.Parameter
    Dim prmtrans As ADODB.Parameter
    Dim prmsec As ADODB.Parameter
    Dim prmshare As ADODB.Parameter
    Dim prmFlg As ADODB.Parameter
    Dim prmErr As ADODB.Parameter
    Dim strSQL As String
    Dim strDesc As String
    Dim cmd As Command
    Dim i As Integer
    Dim adoConn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim txtLog As String
    Dim flag As String
    Dim desc As String
    Set wsData = ActiveSheet
    Set adoConn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command
    i = 1
    txtLog = " "
        
    wsData.Rows(1).Delete
      
    adoConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=ODBCsrc;Initial Catalog=main"
    adoConn.Open
    
    Set cmd.ActiveConnection = adoConn
    cmd.CommandType = adCmdStoredProc
                          
    'This will go through the worksheet, row by row, and send the necessary data to the DB
    Dim MyColumns_Range As Range
    Set MyColumns_Range = Range(wsData.Cells(1, "A"), wsData.Cells(1, "A").End(xlDown))
    For Each c In MyColumns_Range
        strRangeA = "A" & i
        strRangeB = "B" & i
        strRangeC = "C" & i
        strRangeE = "E" & i
        
        fund = Range(strRangeA).Value
        trans_type = Range(strRangeB).Value
        security_id = Range(strRangeC).Value
        shares = Range(strRangeE).Value
          
        cmd.CommandText = "ssga_import_residuals "
          
        'Declare the input parameters
        Set prm_fund = cmd.CreateParameter("@acct_str", adVarChar, adParamInput, 20, fund)
        cmd.Parameters.Append prm_fund
        
        Set prm_trans = cmd.CreateParameter("@trans_type", adVarChar, adParamInput, 10, trans_type)
        cmd.Parameters.Append prm_trans
        
        Set prm_sec = cmd.CreateParameter("@security", adVarChar, adParamInput, 9, security_id)
        cmd.Parameters.Append prm_sec
        
        Set prm_shares = cmd.CreateParameter("@qty_str", adVarChar, adParamInput, 20, shares)
        cmd.Parameters.Append prm_shares
        
        'Declare the output parameters
        Set prmFlg = cmd.CreateParameter("@error_flag", adVarChar, adParamOutput, 1, " ")
        cmd.Parameters.Append prmFlg
            
        Set prmErr = cmd.CreateParameter("@msg_desc", adVarChar, adParamOutput, 255, " ")
        cmd.Parameters.Append prmErr
        'adoConn.Execute strSQL, , adCmdText
        
        Set rst = cmd.Execute
        'rst.Close

        'strDesc = Trim(cmd.Parameters("@error_flag").Value)
        'strDesc = strDesc + Trim(cmd.Parameters("@msg_desc").Value)
        strDesc = cmd(4)
        strDesc = strDesc + cmd(5)
        txtLog = txtLog + strDesc

        i = i + 1
            
        fund = " "
        trans_type = " "
        security_id = " "
        shares = " "
        strDesc = " "
        cmd.CommandText = " "
        cmd.Properties.Refresh
        cmd.Parameters.Delete ("@acct_str")
        cmd.Parameters.Delete ("@trans_type")
        cmd.Parameters.Delete ("@security")
        cmd.Parameters.Delete ("@qty_str")
        cmd.Parameters.Delete ("@error_flag")
        cmd.Parameters.Delete ("@msg_desc")
    Next c
        
    Set wsData = Nothing
    adoConn.Close
    
    'saves any changes made to the workbook and turns off the prompts ('are you sure')
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True

End Sub

Now the commandText reads:

"{ call stored_proc (?, ?, ?, ?, ?, ?) }"

But I still can't read the output parameters. Why not? According to one help site I went to it said I needed to close the recordset in order to get the output parameters but I get an error when I attempt this.

Have you specified them as out parameters in your sp. Also the recordset needs to be open.

Steve

They are specified as outparameters:

'Declare the output parameters
        Set prmFlg = cmd.CreateParameter("@error_flag", adVarChar, adParamOutput, 1, " ")
        cmd.Parameters.Append prmFlg
            
        Set prmErr = cmd.CreateParameter("@msg_desc", adVarChar, adParamOutput, 255, " ")
        cmd.Parameters.Append prmErr
        'adoConn.Execute strSQL, , adCmdText

is that all I need to do to specify them as outparameters? Thats all that I've seen in any examples I've found online.

Also - I guess I assumed that by setting the rst = cmd.Execute that it opened the rst. I was wrong. BUT when I try to open the rst it yells at me:

"Run-time error '3709':
The connection cannot be used to perform this operation. It is eitehr closed or invalid in this context."

What does that mean? Why can't I open the recordset?

Can you post your sp please.

Steve  

Here it is:

create procedure [dbo].[stored_proc] @acct_str varchar(20),
                                   @trans_type varchar(10),
                                   @security varchar(8),
                                   @qty_str varchar(20),
                                   @error_flag varchar(1) output,
                                   @msg_desc varchar(255) output
as

set nocount on

declare @OrderId numeric(10),
        @Account_Name varchar(20),
        @AccountID numeric(10),
        @Qty float,
        @SecurityID numeric(10),
        @SideCode tinyint,
        @TktTypCode tinyint,
        @AccrdInc float,
        @count_proposed int,
        @acct_src int,
        @CountSec int,
        @proceed bit,
        @return_code int,
        @gplus_src int,
        @axys_src int

/* Set default values */
select @proceed = 1, -- TRUE
       @TktTypCode = 1, -- equity
       @count_proposed = 0,
       @AccrdInc = 0,
       @gplus_src = 2,
       @axys_src = 3


/* Begin processing proposed order. */

/* If the account number starts with "52", then reformat the account number from 520009872 to 52-000987.2.*/

select @Account_Name =
CASE
    WHEN (substring(@acct_str,1,2) = '52' and len(@acct_str) >= 9)
         then substring(@acct_str,1,2) + '-' + substring(@acct_str,3,6) + '.' + substring(@acct_str,9,1) + '%'
    ELSE @acct_str + '%'
END

/* Get the account id number in main DB. */

select @AccountID = isnull(account_id,-1),
       @acct_src = source_system_id
  from account
where deleted = 0
   and account_level_code = 2
   and source_system_id in (2,3,4)
   and short_name like @Account_Name

select @CountSec = @@rowcount

if @CountSec > 1
begin
  select @msg_desc = 'More than 1 account found for ' + isnull(@acct_str,'--') + '.  Order not created.'
  select @error_flag = '1'
  return
end

if @CountSec = 0
begin
  select @msg_desc = 'No matching account found for ' + isnull(@acct_str,'--') + '. Order not created.'
  select @error_flag = '1'
  return
end

if @AccountId = -1
begin
  select @msg_desc = 'Account ' + isnull(@acct_str,'--') + ' does not exist in main DB. Order not created.'
  select @error_flag = '1'
  return
end

/* Check if security exists in main DB and get security_id*/

select @acct_src = case @acct_src
                     when null then @gplus_src
                     when 0 then @gplus_src
                     when @gplus_src then @gplus_src
                     when @axys_src then @gplus_src
                     else @acct_src
                   end

select @SecurityId = isnull(security_id, -1)
from security
where deleted = 0
  and source_system_id = @gplus_src
  and major_asset_code = 1 -- equities only
  and substring(user_id_3,1,8) = @security

select @CountSec = @@rowcount

if @CountSec > 1
begin
  select @msg_desc = 'More than 1 security found for Account:' + isnull(@acct_str,'--') + 'and Security ' + isnull(@security,'--') + '. Order not created.'
  select @error_flag = '1'
  return
end

if @CountSec = 0 and @acct_src <> @gplus_src
begin
  select @securityID = isnull(security_id, -1)
  from security
  where deleted = 0
  and source_system_id = @acct_src  
  and major_asset_code = 1 -- equities only
  and substring(user_id_3,1,8) = @security
  select @CountSec = @@rowcount
end

if @securityId = -1 or @CountSec = 0
begin
  select @msg_desc = 'Security ' + isnull(@security,'--') + ' does not exist in main DB for Account: ' + isnull(@acct_str,'--') + '. Order not created.'
  select @error_flag = '1'
  return
end

/* Check for existing proposed orders. */

select @count_proposed = count(*)
from proposed_orders p
where p.account_id = @AccountID
  and p.security_id = @securityId

if @count_proposed > 0
begin
    select @msg_desc = 'Account(' + @acct_str + ') already has proposed order(s) for ' + isnull(@security,'--') + '. Order not created.'
    select @error_flag = '1'
    return
end

/* Conver the transaction type. */

select @sidecode =
CASE
    WHEN @trans_type = 'BUY' then 0
    WHEN @trans_type = 'SELL' then 2
    WHEN @trans_type = 'SELLS' then 8
END

/* Convert the quantity to a float */

select @Qty = convert(float,@qty_str)

/* Call add_proposed to insert the order into the proposed_orders table. */

execute @return_code = add_proposed @OrderID output,
                                    @account_id = @AccountID,
                                    @side_code = @SideCode,
                                    @quantity = @Qty,
                                    @security_id = @SecurityID,
                                    @ticket_type_code = @TktTypCode,
                                    @accrued_income = @AccrdInc

select @proceed =
  case
    when (@return_code <> 0 and @return_code < 60000) then 0
    when (not exists (select * from proposed_orders where order_id = @OrderID)) then 0 -- Check to see if trade created
    else 1
  end

select @msg_desc =
  case
    when @proceed = 1 then 'Trade for account ' + @acct_str + ', security ' + @security + ' created.'
    when @proceed = 0 and @sidecode = 'S' then 'Error creating sell trade for ' + @acct_str + ' and ' + @security + '! Return code: ' + convert(varchar(10),@return_code)
    when @proceed = 0 and @sidecode = 'B' then 'Error creating buy trade for ' + @acct_str + ' and ' + @security + '! Return code: ' + convert(varchar(10),@return_code)
end

select @error_flag =
case
    when @proceed = 1 then '0'
    else '1'
end

return

I can't see that you need the recordset as your not creating one in your sp, but you should be able to read the parameters. They do appear to be correct in your sp and your code.

Steve  

Thats exactly it! I don't need the recordset object! Ha! I just got rid of it so I'm only using the command object and calling the stored procedure and its working! I am getting my output parameters! Its working!

Thanks so much for your help! I guess I didn't realize that I could do this without the recordset object. SO I simply got rid of the recordset object and am using cmd.Execute and from there I am able to retrieve the output parameters with a simple cmd(0) and cmd(1)!

Glad to help