Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160

    Unanswered: Stored Procedure in VB Combobox

    I need to make a combobox list all the tables that start with "Seed" in an database from odbc. I am using this stored procedure in this code.

    Dim conn As New ADODB.Connection
    conn.Open "DSN=*********"
    Dim rs As New ADODB.Recordset
    Set rs = conn.Execute("sp_MSforeachtable ""SELECT '?'"", @whereand=""AND left(name,4) = 'Seed'""")
    Do While Not rs.EOF
    MsgBox rs(0)
    'combobox.additem rs(0)
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing


    When i do this i only get only 1 message box (meaning 1 result)

    There are about 8 tables that are in the Database. This stroed procedure command does work through query analyzer. Is there something i need or don't need?

    thnx
    Beyond Limitation

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Stored Procedure in VB Combobox

    I usually do it like this, frankly I didn't know that you could reference a stored procedure like this in a recordset.Open statement (declarations left out):

    Set cmd = New ADODB.Command
    With cmd
    .ActiveConnection = gconSQL
    .CommandType = adCmdStoredProc
    .CommandText = "spcGet_REODs_Of_NIA"
    Set prmL = .CreateParameter("P0", adBigInt, adParamInput, , strNIAD_Id)
    .Parameters.Append prmLGBWE_Username = ? " & _
    "ORDER BY GBWE_Username, WEHD_Code "
    .Parameters(0) = Me.cbxUsers .Active
    End With

    [QUOTE][SIZE=1]Originally posted by vextout
    I need to make a combobox list all the tables that start with "Seed" in an database from odbc. I am using this stored procedure in this code.

    Dim conn As New ADODB.Connection
    conn.Open "DSN=*********"
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command, prm as New ADODB.Parameter

    With cmd
    .ActiveConnection = conn
    .CommandType = adCmdStoredProc
    .CommandText = "sp_MSforeachtable"
    Set prmL = .CreateParameter("P0", adVarChar, adParamInput, 100, "SELECT '?'") 'Just guessing if this is what you want
    .Parameters.Append prmL
    Set prmL = .CreateParameter("P1", adVarChar, adParamInput, 100, "AND left(name,4) = 'Seed'") 'Just guessing if this is what you want
    .Parameters.Append prmL
    Set rstL = .Execute
    End With

    Do While Not rs.EOF
    MsgBox rs(0)
    'combobox.additem rs(0)
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing


    Hope this helps.

    Ad.

  3. #3
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    thnx for geting back

    i was playing around with what you gave me i ahd no luck

    when u did --> Set rstL = .Execute
    u meant for it to be declared as adodb.recordset ??
    i renamed rstL to rs (that i declared as recordset) and executed it
    did msgbox rs(0) only thing i got was a
    ?

    also is the setting of those paramaters necessary ?

    tnx
    Beyond Limitation

Posting Permissions

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