Using Screen Data In A Custom Sql Statement

Legacy Poster

• November 08, 2013 18:38

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'

 

 

0

• 

  Domingo Rodriguez

  Moderator

  ★ BarTender Hero ★

  • November 11, 2013 10:34

  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
• 

  Legacy Poster

  ★ BarTender Hero ★

  • December 18, 2013 14:47

  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
• 

  Domingo Rodriguez

  Moderator

  ★ BarTender Hero ★

  • December 19, 2013 08:02

  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
• 

  Legacy Poster

  ★ BarTender Hero ★

  • March 26, 2015 14:57

  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?

  0

Please sign in to leave a comment.