Adding an ado parameter to legacy vb stored procedure call 1

[normm]

Hi all, I am dealing with a piece of legacy code in an Excel spreadsheet. The following code has worked successfully for years however we have recently moved to a new SQL server and our stored procedure now requires a parameter.
The code retrieves stored procedure names from the database which it then executes in the loop I need to pass a parameter "@PICUOrg" of type nvarchar(6) to the stored procedure. Additionally I need to pass a null value to this parameter.

I have little to no experience of vb so I would massively appreciate some help with this.

This is the code that has worked fine for years (without the additional needed parameter):

With rstReports

    .ActiveConnection = con
    .Source = "SELECT * " & _
            "FROM nr0810_tblNationalReportTableDefs " & _
            "ORDER BY CAST(TableID AS int) DESC;"
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Open

End With

Do While Not rstReports.EOF
    
    Set cmd = New ADODB.Command
    
    With cmd
    
        .ActiveConnection = con
        .CommandType = adCmdStoredProc
        .CommandTimeout = 300
        .CommandText = rstReports("StoredProcedure")
    
    End With
    
    Set rst = cmd.Execute
    
    OutputADORecordSetToWorksheet rst, rstReports("Title"), rstReports("TableID"), rstReports("MainGroup"), rstReports("MultipleGroupings"), rstReports("ChartType"), rstReports("ChartTotals")
    
    rst.Close
    
    Set rst = Nothing
    
    Set cmd = Nothing
    
    rstReports.MoveNext
    
Loop

here is my "best" attempt at getting this to work, can anyone see what I am doing wrong?

With rstReports

    .ActiveConnection = con
    .Source = "SELECT top 2 * " & _
            "FROM tlkpNationalReportTableDefinitions " & _
            "ORDER BY CAST(TableID AS int) asc;"
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Open

End With

Do While Not rstReports.EOF
    
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = con
        .CommandType = adCmdStoredProc
        .CommandTimeout = 300
        .CommandText = rstReports("StoredProcedure")
    End With
    
    cmd.Parameters.Append cmd.CreateParameter("@PICUOrg", adVarChar, adParamInput, 50, Null)
    
    Set rst = cmd.Execute
    
    OutputADORecordSetToWorksheet rst, rstReports("Title"), rstReports("TableID"), rstReports("MainGroup"), rstReports("MultipleGroupings"), rstReports("ChartType"), rstReports("ChartTotals")
    
    rst.Close
    
    Set rst = Nothing
    
    Set cmd = Nothing
    
    rstReports.MoveNext
    
Loop

the error I am getting is:


run-time error '-2147217900 (80040e14)':

Syntax error, permission violation, or other nonspecific error


the debugger says that the "cmd.Execute" is the problem, thanks in advance for any help that you can give
normm

 

[SkipVought]

I recently converted some queries I had to parameter queries in ADO...

Function PartCost(PN As String) As Single
'SkipVought/2009 Aug 18/
'--------------------------------------------------
' Access: A010PROD.FPRPTSAR.READ
'--------------------------------------------------
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection, cmd As ADODB.Command
    
    Set rst = New ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command

    sServer = "A010PROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    sSQL = sSQL & "SELECT COST"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM FPRPTSAR.PART_MASTER"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE PART_ID=?"
        
    Debug.Print sSQL
        
    With cmd
        .CommandText = sSQL
        .CommandType = adCmdText
        .Prepared = True
        
        .Parameters.Append .CreateParameter( _
            Name:="PART_ID", _
            Type:=adChar, _
            Direction:=adParamInput, _
            Size:=16, _
            Value:=Trim(PN))
        
        .ActiveConnection = cnn
        
        Set rst = .Execute
    End With
                          
    On Error Resume Next
    
    rst.MoveFirst

    If Err.Number = 0 Then
        PartCost = rst(0)
    Else
        PartCost = 0
    End If
    
    rst.Close
    cnn.Close
    
    Set cmd = Nothing
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Bluejay07]

First question: Is this VB code or VBA code. I'm guessing VBA since you are referring to excel. There is a separate forum for VBA code: forum707.

Do you need to pass a null value or an empty string. There is a difference. An empty string is used by typing "" (although with excel code you may prefer to use vbNullString).
My guess is that the stored procedure is rejecting your passed values when executing the line "Set cmd = cmd.execute".

Without us knowing how the stored procedure is declared and used, it is difficult for us to provide more help.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[SkipVought]

The change to SQL Server PROBABLY entailed some change in SQL code.

If your stored procedure (which is basically SQL code) NOW all of the sudden, needs a parameter, was the SP (SQL Code) changed to require a parameter? It did not happen JUST BECAUSE the server changed.

How did that change and the accompanying parameter coding NOT get changed at the same time? The SOMEONE who made the change, ought to know how the parameter(s) ought to be assigned.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[normm]

Thanks for the responses, BlueJay was correct, it required vbNullstring.

I am the SQL developer, I rebuilt/ expanded the database that works with a new c#/xml front end. All stored procedures using this legacy spreadsheet have now been re-created but with parameters to scope them to the data. the same reports are generated using reporting services. As procedures are not currently in place to do our annual report on reporting services I am hacking the spreadsheet to do it one last time!

thanks again for the help and apologies for not posting this in the appropriate forum.

Best Wishes
Normm