Page 1 of 1

assign sql data to variables

Posted: Tue Nov 26, 2019 4:38 pm
by pherms
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.

Re: assign sql data to variables

Posted: Wed Nov 27, 2019 1:57 pm
by pherms
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.

Re: assign sql data to variables

Posted: Wed Nov 27, 2019 4:15 pm
by Mytch
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!