Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    18

    Unanswered: Send Variable to stored procedure - how?

    I have a passthrough query to a SQL server database that calls on a stored procedure to update a table that I have linked to my access database.

    The procedure needs one variable input (datatype=integer) with the pt-query, and I want this input to be entered by less advanced users in some kind of input field or in a form or something.

    I had a similar call for a stored procedure where the variable was of datatype text. Then I just referred to the input field [forms]![frmInputform]![txtInput] in my exec statement and it worked fine... But for some reason this doesn't work for the Integer variable ??????

    Anyone have input on this problem???

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Nenne View Post
    I had a similar call for a stored procedure where the variable was of datatype text. Then I just referred to the input field [forms]![frmInputform]![txtInput] in my exec statement and it worked fine...
    Are you really sure? Please could you supply the code? Because that is no pass through query I know of.
    A pass through is simply a string plus connection parameters, passed to the server to execute. As such, you need to change the string content of the query. I do this by instantiating a DAO querydef object and changing it via that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2009
    Posts
    18
    Quote Originally Posted by pootle flump View Post
    Are you really sure? Please could you supply the code? Because that is no pass through query I know of.
    A pass through is simply a string plus connection parameters, passed to the server to execute. As such, you need to change the string content of the query. I do this by instantiating a DAO querydef object and changing it via that.
    Well, I'm not absolutely certain, but the result looked correct. It was actually the same exec statement both times, but I rewrote the stored procedure to find the text variable out for itself.... Originally it looked like this (stored procedure I don't have access to from home, so I can't include it):

    Code:
    exec signals.prod_file_generate '[forms]![frmCurrentVersion]![VM_version]', [forms]![frmCurrentVersion]![Quarter]
    Which did not work, but when I entered Quarter as 20094 instead, it worked fine and I got my table. Then I figured out how to exclude the textvariable, but I still need to give the Quarter as Integer...

    Can I do it with VBA instead 'you think?

    I only have a vague idea what DAO is and the querydef object... - can you help my out a little more there?? Can I use this to let users change the variable in a form or something?
    Last edited by Nenne; 03-04-10 at 18:47.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Unfortunately my laptop disk has shuffled its mortal coil so you'll either have to wait, do some Googling or hope another poster can help
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If your procedure expects a numeric (Integer) parameter, just remove the single quotes around the reference to the Form object, or, even better, explicitly cast it:
    Code:
    exec signals.prod_file_generate Clng([forms]![frmCurrentVersion]![Numeric_XXX]), Clng([forms]![frmCurrentVersion]![Quarter])
    Have a nice day!

  6. #6
    Join Date
    Jun 2009
    Posts
    18
    Quote Originally Posted by pootle flump View Post
    Unfortunately my laptop disk has shuffled its mortal coil so you'll either have to wait, do some Googling or hope another poster can help
    Oh, that doesn't sound good... Sorry about that...

    If anyone else can help, I've tried to find some help by googling and I ended up with this piece of code, that don't work, and I'm sure it's because I don't really have all the objects, methods and properties in order... Someone who's a little more into programming than me probably sees the problem immediately...

    Code:
    Sub StoredProcedure()
    
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strConnectString As String
    Dim intQuarter As Integer
    
    Set dbs = CurrentDb
    
    intQuarter = [Forms]![frmLogProd]![ComparisonQuarter]
    strConnectString = "ODBC;DSN=Custom;UID=Helenask;DATABASE=Custom;Trusted_Connection=Yes"
    
    Set qdf = dbs.CreateQueryDef()
    With qdf
      .Connect = strConnectString
      .ReturnsRecords = False
      .SQL = "exec signals.prod_file_generate " & intQuarter
      .Execute (dbSQLPassThrough)
    End With
    
    Set qdf = Nothing
    Set dbs = Nothing
    
    End Sub
    When I try to execute this code I get "Object invalid or no longer set" Error. It bugs out on the .Execute line.

    The connection string and the Integer variable both look OK.

    Thank you for any input!
    /Nenne

  7. #7
    Join Date
    Jun 2009
    Posts
    18
    Quote Originally Posted by Sinndho View Post
    If your procedure expects a numeric (Integer) parameter, just remove the single quotes around the reference to the Form object, or, even better, explicitly cast it:
    Code:
    exec signals.prod_file_generate Clng([forms]![frmCurrentVersion]![Numeric_XXX]), Clng([forms]![frmCurrentVersion]![Quarter])
    OK, so that SHOULD actually work? Because I've tried, but I get Syntax error... And I was thinking maybe Pootle was correct that it is not possible to include references to forms in a passthrough query like I tried... (even if it seemed to work, I might not have gotten the result I actually thought I did)

    /Nenne

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you use a casting function (Clng, Cstr, etc...) you do not pass a reference to a control on a form to your procedure. If you really want to be sure of it you can use an intermediate variable:
    Code:
    lngIVar = Clng(Forms!...)
    exec signals.prod_file_generatelngIVar...
    Usually, this is not necessary, though.

    Another thing you might want to try would be to completely reference what you pass to the procedure and not rely on objects default properties:
    Code:
    exec signals.prod_file_generate Forms!frmCurrentVersion!Numeric_XXX.Value
    Have a nice day!

Posting Permissions

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