Use Vbscript To Complete Postgresql Query Using Inputbox Inside Select Statement S’abonner
Hi everybody, I am new to VBScript so please be gentle. I am trying to use a Visual Basic Script that will use an input variable provided at print time by an operator from a Visual Basic InputBox to complete our custom postgresql query...and then use that output to populate my label. Currently this is the path I am on but I am open to ideas for better methods of achieving my desired result, which is simply to pass in a print-operator typed number to the WHERE clause in my custom postgresql statement prior to the query hitting the database. This will reduce the time the query takes if it only searches for the one record instead of outputting all possible shipment numbers. So, here we go...
Below is my custom postgresql query:
SELECT shiphead_number "Shipment#",
cohead_number "Sales Order#",
REPLACE(cohead_custponumber, '-', '') "Purchase Order#",
cust_id,
CASE WHEN POSITION('Rev' in (CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END),'-',''))) > 0
THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END),'-',''), 'REV', 1)
ELSE (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END),'-',''))
END)) > 0
THEN split_part((CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END),'-',''))) > 0
THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END),'-',''), 'REV', 1)
ELSE (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END),'-',''))
END), 'Rev', 1)
ELSE (CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END),'-',''))) > 0
THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END),'-',''), 'REV', 1)
ELSE (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END),'-',''))
END)
END AS "True Part Number",
CASE WHEN POSITION(' ' in (CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END)) > 0
THEN CONCAT(split_part(CASE WHEN POSITION('-' in (CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END)
) > 0
THEN CONCAT(split_part(CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END, '-', 1),
split_part(CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END, '-', 2),
split_part(CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END, '-', 3)
)
ELSE (CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END)
END, ' ', 3),
split_part(CASE WHEN POSITION('-' in (CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END)) > 0
THEN CONCAT(split_part(CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END, '-', 1),
split_part(CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END, '-', 2),
split_part(CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END, '-', 3)
)
ELSE (CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ',', 1)
ELSE item_number
END)
END, ' ', 4)
)
ELSE '-'
END AS "REV LEVEL",
CASE WHEN POSITION(',' in item_descrip1) > 0
THEN split_part(item_descrip1, ', ', 2)
ELSE split_part(item_descrip1, ', ', 1)
END AS "True Part Description",
CAST(SUM(shipitem_qty) AS integer) "Total Item Quantity",
CASE WHEN POSITION(' ' in cohead_shiptoaddress2) <= 1
THEN CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, chr(13), cohead_shiptocountry)
ELSE (CASE WHEN POSITION(' ' in cohead_shiptoaddress3) <= 1
THEN CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptoaddress2, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, chr(13), cohead_shiptocountry)
ELSE CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptoaddress2, chr(13), cohead_shiptoaddress3, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, '*', cohead_shiptocountry)
END)
END AS "Address(FULL)",
REPLACE(STRING_AGG((CASE WHEN EXISTS(
SELECT string_agg(metric_value, ', ')
FROM metric
WHERE metric_name='LotSerialControl'
AND metric_value ='t'
)
THEN x.ls_number
ELSE ''
END), ', '), '-', '') AS "LotNumbers"
FROM shiphead
LEFT JOIN cohead
ON shiphead_order_id = cohead_id
AND shiphead_order_type = 'SO'
LEFT JOIN cust
ON cust_id = cohead_cust_id
LEFT JOIN shipitem
ON shiphead_id = shipitem_shiphead_id
LEFT JOIN coitem
ON coitem_id = shipitem_orderitem_id
LEFT JOIN itemsite
ON coitem_itemsite_id = itemsite_id
LEFT JOIN item
ON itemsite_item_id = item_id
LEFT JOIN (
SELECT ls_number,
shipitem_shiphead_id,
shipitem_orderitem_id
FROM invdetail, invhist, shipitem, ls
WHERE
(shipitem_invhist_id=invhist_id)
AND (invhist_id=invdetail_invhist_id)
AND (invdetail_ls_id=ls_id)
) x
ON ( x.shipitem_shiphead_id = shiphead.shiphead_id
AND
x.shipitem_orderitem_id = coitem.coitem_id
)
--WHERE shiphead_number='" & CStr(searchkey) & "' --JT used for VBScript in BarTender(allows operator input to complete query)
GROUP BY shiphead_number,
item_descrip1,
item_number,
cohead_number,
cohead_custponumber,
cohead_shiptoname,
cohead_shiptoaddress1,
cohead_shiptoaddress2,
cohead_shiptoaddress3,
cohead_shiptocity,
cohead_shiptostate,
cohead_shiptozipcode,
cohead_shiptocountry,
x.ls_number,
cohead_cust_id,
cust_id
ORDER BY shiphead_number DESC;
If you notice the line with this: "--WHERE shiphead_number='" & CStr(searchkey) & "". This line, if uncommented and inserted into a vbscript, will provide an InputBox for the print-operator to type or scan in a shipment number. I want that input variable to be passed into my query before it hits my database, thus reducing the collected records from the query. Below is my same custom query put into the "OnPrintJobStart" VBScript section:
'------QUERY PARAMETERS------------------------------------
'Search for...:
searchkey = InputBox("Enter Shipment Number")
'----------------------------------------------------------
connectionparameters = "Driver={PostgreSQL ANSI(x64)};Server=MYIP;Port=5432;Database=MYDB;Uid=admin;Pwd=REMOVED;"
sqlcall = "SELECT shiphead_number ,cohead_number,REPLACE(cohead_custponumber, '-', ''),cust_id,CASE WHEN POSITION('Rev' in (CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''))) > 0THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''), 'REV', 1)ELSE (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-','')) END)) > 0 THEN split_part((CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''))) > 0THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''), 'REV', 1)ELSE (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-','')) END), 'Rev', 1)ELSE (CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''))) > 0THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''), 'REV', 1)ELSE (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-','')) END)END,CASE WHEN POSITION(' ' in (CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END)) > 0THEN CONCAT(split_part(CASE WHEN POSITION('-' in (CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END)) > 0 THEN CONCAT(split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 1), split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 2), split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 3)) ELSE (CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END) END, ' ', 3),split_part(CASE WHEN POSITION('-' in (CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END)) > 0 THEN CONCAT(split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 1), split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 2), split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 3)) ELSE (CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END) END, ' ', 4))ELSE '-'END,CASE WHEN POSITION(',' in item_descrip1) > 0THEN split_part(item_descrip1, ', ', 2)ELSE split_part(item_descrip1, ', ', 1)END,CAST(SUM(shipitem_qty) AS integer),CASE WHEN POSITION(' ' in cohead_shiptoaddress2) <= 1THEN CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, chr(13), cohead_shiptocountry)ELSE (CASE WHEN POSITION(' ' in cohead_shiptoaddress3) <= 1 THEN CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptoaddress2, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, chr(13), cohead_shiptocountry) ELSE CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptoaddress2, chr(13), cohead_shiptoaddress3, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, '*', cohead_shiptocountry)END)END,REPLACE(STRING_AGG((CASE WHEN EXISTS(SELECT string_agg(metric_value, ', ') FROM metric WHERE metric_name='LotSerialControl' AND metric_value ='t')THEN x.ls_number ELSE ''END), ', '), '-', '')FROM shiphead LEFT JOIN cohead ON shiphead_order_id = cohead_id AND shiphead_order_type = 'SO'LEFT JOIN cust ON cust_id = cohead_cust_id LEFT JOIN shipitem ON shiphead_id = shipitem_shiphead_id LEFT JOIN coitem ON coitem_id = shipitem_orderitem_id LEFT JOIN itemsite ON coitem_itemsite_id = itemsite_id LEFT JOIN item ON itemsite_item_id = item_id LEFT JOIN (SELECT ls_number,shipitem_shiphead_id,shipitem_orderitem_id FROM invdetail, invhist, shipitem, ls WHERE(shipitem_invhist_id=invhist_id) AND (invhist_id=invdetail_invhist_id) AND (invdetail_ls_id=ls_id)) x ON ( x.shipitem_shiphead_id = shiphead.shiphead_id AND x.shipitem_orderitem_id = coitem.coitem_id) WHERE shiphead_number='" & CStr(searchkey) & "' GROUP BY shiphead_number,item_descrip1,item_number,cohead_number,cohead_custponumber,cohead_shiptoname,cohead_shiptoaddress1,cohead_shiptoaddress2,cohead_shiptoaddress3,cohead_shiptocity,cohead_shiptostate,cohead_shiptozipcode,cohead_shiptocountry,x.ls_number,cohead_cust_id,cust_id ORDER BY shiphead_number DESC;"
Set conn = CreateObject("ADODB.Connection")
conn.Open connectionparameters
Set recs = CreateObject("ADODB.Recordset")
recs.Open sqlcall, conn, 1, 3
MsgBox("It Worked!!! Now I just need to figure out how to get this to output the results to variables that I can call into label fields later")
Ok, so when I use the above in a VBScript and test it....the test works and I get the MsgBox test at the end of the script. However, I don't know where the output is stored, how to store it if I need to, and how to pull that output into the specific fields I want on my label. Any ideas or guidance to complete this would be greatly appreciated.
7 commentaires
0
Ian Cummings
Modérateur
Actions pour les commentaires
Why are you not using the built-in functionality of BarTender to connect to the database and use the queryprompt functionality to prompt the user at print time for the search criteria value? Note that BarTender's database capability does also support custom SQL statements that may include the queryprompt reference in them if you wish.
Take a look at these videos:
https://www.youtube.com/watch?v=WUBXhAnhrMU
Hi Ian, thanks for the reply. I tried using the BT built in DB connection in conjunction with the query prompt, but I was unable to set the query prompt filters because I am using a custom SQL statement. I thought this was unusual, so I called tech support and was told I cannot use the built in query prompt if I am using my custom SQL statement. Was I informed incorrectly? If so, how can I set the query prompt to search a specific column from my query? You can set it by going to the "Filters" tab, but I don't have the "Filters" tab when I use a custom SQL statement.
Yes, unfortunately you got incorrect information. To view the syntax used for such query prompt references, create a database connect, build a query using a named query prompt, and then switch to the custom SQL view. Example:
SELECT `CONTCOMP` FROM `CUSTOMER` WHERE `CONTCOMP` = '?QueryPrompt1'
Yep, that was incredibly easy compared to what I was getting into. Thanks a bunch for the insight. I simply added "WHERE shiphead_number = '?ShipmentNumber' AND shipitem_qty = '?Quantity'". These both allow me to prompt the operator for the shipment number and quantity prior to firing my query. Thanks again!
Glad that worked out for you. Trying to debug the VB script wouldn't have been fun for anyone... :)
FYI: BarTender 2016 R3 now offers native support for PostgreSQL databases: http://www.seagullscientific.com/support/service-releases-legacy-versions/2016/
Awesome! Thanks for the update. I am going to check out the new features today.
Vous devez vous connecter pour laisser un commentaire.
