Wonderware, SQL commands, and Excel

[johnd_125]

I have an application where Intouch needs to send a couple values to Excel, where a unique row will be found. By using a SQL command, 3 other values in the row will be read and assigned back to Intouch tags.

The problem I've had is that something keeps the data from being read. The problem seems to be the format of the Excel file. If the bind list is configured to read just 1 of the values, then sometimes it will work. But, adding 1 or 2 more values causes it to fail. Another file with the same exact column headings may not work at all. I've set the Excel files involved to either "text" or "number" depending on the Intouch tag they are tied to. I was told by the local dist. to use a 32 bit version of the ODBC manager. The DSN seems to be limited to Excel 97-2003 version.

Documentation and examples for this seems to be very weak. Even the local distributor doesn't seem too interested.

I feel like I'm very close to making this work but if I can't get a file to work in a predictable manner, its useless.

BTW, there is no possibility of getting away from Excel. This is a QS, quality controlled file and they want to get their data from it.

TIA.

 

[danatomega]

Maybe you could post some code so we could see what you're doing now and how we can help ...

 

[johnd_125]

I apologize for the delay, but I'm back on this, now.

I am still at the point where I can successfully use SQL commands to query for values in 2 columns of an Excel spreadsheet and read values back from 3 other columns. BUT the problem is that it only works for some rows!! I have examined and worked with the excel spreadsheet and cannot find a reason or a solution why it will not work on some rows.

Here's my setup.

This is a dummied down excel spreadsheet I want to use. I will send SQL commands with criteria for the first 2 columns and I expect to receive back the values in the remaining 3 columns. The file is save in Excel 97-2003 format.

This is my Intouch screen. I use the pull downs near the top right to select "A" or "I" for from the ActInact column. I use the other pull downs to select a value from the MatNum column. When I have the 2 choices made, I press the Retrieve Record pushbutton. It then populates the values at the left of the screen in the highlighted areas with my 5 values for the first record. Pressing Next will let me see if there is a duplicate record.

Here is a record that has been successfully retrieved. I pressed the Next button once, so it corresponds to the 2nd Record (row 3) in the excel spreadsheet. Note that there are 4 records which should meet this criteria.

Now for the code behind the scenes.

A Window Script..
wcLoadList ( "ComboBox_1", "Products2.txt" );
wcLoadList ( "ComboBox_2", "Colors2.txt");
wcLoadList ( "ListBox_2", "MatNumList.txt");

ResultCode = SQLConnect (ConnectID, "DSN=cups4");


ErrorMsg = "";

IF ResultCode <> 0 THEN
ErrorMsg = SQLErrorMsg ( ResultCode );
ENDIF;

Action behind the Retrieve Record PB:
WhereExpr = "ActInact = '" + ComboItem1 + "' and MatNum = '" + ComboItem2 + "'";

Datachange script for the tag WhereExpr:
ResultCode = SQLSelect ( ConnectID, "lot", "lot", WhereExpr, "" );

ErrorMsg = "";

IF ResultCode <> 0 THEN
ErrorMsg = SQLErrorMsg ( ResultCode );
ENDIF;

ResultCode = SQLFirst ( ConnectID );

ErrorMsg = "";

SQL Bind List Setup. The tagnames correspond to data displayed on the Intouch window.

SQL Table setup

DSN/ODBC Setup

The Issue is that while this seems to work well on some rows in the excel spreadsheet, there are some rows it will not work on. I've checked the formatting of the cells. I've copied and pasted the Material Number from cells that are working to cells that are not and they still don't work.

My Local Dist. has helped some but now they are telling me to abandon the ODBC altogether. They have told me to remove the spaces and special characters from the column headings even though I don't have any to start with. They are suggesting the following code:

SQLConnect( ConnectionId, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\JDControl\lot.xls; Extended Properties=" + StringChar(34) + "Excel 12.0 Xml;HDR=YES" + StringChar(34) + ";" ); SQLSelect( ConnectionId, "[lot$]", "HTParam", "", "" ); SQLFirst( ConnectionId );

XL_RowCount = SQLNumRows( ConnectionId ); XL_CurrentRow = 1;

I've tried this with changes only to point to the correct location and name of the excel spreadsheet, and corrected the Bind List argument. Still doesn't work. it gives an error:

ADODB.Connection: Provider cannot be found. It may not be properly installed.

Any ideas? Thanks....

 

[thomas1000]

Did you manage to solve your problem

 

[johnd_125]

Yes, the application has been running for awhile, now.