Using Screen Data In A Custom Sql Statement S’abonner

0
Avatar
Legacy Poster

I am having trouble with something which in theory sounds simple enough.

 

I have a field that I am trying to pull from a database based off of a UPC number. For testing, I set up the SQL statement to ask for a user prompt. So far so good, I can enter the necesary information and I get the desired result. Now the problem is that in production, I will actually be using a handheld device which does not allow for the user prompt. The value that I am looking for though is available as screen data. How can I insert this screen data into my SQL statement, thereby removing the need of the prompt.

 

Here is my statement, where ?upc_cd represents the user Prompt that needs replaced with screen data:

SELECT  "user_def_fld_4" FROM "dbo"."IMITMIDX_SQL" WHERE "upc_cd" = '?upc_cd'

 

 

4 commentaires

0
Avatar
Domingo Rodriguez
Modérateur
Actions pour les commentaires Permalien

When you say "Replace with Screen Data", are you trying to replace this with static information, or are you more looking to replace it with a named data source value from your BarTender document (which will probably be variable)?

 

1. What you could try is the following (using an SQL Statement, not the query prompt):

 

SELECT  "user_def_fld_4" FROM "dbo"."IMITMIDX_SQL" WHERE "upc_cd" = '" & MyUPC & "'"

 

Where MyUPC is the named data source which will contain the "Screen Data" data. You can assign the variable name for the data source by modifying the "Name" field under the "Data Source" tab once you've chosen the right Data Source under BarTender object's properties dialog.

 

2. Another suggestion would be to create your query prompt (not an SQL Stamente), now clicking on the "Query Prompt..." button, click "More Options..." and now choose and write a simple VBScript line:

 

value = Format.NamedSubStrings("myUPC").Value 
 
Where MyUPC is the named data source which will contain the "Screen Data" data. You can assign the variable name for the data source by modifying the "Name" field under the "Data Source" tab once you've chosen the right Data Source under BarTender object's properties dialog.
 
The prompt will still appear at print time, but if you just click on "Print" the actual value taken will be the one being read by the VBScript.
0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

How can I use a named data source in a database query in the database setup.  I know that if I would like to use query prompt, I write (WHERE field = '?queryprompt1').  But I would like to use a named data source referenced to a dropdown list in the data entry from instead.  I tried the example above and it doesn't works.

 

Thanks

0
Avatar
Domingo Rodriguez
Modérateur
Actions pour les commentaires Permalien

The drop-down list will be shown after BarTender's query prompt dialog, so your selection will have no effect. The example I had proposed is a named data source with a "Screen Data" value. In your case, you would better connect to your database directly using VBScript inside the BT document (using ADO technique e.g.), rather than using BarTender's database connection wizard.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

I am not sure why it is that this is hard....

 

Using 2 Named Data Source values, I am trying to pull in a database field. The Named Data Source values are PO_NUMBER and ITEM_NUMBER

 

I have my custom SQL statement which attempts to use the variables with those names.

SELECT "dbo"."303v850lb"."PO1_07" 
FROM  "dbo"."303v850h" ,  "dbo"."303v850lb" 
WHERE  "dbo"."303v850h"."PO_ID" =  "dbo"."303v850lb"."PO_ID" 
and  "dbo"."303v850lb"."PO1_06" = 'PD' 
and  "dbo"."303v850h"."BEG_03" = '"&PO_NUMBER&"'
and  "dbo"."303v850lb"."PO1_LineNo" = 
   (
     SELECT "dbo"."303v850lb"."PO1_LineNo" 
     FROM  "dbo"."303v850h" ,  "dbo"."303v850lb" 
     WHERE  "dbo"."303v850h"."PO_ID" =  "dbo"."303v850lb"."PO_ID" 
     and "dbo"."303v850lb"."PO1_06" = 'VA'
     and  "dbo"."303v850h"."BEG_03" = '"&PO_NUMBER&"'
     and "dbo"."303v850lb"."PO1_07" = '"&ITEM_NUMBER&"'
   )

When I replace the variable names with actual values, I get the return expected.

 

Am I doing something wrong, or do I just need to run this live to see the results. Right now, I have the PO_NUMBER value as well as the ITEM_NUMBER value populated, so shouldn't it look at those values when I print my test label?

Vous devez vous connecter pour laisser un commentaire.