Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    Unanswered: How to pass a variable to a stored procedure

    Hi all,

    I would like to ask about passing a variable to a stored procedure. I
    use a VBA from Access 2002 Front End.

    My Access function looks like this:

    Public Function AppendToLogTable(UserName As String)
    On Error GoTo AppendToLogTable_Err

    Dim conConnection As ADODB.Connection
    Dim StrSQL As String

    Set conConnection = CurrentProject.Connection
    StrSQL = "usp_AppendToLogTable UserName"
    conConnection.Execute StrSQL, iAffected, adExecuteNoRecords

    WrapUp:
    conConnection.Close

    AppendToLogTable_Exit:
    Set conConnection = Nothing
    Exit Function
    AppendToLogTable_Err:
    If Not conConnection Is Nothing Then
    If conConnection.State = adStateOpen Then conConnection.Close
    End If
    MsgBox Err.Description
    Resume AppendToLogTable_Exit
    End Function

    The SP looks like this:

    CREATE PROCEDURE dbo.usp_AppendToLogTable @UserName varchar(8)
    AS
    INSERT INTO tbl_UsageLog (Email_Alias,LoginTime)
    VALUES (@UserName, GETDATE())
    GO

    I use the AppendToLogTable function in this way:

    Call AppendToLogTable ("John")

    What I expect is name "John" to be added to table tbl_UsageLog but instead
    of this I get UserName added to the table.

    How can I pass a variable from function AppendToLogTable (Name As String)?

    Regards

    Dani

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    It looks like john would be the one inserted into the table if you called it that way, not sure what you mean when you say instead of John you get username added to table.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    UserName

    If I hard code the passing value in this way:
    .........
    Set conConnection = CurrentProject.Connection
    StrSQL = "usp_AppendToLogTable 'John'"
    conConnection.Execute StrSQL, iAffected, adExecuteNoRecords
    ..........

    I get 'John' appended to the table.

    However I would like to pass a string variable named UserName.
    I would like to initialize the string UserName and then to execute the VB function that calls the SP named usp_AppendToLogTable.

    My question is how to do it?
    If I use a code like this:

    Dim UserName As String
    UserName="AnyName"

    Set conConnection = CurrentProject.Connection
    StrSQL = "usp_AppendToLogTable UserName"
    conConnection.Execute StrSQL, iAffected, adExecuteNoRecords
    .........

    I get string "UserName" inserted not "AnyName".
    How to pass a string variable to the SP?

    I am new to SP's and would appreciate any help.
    Thanks.

  4. #4
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I think this has to do with your VB coding.
    When you put your VB declared variable in a SQL query string, you won't read it as a VB declared variable.

    Maybe you could try this:

    Set conConnection = CurrentProject.Connection
    StrSQL = "usp_AppendToLogTable "& UserName &"
    conConnection.Execute StrSQL, iAffected, adExecuteNoRecords
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  5. #5
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    It works. Thank you.

    Dear Patrick,

    Thank you for your help.

    Set conConnection = CurrentProject.Connection
    StrSQL = "usp_AppendToLogTable " & UserName & ""
    conConnection.Execute StrSQL, iAffected, adExecuteNoRecords

    This was my first call to my first stored procedure.

    Best Regards

Posting Permissions

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