dcsimg
Results 1 to 12 of 12

Thread: Undefined function in query

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    234

    Undefined function in query

    I've got a DataAdapter that I'm using to load a DataTable. I've configured the DataAdpater's SelectCommand as follows (note the "GetAssetPrice" function that is embedded in the SELECT statement)...

    Code:
    Dim selectData As New OleDb.OleDbCommand("SELECT Accounts.AccountName, Assets.AssetSymbol, Assets.AssetName, GetAssetPrice(Assets.RecID, #" & Date.Today & "#, ""ClosePrice"") AS AssetPrice, Positions.* " & _
                                                     "FROM Assets INNER JOIN (Accounts INNER JOIN Positions ON Accounts.RecID = Positions.AccountID) ON Assets.RecID = Positions.AssetID " & _
                                                     "ORDER BY Accounts.AccountName, Assets.AssetName", con)
    
    daPositions.SelectCommand = selectData
    The following error is raised when my code populates the DataTable (via the DataAdapter's SelectCommand):

    Name:  2019-04-18_19-32-36.jpg
Views: 200
Size:  27.7 KB

    The GetAssetPrice function works in my VB.Net application. For example:

    Code:
    Debug.Print(GetAssetPrice(20, Date.Now, "ClosePrice").ToString)
    A similar query works in MS Access:

    Code:
    SELECT Accounts.AccountName, Assets.AssetSymbol, Assets.AssetName, GetAssetPrice([Assets].[RecID],Now(),'ClosePrice') AS AssetPrice, [AssetPrice]*[Positions].[PositionShares] AS PositionBalance, Positions.*
    FROM Assets INNER JOIN (Accounts INNER JOIN Positions ON Accounts.RecID = Positions.AccountID) ON Assets.RecID = Positions.AssetID
    WHERE (((Accounts.RecID)=20));
    I don't know why it doesn't work in my VB.Net SelectData statement.

    I've been looking at this for a couple of hours and I'm stuck...can someone please point me in the right direction to fix this error?
    Last edited by Mark@SF; Apr 18th, 2019 at 10:36 PM.

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    22,497

    Re: Undefined function in query

    The problem is you've enclosed GetAssetPrice in your sql string. Something like...

    "SELECT Something, " & GetAssetPrice(etc) & " Something else"

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    234

    Re: Undefined function in query

    Hi .paul. -

    How would I pass the "Assets.RecID" field value to the GetAssetPrice function if I don't enclose the function in my SQL string?

    I think I'm just missing something really obvious

  4. #4
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    22,497

    Re: Undefined function in query

    I'm not 100% sure of this...

    GetAssetPrice( & "Assets.RecID, #" & Date.Today & "#, ""ClosePrice"" & ) & " AS AssetPrice,"

    Basically, you've got to construct a string for the sql part, but your function isn't recognised in sql, because it isn't sql

  5. #5
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    22,497

    Re: Undefined function in query

    It could be that a string isn't ideal for your query. I'm fairly sure you can construct a query with xml. I'll see if i can find an example...

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    22,497

    Re: Undefined function in query

    Linq to sql might be easier in your situation...

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    234

    Re: Undefined function in query

    Right, I need the SQL to convert the GetAssetPrice() function into a decimal value: GetAssetPrice(20,#4/18/2019#,"ClosePrice") = 133.56

    And do this for each row of data that the SQL returns.

    I'm really stumped why this works in MS Access, but not in VB.Net...

  8. #8
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    22,497

    Re: Undefined function in query

    LINQ to SQL

    Creating a DataContext

    Querying a database

    You'll see LINQ gives you much easier control than string concatenation...

  9. #9
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    22,497

    Re: Undefined function in query

    Quote Originally Posted by Mark@SF View Post
    Right, I need the SQL to convert the GetAssetPrice() function into a decimal value: GetAssetPrice(20,#4/18/2019#,"ClosePrice") = 133.56

    And do this for each row of data that the SQL returns.

    I'm really stumped why this works in MS Access, but not in VB.Net...
    It works in Access because you're not concatenating a string in your query. Try the LINQ queries

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    234

    Re: Undefined function in query

    Ok, thanks. I've never worked with LINQ so I'll have to do some homework...

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,086

    Re: Undefined function in query

    Hi,

    did you try the Access query 1:1

    I can't try this
    Code:
     Dim sSQL As String = " SELECT Accounts.AccountName,"
            sSQL &= " Assets.AssetSymbol, "
            sSQL &= " Assets.AssetName, "
            sSQL &= " GetAssetPrice([Assets].[RecID],Now(),'ClosePrice') AS AssetPrice, "
            sSQL &= " [AssetPrice]*[Positions].[PositionShares] AS PositionBalance, "
            sSQL &= " Positions.*"
            sSQL &= " FROM Assets INNER JOIN "
            sSQL &= " (Accounts INNER JOIN Positions ON Accounts.RecID = Positions.AccountID) "
            sSQL &= " ON Assets.RecID = Positions.AssetID"
            sSQL &= " WHERE (((Accounts.RecID)=20));"
    
            Dim selectData As New OleDb.OleDbCommand(sSQL, con)
    
            daPositions.SelectCommand = selectData
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,196

    Re: Undefined function in query

    When you did this in Access, where was the function? It wasn't calling the function from your .NET project, since how would it even know about that project, let alone load the page? Do you have the function defined in Access as a VBA function?
    My usual boring signature: Nothing

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width