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?

You have
       strRangeA = "A" & i
        strRangeB = "B" & i
        strRangeC = "C" & i
        strRangeE = "E" & i

why are you skipping "D"? (?,?) means that the range is not valid
Marco

"D" is the D column in the excel spreadsheet that I am reading the data from.
Before I introduced the command object, this worked (with the skipping of the "D"). As I step through my code, I can see that each variable populates, it just doesn't run. Even when I take the

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

I get the same error in SQL...

When you are executing a stored procedure through a command object whose CommandType is adCmdStoredProc, the CommandText should just be the name of the stored procedure.
If the name of your stored procedure is "stored_proc" then you should have The question marks represent parameters in the definition that the cmd is holding. creates the first question mark that you see (the name of that ? is "flg") and [code] Creates the other. Because these parameter creations are inside the loop, you will get 2 more for each loop, but that will generate an error, because the names of a command object's parameters must be unique.

You should put the assignation of the CommandType and ActiveConnection outside the For / Next, as those properties are not going to change.

It looks like maybe fund holds the stored procedure name, so that needs to be inside the loop.

I don't know if setting the CommandText clears the parameter collection or not, you should experiment on that one.

If it clears the list, the creation of the parameters can stay where it is. otherwise, it should be outside the loop.

Does that clarify some of the things that you need to rearrange?

Well I did change things a bit so that the cmd.CommandText ONLY specifies the stored procedure. I then created the parameters (all 6) as such:

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, 8, 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

I am still looking for ways to refresh the parameters. Yet I am not getting the error message, however the application does not work...as my watched CommandText is equal to:

call stored_proc (?, ?, ?, ?, ?, ?)

How can I be sure that the stored proc knows what to do with the ?'s or that the ?'s are just place holders?

Also to retrieve the error codes, how can I do that? They are not coming back to me.

The ?s are placeholders in the watch window.

The parameters from the parameters collection will be linked with the ?s, 1st ? to Parameter(0), 2nd ? with parameter(1), etc.

The order must be in the order that the parameter list is found in the declaration of the stored procedure in the DB. It is unnerving to just trust Oracle and VB to play well together, but it does work. The parameter's order in VB and Oracle must be the same, but when they are, it works.

Any errors will be in the Errors collection of the connection object.

Ok this makes sense. So I'm no longer getting errors BUT I am not able to return the output parameters. Could this be a problem with the stored procedure or is this syntax incorrect?

strDesc = Trim(cmd.Parameters("@error_flag").Value)
strDesc = strDesc + Trim(cmd.Parameters("@msg_desc").Value)

Hi People, it may be in appropriate to post this here.

I have the following stored procedure in sql server 2000

CREATE PROCEDURE dbo.[usp_SampleProc]
@ErrorStmt varchar(200) ='' output
as
    set @ErrorStmt = ' Error while updating'
    SELECT 1/0

    IF (@@ERROR != 0 )
    BEGIN
             set @ErrorStmt = ' Error while updating 123456'
    return

    END

set @ErrorStmt = ' No Errors!'

When I execute this through vb 6.0 using following providers it yields the following results for the output parameter @ErrorStmt

MSDASQL.1 --> This returns always null value in the Commnad parameter.

SQLOLEDB.1 --> This returns the correct result as ' Error while updating 123456'.

Is there anything to do with my code or MSDASQL.1 settings. Awaiting for your reply... :-)