Undefined function in query

[Mark@SF]

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)...

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):



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

Debug.Print(GetAssetPrice(20, Date.Now, "ClosePrice").ToString)

A similar query works in MS Access:

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?

[.paul.]

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

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

[Mark@SF]

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

[.paul.]

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

[.paul.]

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...

[.paul.]

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

[Mark@SF]

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...

[.paul.]

LINQ to SQL

Creating a DataContext

Querying a database

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

[.paul.]

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

[Mark@SF]

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

[ChrisE]

Hi,

did you try the Access query 1:1

I can't try this

 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

[Shaggy Hiker]

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?