Onfilllist With Multiple Fields In Recordset 关注

0
Avatar
Legacy Poster

As a current Label Matrix user (old version) I am evaluating Bartender as a replacement. One of the important features should be to retract and use data from an SQL database and build the label upon the query results.

 

In order to test this I have created a simple label design with an itemcode and barcode field. 

Secondly I created within VB a dropdown list as user input based on this forum thread:

 

http://seagullscientific.invisionzone.com/index.php?/topic/201-vbscript-to-populate-a-dropdown-list/

 

It works fine and displays a drop down list of all item codes during the user print request.

What I do not get to work is the use of extra fields in the recordset created from the query.

 

The original sample in the thread mentioned above is:

 

      strCon = "SELECT " & columnName & " FROM " & Tablename & " ORDER BY "& columnName

 

What I would like to have is:

 

      strCon = "SELECT " & column1Name & "," & column2Name & " FROM " & Tablename & " ORDER BY "& column1Name

 

The query itself is ok, SQL does it. column1Name should be the drop down list item but column2Name should also be returned as soon as an item as been selected.

 

In my sample, I would like to select the itemcode and get both itemcode and barcode field returned when selected. The barcode field will the be used to generate the actual barcode.

 

 

When doing a direct selection on the database from the print menu screen, it works fine, but not using the VB OnFill function.

 

 

 

This functionality is very important for our production environment and making it work or not will be highly decisive to purchase Bartender or not.

 

Thanks,

Alex

11 评论

0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

Do you have a sample BT document and sample database to attach to this topic? If yes, please use the "More Reply Options" when posting a new response to attach the files for us to examine.

0
Avatar
Legacy Poster
评论操作 固定链接

Thank you for your answer.

 

Our main database is 150GB, so I won't include that.. :) I have created a sample SQL2005 database with the following records:

 
ItemCode         UserField_02
1100761000    8716236258352
1100761001    8716236342181
1100821000    8716236247868
1100821001    8716236342204
 
 
The BT file was changed to work on this database.
 
I have attached the BT file, but the upload of the test.bak was not permitted. I renamed the database backup file test.bak to test.txt. Please rename it back to test.bak before restoring it to an SQL server.
 
Alex
 
0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

Do you perhaps have an SQL Server 2008 R2 database example (or by default an Access) sample to send me?

0
Avatar
Legacy Poster
评论操作 固定链接

I have created the same database in SQL Server 2008 R2, but was not able to connect to it from BT. You will need to change the database connection and VB ado connection string in the BT file yourself.

 

Enclosed is the database file, again renamed to "test.txt"

0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

Although I still don't 100% understand what you're exactly after, I believe that the below forum thread should help:

http://seagullscientific.invisionzone.com/index.php?/topic/2090-newbie-questions/?hl=textfruit#entry6481

0
Avatar
Legacy Poster
评论操作 固定链接

Thank you. This puts me on track a bit.

 

But in this example.

 

        Set rs = objConn.Execute("SELECT * FROM [Sheet1$] WHERE Fruit = '" & TextFruit & "'")

 

The variable 'TextFruit' is not defined anywhere above. It would need to have the value of the previous selected fruit.

 

Please explain.

0
Avatar
Legacy Poster
评论操作 固定链接

OK, It is working. 

 

(The TextFruit was the name of the datasource. )

 

I have managed to display the correct barcode upon selection of the Itemcode from the drop down list.

Thanx again!!

 

Saludos,

 

Alex

0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

Good to know that you're up and running now Alex!

0
Avatar
Legacy Poster
评论操作 固定链接

Domingo,

 

Evaluation is approaching the end phase. Currently the following has been built:

 

1. Drop down list from items within ERP database.

2. Upon selection from an item, retrieve description of this item and print it

3. Upon selection from an item, retrieve barcode no. of this item and print barcode

4  Upon selection from an item, look up refence code for retrieving path and file name of item logo picture from a separate database

    and print the logo dependant on the item chosen. (Different logo's for different items)

 

This covers most of the functionality I was looking for. Further design will involve 'more of the same functionality'.

 

But what I do find quite annoying... is the situation when printing.

 

The 'use database' tick box is activated by default. Upon most print jobs I need to uncheck this box. If I do not, the complete Item table is loaded which takes a lot of time. Sometimes however, the box remains unchecked.

 

Any idea how to default the 'Use Database' tickbox to 'unchecked'?

 

Alex

 

 

0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

If you connect to your database via VBScript, do you really need to setup a database connection in BarTender via the "File > Database Connection Setup" dialog?

0
Avatar
Legacy Poster
评论操作 固定链接

Thanx, Point is clear. I removed the database connection and handle all interfacing with VB script. 

 

Problem is solved, thanx.

请先登录再写评论。