assign sql data to variables

Label printing integration (NiceWatch middleware and ActiveX programming interface)

Moderators: Georges, milos, NiceLabel Support Team

Post Reply
pherms
Enthusiast
Posts: 7
Joined: Thu Oct 10, 2019 4:23 pm

assign sql data to variables

Post by pherms » Tue Nov 26, 2019 4:38 pm

Hi,

I am trying to optimize the Nicelabel solution we have built last year.
At the moment we run a bunch of select queries, which select only one database field and we put the result in a variable.

What I want to do to optimize this is run 1 query where I select the fields I need, and assign the fields to a variable and go from there.
My best guess was to write a script which would accomplish this. This is my script:

Code: Select all

' Connection String required to connect to MS Access database
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=U:\My Documents\db1.mdb;"
' SQL statement to run
sql = "select * from Adressenlijst where id='1'"

' Create ADO Connection/Command objects
set cn = createobject("ADODB.Connection")
set cmd = createobject("ADODB.Command")
set rs=Createobject("ADODB.recordset")

' Open connection
cn.open connectionString

'3 is adOpenStatic
rs.open sql,cn,3

if NOT rs.EOF then
	
	[strachternaam]=rs("Achternaam")
	[strvoornaam]=rs("Voornaam")
	[strfunctie]=rs("Functie")
	[strorganisatie]=rs("Organisatie")

end if
This script would use ADODB to query the database and assign the values to the variables. I got this working, but I came accross this forum topic: viewtopic.php?f=10&t=8881&p=34331&hilit ... ery#p34331.

In this topic Saso from the nicelabel team talks about using the Structured text filter and use that method.
Now I am wondering what the best solution would be. Can I keep using the ADODB method to retrieve the data or is it better to use the structured text filter?
Where can I find this structured text filter in Nicelabel 2017?
At the moment I am pulling data from a Microsoft Access database, but I am planning to migrate this to MSSQL server.

Thank you in advance.

pherms
Enthusiast
Posts: 7
Joined: Thu Oct 10, 2019 4:23 pm

Re: assign sql data to variables

Post by pherms » Wed Nov 27, 2019 1:57 pm

I have found a solution to my problem.
When you use the action "Execute SQL Statement" you can choose the option to "Iterate for every record".
You have to create variables with the same name as the fields name in the database.

Then you can either connect your label fields to these newly created variables or you can add additional "Set variable" actions to set the variables you may already have, to these new variables.

User avatar
Mytch
NiceLabel Support
NiceLabel Support
Posts: 103
Joined: Fri Jul 13, 2018 10:26 pm
Location: Milwaukee, Wisconsin
Contact:

Re: assign sql data to variables

Post by Mytch » Wed Nov 27, 2019 4:15 pm

pherms wrote:
Wed Nov 27, 2019 1:57 pm
I have found a solution to my problem.
When you use the action "Execute SQL Statement" you can choose the option to "Iterate for every record".
You have to create variables with the same name as the fields name in the database.

Then you can either connect your label fields to these newly created variables or you can add additional "Set variable" actions to set the variables you may already have, to these new variables.
That's exactly how it's done. \:D/ Thanks for posting your solution!

Post Reply