Results 1 to 2 of 2

Thread: empty recordset when multiple entires in parameter

  1. #1
    VBAX Newbie
    Nov 2011

    empty recordset when multiple entires in parameter


    I have a stored procedure in sql with parameter and vb in excel to pass all works when only one variable entered but I require user to be able to enter multiple codes.
    I do not get an error but recordset is empty
    Hope this makes sense this is my first post any suggestions greatly appreciated
    Thank you

    'This sub-routine defines an ADOBD command to return variables to VBA from SQL Stored Procedures.
    'The ADODB command executes a Stored Procedure on the SQL Server (cmd.CommandText = [Stored Procedure Name])
    'Input requirements from the Stored procedure are declared as variants at the start of the sub-routine.

    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim stCon As String 'SQL Connection string
    Dim stProcName As String 'Stored Procedure name

    ' Declare variables for Stored Procedure
    Dim myVariable As Variant

    ' Set ADODB requirements
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

    ' Open a Connection using an ODBC DSN named "Pubs".
    cnn.ConnectionString = "DSN=TINACUST;UID=ANALYST;PWD=ANALYST;"

    ' Defines the stored procedure commands
    stProcName = "fgbomprompt" 'Define name of Stored Procedure to execute.
    cmd.CommandType = adCmdStoredProc 'Define the ADODB command
    cmd.ActiveConnection = cnn 'Set the command connection string
    cmd.CommandText = stProcName 'Define Stored Procedure to run

    'set parameters to be executed
    myVariable = InputBox("enter Stockcode")

    Dim myvar2 As String
    myvar2 = Replace(Trim(myVariable), ", ", "', '")
    myvar2 = "'" & myvar2 & "'"
    Debug.Print myvar2
    Set prm = cmd.CreateParameter("@code", adVarChar, adParamInput, 100, myvar2)

    With cmd
    .Parameters.Append prm
    End With

    'Execute stored procedure and return to a recordset
    rst.Open cmd.Execute

    Sheet1.Range("A2").CopyFromRecordset rst

    Set rst = Nothing

    if print myvar2
    get '926315', '926316'
    if enter 926315, 926316 in inbox box this is exactly how works in sql select query

  2. #2
    I take it your SQL statement is something like SELCT 'your values' FROM 'Your Table' WHERE 'Field' = @code.

    If this is the case, try changing the WHERE to WHERE 'Field' IN (@Code).

    The IN clause takes comma separated values and returns all matching entries.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts