locked
Undefined Function in Expression RRS feed

  • Question

  • Hello,

    I am using Visual Studio 2015. I have an Update Nonquery that has a custom function in it. I want to update all of the records in the database with the custom function. However, I am getting an error message that says: "Undefined function in 'Depreciation' expression.

    My code is below. The values to be used in the function (YearNum, DateInService, etc.) are the names of the columns in the Access Database. I need the Update Query to go down each row of the Access table and perform the function and put the result in the column DeprFed for each record.

    What am I doing wrong? Is my code wrong, or is there another way that I should be updating the table using the custom function?

    Your help will be greatly appreciated. Thanks so much.

            Using cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AssetDatabase.mdb")
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cn.Open()
                    cmd.CommandText = "UPDATE tblAssetData SET DeprFed = Depreciation(1, [YearNum], [DateInService], [BusUsagePct], [MethodFed, [LifeFed]) WHERE ID > 0"
                    Dim result As Integer = cmd.ExecuteNonQuery
                End Using
                cn.Close()
            End Using
    


    BobV365

    Monday, August 3, 2015 4:16 AM

Answers

  • Hi, thanks for the response. The missing bracket happened when I was editing the code to shorten it for posting with my question, but in the original code the bracket is there. So, that is not what is causing the "Undefined function 'Depreciation' in expression" error.

    Hopefully, someone can point me in the right direction so I can have the custom function in my Sql statement. I know I can do this in Access. If it doesn't work the same in VB.Net, then there must be some other way to accomplish the same end result in VB.Net.

    Maybe I need to loop through each record and define the values that exist on that record for use in the custom function.

    I have revised my original code so that I loop through each record, perform the custom function and then update the value in the database. See my code below. (I abbreviated the code to make it easier to follow for this purpose.)

    Is there a better way to do this rather than looping through each record? If there is a more efficient way, then please let me know what suggestions you have. I would greatly appreciate any help in this matter.

    Try Dim i As Integer = i For Each row As DataRow In AssetDatabaseDataSet.tblAssetData.Rows i = i + 1 Me.TblAssetDataBindingSource.Position = Me.TblAssetDataBindingSource.Find("ID", i) Dim YearNum As Integer = Me.txtYrNum.Text Dim DateInService As Date = Me.txtDateInService.Text Dim BusUsagePct As Double = Me.txtBusUsagePct.Text Dim Cost As Double = Me.txtCostFed.Text

    Dim Method As String = Me.cboMethodFed.Text Using cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AssetDatabase.mdb") Using cmd As New OleDbCommand With {.Connection = cn} cn.Open() Dim Depr1 As Double = Depreciation(1, YearNum, DateInService, BusUsagePct, Method, Cost) Call RoundingCalc(Depr1) MsgBox("Rounded Depr1: " & Depr1) cmd.CommandText = "UPDATE tblAssetData SET [DeprFed] = " & Depr1 & " WHERE [ID] = " & i Dim result As Integer = cmd.ExecuteNonQuery End Using cn.Close() End Using Next row Catch ex As Exception MessageBox.Show(ex.ToString) End Try



    BobV365





    Monday, August 3, 2015 10:13 AM
  • Going this route I would suggest not obtaining data from the DataGridView but instead from the DataSet if you are using strong typed classes generated via the IDE data wizard.

    Example using Microsoft Northwind database customers table. Here CustomersDataSet is in the area beneath the form canvas in the IDE. I am simply showing via Console.WriteLine that we can get to the data.

    For Each row As CustomersDataSet.CustomersRow In Me.CustomersDataSet.Customers.Rows
        Console.WriteLine("{0} - {1}", row.CompanyName, row.ContactName)
    Next

    Or use the BindingSource where in this case CustomersBindingSource is hooked to CustomersDataSet.

    Dim dv As DataView = CType(CustomersBindingSource.List, DataView)
    Dim dt As DataTable = dv.Table
    
    For Each row As DataRow In dt.Rows
        Console.WriteLine("{0} - {1}",
            row.Field(Of String)("CompanyName"), row.Field(Of String)("ContactName"))
    Next
    Both methods shown bypass accessing your controls thus bypass triggering any events of these controls which in the end speeds up processing.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    • Marked as answer by Youjun Tang Monday, August 10, 2015 8:12 AM
    Monday, August 3, 2015 12:48 PM

