Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: ADO-Command

  1. #1
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Unanswered: ADO-Command

    I have a Sql like that
    SELECT * INTO Temp FROM pbe WHERE pbe_von<=get_stichtag() AND pbe_bis>=get_stichtag();

    Where get_stichtag() is a function.
    How could I use an ADO Comand to Execute this SQL

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    get_stichtag() is a function in the FE right?

    Code:
    TheSQL = SELECT * INTO Temp FROM pbe WHERE pbe_von<=" & get_stichtag() & " AND pbe_bis>=" & get_stichtag()
     
    TheCommand.CommandText = TheSQL
    TheCommand.Execute
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Yes, it is a function in the Front End, I did as you said also I put TheCommand.CommandType= adCmdStoredProc
    But I get the message that get_stichTag is unknown. Thanks

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What does your current call look like now? What does get_stichtag() do anyways? Does it return a static scalar value or is supposed to be different for each record?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also -

    You are building a dynamic SQL string. Therefore:
    Code:
    TheCommand.CommandType= adCmdStoredProc
    No it ain't

    Try adCmdText (or something like it).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    no params for the function suggests static... but who knows!

    stichtag (= deadline ??) smells like a date. unless get_stichtag() is already returning a suitably formatted string "#mm/dd/yyyy#" (which is a good idea by the way), you might have to worry about the #

    ...and don't forget that get_stichtag() needs to be Public in a module for the query to be able to find it.

    izy


    LATER: ...and don't forget that get_stichtag() needs to be Public in a module for the query to be able to find it. COMPLETE bull if it is static --- sooooorry, it's been a difficult day. if get_stichtag() is static it just needs to be in scope when TheSQL is built.
    Last edited by izyrider; 10-03-06 at 12:48.
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and why bother with ADO unless there is some hidden aspect to this which you didn't mention
    currentdb.execute TheSQL
    works fine.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Furthermore, why is the logic on the front end instead of the database? What does that vba function do that couldn't be re-written as a udf?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are we saying please could you post the rest of the ADO code (including the connection) and the function code too?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well yeah, but that's the easy way to ask...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You are right, get_StichTag gets today's date if no other date was entered

    PHP Code:
    Function get_stichtag() As Variant
       
    If IsNull(glb_stichtag) Or IsEmpty(glb_stichtagThen
          get_stichtag 
    DateSerial(Year(date), Month(date), Day(date))
       Else
          
    get_stichtag DateSerial(Year(glb_stichtag), Month(glb_stichtag),  
                               
    Day(glb_stichtag))    
      
    End If
    End Function 
    Where glb_Stichtag is a global variable....

  12. #12
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    get_stichtag() is a function in the FE right?

    Code:
    TheSQL = SELECT * INTO Temp FROM pbe WHERE pbe_von<=" & get_stichtag() & " AND pbe_bis>=" & get_stichtag()
     
    TheCommand.CommandText = TheSQL
    TheCommand.Execute
    HTH
    It functions in this way, when you give the Sql, but I'd like not to give the sql, because some of them are very complex and they are already stored in DB as Queries, so I'd like to give only the query name which includes sub queries and most of them call this function get_stichtag()

    Thanks a lot

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Please can you post your ADO code (in full)?

    Just to make sure there is no more confusion about this (once you've posted the code) - this SQL:
    SELECT * INTO Temp FROM pbe WHERE pbe_von<=get_stichtag() AND pbe_bis>=get_stichtag();

    Is the sql from a query in the BE that you are calling from the FE by name and the get_stichtag() function is in the FE?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Thank you for your help, I'm still new by ADO....

    PHP Code:
      Set glb_Command = New ADODB.Command
      With glb_Command
       
    .CommandText "Select * from pgd where ((pgd_eintrittsdatum<=" 
                                
    get_stichtag() & _
                                
    " Or pgd_eintrittsdatum Is Null) AND 
                                 (pgd_austrittsdatum>=" 
    get_stichtag() & _
                                
    " Or pgd_austrittsdatum Is Null));"
       '====================================================
        ' 
    .CommandText "ahis_pgd_st"    ' This is the name of the query which 
                                                       ' 
    I'd like to use   
       '
    ====================================================
        .
    CommandType adCmdText  'adCmdStoredProc
        .ActiveConnection = glb_Conn.ConnectionString
      End With
       
            Set glb_RS = New ADODB.Recordset    
                 
            glb_RS.Open glb_Command.CommandText, glb_Conn, adOpenDynamic, 
                               adLockOptimistic

            MsgBox glb_RS.RecordCount 

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please could you add:
    Code:
     
    Debug.Print .ActiveConnection.ConnectionString
    Debug.Print .CommandText
    just before end with. Could you also confirm the data type of pgd_eintrittsdatum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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