ADO.net with MS Access query containing a module function

[MARKAND911]

I have created an MS Access Query named GetContactCategory

select GetCategoryNames(contactId) as CategoryNames from Contacts

The Function GetCategoryNames is the function written in a module of same database.

Now when i call this query in my c# application using ADO.net it throws error.

Following is my c# code
`

using (OleDbCommand cm = new OleDbCommand())
            {
                cm.Connection = AccessConnection();
                cm.CommandType = CommandType.StoredProcedure;
                cm.CommandText = "GetContactCategory";

                cm.Connection.Open();
                using (OleDbDataReader reader = cm.ExecuteReader())//This line throws error
                {
                    //some code
                }
           }

`
The error which is get Undefined function 'GetCategoryNames' in expression.

what is the problem and whats the solution.

Hello markand911,

You can try using a DataAdapter instead of a Reader. This is how I setup my structure:

DataTable dt = new DataTable("DT");
                        using (OleDbConnection dbconn = new OleDbConnection(connectionString))
                        {
                            OleDbCommand cmd = new OleDbCommand(sprocName, dbconn);
                            cmd.CommandType = System.Data.CommandType.Text;

                            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
                            try
                            {
                                dbconn.Open();
                                dataAdapter.FillSchema(dt, System.Data.SchemaType.Source);
                                dataAdapter.Fill(dt);
                                ds.Tables.Add(dt); //add to dataset
                            }
                            catch (Exception)
                            {
                                throw;
                            }

I will usually try and put a try catch around the opening of the database and filling of the data into my datatable with a breakpoint on the catch for debug purposes. This should be very similar to what you are doing. I have my access query added in as a file inside of VS which I am passing into the OleDbCommand. Try the DataAdapter and see if it works better for you.

Edit:
You may also want to take a look at calling a stored proceedure: http://stackoverflow.com/questions/22856472/how-to-call-stored-queries-of-ms-access-with-parameters or http://stackoverflow.com/questions/9890341/running-ms-access-saved-query-from-c-sharp Undefined function 'GetCategoryNames' tells me that it is not reading the GetCategoryNames file because it doesn't know what it is.