All replies

  • I don't know. But this - [MethodFed - is missing close bracket.

    La vida loca

    Monday, August 3, 2015 4:36 AM
  • Hi, thanks for the response. The missing bracket happened when I was editing the code to shorten it for posting with my question, but in the original code the bracket is there. So, that is not what is causing the "Undefined function 'Depreciation' in expression" error.

    Hopefully, someone can point me in the right direction so I can have the custom function in my Sql statement. I know I can do this in Access. If it doesn't work the same in VB.Net, then there must be some other way to accomplish the same end result in VB.Net.

    Maybe I need to loop through each record and define the values that exist on that record for use in the custom function.

    I have revised my original code so that I loop through each record, perform the custom function and then update the value in the database. See my code below. (I abbreviated the code to make it easier to follow for this purpose.)

    Is there a better way to do this rather than looping through each record? If there is a more efficient way, then please let me know what suggestions you have. I would greatly appreciate any help in this matter.

    Try Dim i As Integer = i For Each row As DataRow In AssetDatabaseDataSet.tblAssetData.Rows i = i + 1 Me.TblAssetDataBindingSource.Position = Me.TblAssetDataBindingSource.Find("ID", i) Dim YearNum As Integer = Me.txtYrNum.Text Dim DateInService As Date = Me.txtDateInService.Text Dim BusUsagePct As Double = Me.txtBusUsagePct.Text Dim Cost As Double = Me.txtCostFed.Text

    Dim Method As String = Me.cboMethodFed.Text Using cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AssetDatabase.mdb") Using cmd As New OleDbCommand With {.Connection = cn} cn.Open() Dim Depr1 As Double = Depreciation(1, YearNum, DateInService, BusUsagePct, Method, Cost) Call RoundingCalc(Depr1) MsgBox("Rounded Depr1: " & Depr1) cmd.CommandText = "UPDATE tblAssetData SET [DeprFed] = " & Depr1 & " WHERE [ID] = " & i Dim result As Integer = cmd.ExecuteNonQuery End Using cn.Close() End Using Next row Catch ex As Exception MessageBox.Show(ex.ToString) End Try



    BobV365





    Monday, August 3, 2015 10:13 AM
  • Did you verify that the function names are spelled similarly in both places?

    Can you run the Update Query successfully from Access?


    Programming is easy, understanding how is not.

    Monday, August 3, 2015 11:51 AM
  • I don't do much in Access anymore and it is hard to answer your question without knowing the database design, but is it possible you are missing the schema in your call to the function? This is just guessing since the issue appears to be on the Access side rather than the VB side.

    David M. Nichols software engineer

    Monday, August 3, 2015 12:18 PM
  • Going this route I would suggest not obtaining data from the DataGridView but instead from the DataSet if you are using strong typed classes generated via the IDE data wizard.

    Example using Microsoft Northwind database customers table. Here CustomersDataSet is in the area beneath the form canvas in the IDE. I am simply showing via Console.WriteLine that we can get to the data.

    For Each row As CustomersDataSet.CustomersRow In Me.CustomersDataSet.Customers.Rows
        Console.WriteLine("{0} - {1}", row.CompanyName, row.ContactName)
    Next

    Or use the BindingSource where in this case CustomersBindingSource is hooked to CustomersDataSet.

    Dim dv As DataView = CType(CustomersBindingSource.List, DataView)
    Dim dt As DataTable = dv.Table
    
    For Each row As DataRow In dt.Rows
        Console.WriteLine("{0} - {1}",
            row.Field(Of String)("CompanyName"), row.Field(Of String)("ContactName"))
    Next
    Both methods shown bypass accessing your controls thus bypass triggering any events of these controls which in the end speeds up processing.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    • Marked as answer by Youjun Tang Monday, August 10, 2015 8:12 AM
    Monday, August 3, 2015 12:48 PM
  • User defined VBA functions can only be executed from a SQL query when running in Microsoft Access. It cannot be done be via OLEDB/ODBC.

    You may need to fetch the data from tblAssetData and put it into a DataTable, perform the depreciation calculation, and then perform the batch update through an OleDbDataAdapter. That is, unless you can perform the depreciation calculation from your SQL statement using SQL functions.


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Monday, August 3, 2015 1:11 PM