1 Attachment(s)
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):
Attachment 167645
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?
Re: Undefined function in query
The problem is you've enclosed GetAssetPrice in your sql string. Something like...
"SELECT Something, " & GetAssetPrice(etc) & " Something else"
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 :blush:
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
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...
Re: Undefined function in query
Linq to sql might be easier in your situation...
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...
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...
Re: Undefined function in query
Quote:
Originally Posted by
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...
It works in Access because you're not concatenating a string in your query. Try the LINQ queries
Re: Undefined function in query
Ok, thanks. I've never worked with LINQ so I'll have to do some homework...
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
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?