Hi,

I'm trying to start a small development project in Visual Studio 2013 using the ODBC 9.1A and I'm getting an error when trying to retrieve the Tables.

My connection is successful as per the screen shot below but Visual Studoi cannot retrieve the tables.

SAny help would be appreciated.

Connection Successful

Connection Wizard

PS: I am able to get data into Excel...

Hi Yann:

Error code HYC00 represents a number of errors such as "Catalogs are not supported", "Schemas are not supported", "Create index statements are not supported", "Create table statements are not supported" and "Optional feature not implemented". As you can see, the messages aren't very specific but they do indicate a significant failure.

At the point this error appears, have you selected a table or are you are trying to populate the list of tables?

BTW, I don't know if it would make any difference at all, but there is a new version of the ODBC utility. If you are on the annual upgrades plan, you could install version 9.2.

Could you please add a screen shot of the DSN configuration?

Hi Bruce,

Thanks for the reply.

At the point where I get the error I am waiting for the Tables to populate.
I have tested with another ODBC Datasource (SQL Server) and it populates properly.

I am waiting for the install codes from my reseller to get the 9.2A installed, it might help...

Here is the screen shot of my Adagio 32 Bit DSN
Data Directory points to the Data Folder...
Username and Password are also populated.

Adagio 32 Bit DSN

Thanks
Yann

Hi Yann:

To the extent that the DSN has anything to show, it looks correct. You could try a single asterisk in place of "AR*", though it shouldn't make any difference in reality. You've created a 32-bit DSN which is correct for Visual Studio. Let's see if the new ODBC version helps at all.

I updated to 9.2A
tested and not working...

I changed the Table Selection to * from AR*
tested and not working...

I updated the Data Directory from \\SERVER\DATAFOLDER\ to mapped drive G:\DATAFOLDER
tested and not working...

Not sure what the issue is at this point...

Can you connect to the sample data using Excel? Can you connect to your data using Excel?

Yes, I have no issue using Excel Query to bring the data in.

Perhaps it is the call that is trying to populate the tables that is causing the problem. Have you tried just opening the connection and reading known fields from a known table? You can get the ODBC table and field names from the Data Dictionary Exporter.

Perhaps a description of what you are trying to accomplish would be useful.

Thanks Andrew,

At the end of the day, what we are trying to accomplish is show the current, +30, +60, +90 for each of our customers in a windows app.
The table I was thinking that would be appropriate to work with is the "AR Trans w Cust" (that's the name in GridView.

There seams to be different table names depending on where you look.
in Excel it comes up as ARTranswCust__ar92a100

I was looking for a matching table after using the Data Dictionary Exporter but could not find anything like the ARTranswCust__ar92a100 or "AR Trans w Cust"
Maybe these are views...

Any chance you could point me in the right direction?
Thanks
Yann

Hi Yann

Why not use the AR Customer Master table - it has those fields as well, and reading from that table is very very straightforward. The only downside is that someone has to run Receivables | Maintenance | Calculate Customer Aging once a day. But that is not so awful, because it can be set to run automatically when the first person launches Receivables each morning.

When you run that function, you probably will want to uncheck "Update on hold by", and you can choose whether to update the fields based on the document date or the due date.

Steve

Hi Yann,

Did you want your app to do the 30/60/90 calculations, or is it OK to have Adagio do this ahead of time with the Calculate Customer Aging function? If the latter choice would work, then all you need is the customer master file (without transactions), as the aged amounts are stored in fields titled 'Aged Current', Aged Bucket 1', 'Aged Bucket 2', etc.

"..small development project in Visual Studio 2013 using"

If you upload a backup VS2013 project or the "code snippet" that is causing the failure I will have R&D take a look.
Upload to www.softrak.com/dbr for Tech Support attention Michael

Thanks Michael,

Because I am not as familiar as I would like to be with the Adagio tables, I'm trying to use the Data Source Configuration Wizard in Visual Studio.

If you have an example of a Adagio Connection string and some DataAccess code, it could be helpful.

Can I use SqlCommand when querying your tables?

Thanks
Yann

Ok,

I was able to get all the records from the ARTranswCust__ar92a100 table by using the following code...
The Wizard does not seam to be compatible with the Adagio Data Source for some reason. Could be a problem similar to the Excel Query, where it won't display tables with more than 255 columns...

My Data Table was filled after a good 15-20 seconds (it wasn't very fast).
I'll try other tables as well as filter the results to see if I can speedup the process.



DataTable vendorTransactions = new DataTable();

OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = "DSN=Adagio Data Source; ";
try
{
conn.Open();
OdbcCommand command = conn.CreateCommand();
command.CommandText = "select * from ARTranswCust__ar92a100";

OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = command;
adapter.Fill(vendorTransactions);

// Process data here.
}
catch (Exception ex)
{
MessageBox.Show("Failed to connect to data source");
}
finally
{
conn.Close();
}

I would not use "Select *" from any table. In this case you are getting back 276 columns time # records in AR Transaction file.

Use ...\Softrak\system\DataDictionaryExporter.exe /ODBC to create an Excel Workbook that lists all the data elements in all the tables for a particular application.

With the AR 92 Sample data Select * From ARTRanswCust returned 185,000 data cells.

Yes, Thank you.
I had done the Data Dictionary Export already.

in response to the post from "Softrak Tech Support" it was suggested that I look in the 'Aged Current', Aged Bucket 1', 'Aged Bucket 2', etc.
but there is no data in there...

Even on my Customer screen, I do not have any data in the Current, 1-30, 31-45, 46-60, over 60 boxes at the bottom of the window...

Thanks

Hi,

Further to my last post, I noticed that if I want to pick columns by defining my own query, the column names do not match what came out of the Data Dictionary Export.
for example, i am able to do this: select cust, Name, Name2, BalanceOutstanding from AR92ACST where BalanceOutstanding > 0
But the columns are actually named as such in the Data Dictionary
cust # (with a space and a # sign)
Name
Name 2 (with a space)
Balance Outstanding (with a space)

How do I know what the column should be called?

Thanks
Yann

Use ...\Softrak\system\DataDictionaryExporter.exe /ODBC to create an Excel Workbook that lists all the data elements in all the tables for a particular application.

NOTE: /ODBC to create ODBC compliant names

You need to start the Data Dictionary Exporter with /ODBC on the command line to get the ODBC names. Otherwise you get the GridView Names.

You need to run "Calculate Customer Aging" to populate the fields in the customer master.

Thank you.

Is there any way to change the settings for the aged values?
If I wanted to 1-30, 30-60, 60-90 and over 90 in the fields of the Customer Master table?

AgedBucket1
AgedBucket2
AgedBucket3
AgedBucket4
AgedDays1
AgedDays2
AgedDays3

Thanks
Yann

When you run the Calculate function, you can change the dates. The default dates come from Company Profile | Statements/Reports