Results 1 to 2 of 2

Thread: empty recordset when multiple entires in parameter

  1. #1
    VBAX Newbie
    Joined
    Nov 2011
    Posts
    2
    Location

    empty recordset when multiple entires in parameter

    Hi

    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("A2").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("n1:o1").ClearContents
        Range("A2").Select
    ' Open a Connection using an ODBC DSN named "Pubs".
        cnn.ConnectionString = "DSN=TINACUST;UID=ANALYST;PWD=ANALYST;"
        cnn.Open
    ' 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
    rst.Close
      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
    Last edited by Aussiebear; 06-06-2025 at 02:47 PM.

  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
  •