Update table column value when print a label

Issues related to label design (working with databases, data processing, RFID encoding etc.) and printing (from NiceLabel Express, NiceLabel Pro, NiceForm and NicePrint)

Moderators: Georges, milos, NiceLabel Support Team

Post Reply
ajaysamgir
Enthusiast
Posts: 10
Joined: Tue Jun 16, 2015 4:08 pm

Update table column value when print a label

Post by ajaysamgir » Mon Jun 22, 2015 5:32 pm

Hello,

I am trying to update database table when label is printed for that purpose i am trying to use "Execute SQL statement" in automation builder, so table is updated but its updates all records which is existing.

Following sql query i have use,

I

Code: Select all

update os_spec_label set print_date = now()


I am trying to update print_date(with time-stamp) but it also update my existing records.
So it is possible to add where <condition> or i need to write python script ? which is better option?

User avatar
jure
NiceLabel
NiceLabel
Posts: 186
Joined: Fri Apr 18, 2014 2:04 pm
Location: NiceLabel HQ
Contact:

Re: Update table column value when print a label

Post by jure » Mon Jun 22, 2015 6:06 pm

Hello!

This is of course possible. 8)
I would suggest to execute SQL statement like this for example:

Code: Select all

DECLARE @currDate DATETIME;
SET @currDate = GETDATE();

UPDATE os_spec_label SET print_date = @currDate WHERE ID = :myID;
As you can see, first I declare SQL variable as DATETIME, and then I set current date as a value. After that I used normal UPDATE statement with "WHERE" condition, so I can only update the record which is being printed. Variable ":myID" comes from the Automation, and this is why it is declared with colon at the start.

Best regards,
Jure
There is no place like 127.0.0.1

ajaysamgir
Enthusiast
Posts: 10
Joined: Tue Jun 16, 2015 4:08 pm

Re: Update table column value when print a label

Post by ajaysamgir » Tue Jun 23, 2015 7:42 am

Hello jure,

What is :myID ? its generates error when i am trying to run sql with it?
error : You have error in sql syntax near 'myID'
I am trying to print one or more label so how i provide id one by one to sql ?

User avatar
jure
NiceLabel
NiceLabel
Posts: 186
Joined: Fri Apr 18, 2014 2:04 pm
Location: NiceLabel HQ
Contact:

Re: Update table column value when print a label

Post by jure » Tue Jun 23, 2015 8:43 am

Hello,

In the provided example :myID is variable in the Automation, which is the same as the ID in the SQL table. So for example if I am printing a record from database which has ID 5, then I only want to update this record. You could also change this to any other "unique" field, for example WHERE myProduct = :myProduct, so the only important thing is to define a variable :*your_var_name* which you will somehow set in your Automation configuration, so that right record in the SQL will be updated.

There are more ways to set the value to this "filter" variable, but I would need to see your configuration, tell you which way would work best for you. If you need more help regarding this then please contact us directly to support@nicelabel.com, and send us your configuration files so we can inspect them and purpose the solution.

Best regards,
Jure
There is no place like 127.0.0.1

ajaysamgir
Enthusiast
Posts: 10
Joined: Tue Jun 16, 2015 4:08 pm

Re: Update table column value when print a label

Post by ajaysamgir » Tue Jun 23, 2015 10:26 am

Hello jure
Its working thank you :) .

user12
Valued Contributor
Posts: 22
Joined: Fri Jun 14, 2019 8:28 am

Re: Update table column value when print a label

Post by user12 » Mon Jul 01, 2019 9:30 am

Hello Jure,
Could you please help me regarding a similar sql statement:

select Driver from Table2
where Line =:Table1.Line

It should return 3 rows with Driver names. but retrieves 0 rows.
On checking by message action, :Table1.Line shows the correct data as 'A'.

on executing,

select Driver from Table2
where Line ='A' ,the rows are retrieved correctly.

but I need to do it automatically.

I tried contacting the support team. it would many thankful if anyone can save the situation?

User avatar
Kyleodonnell
NiceLabel Support
NiceLabel Support
Posts: 462
Joined: Mon Aug 28, 2017 3:07 pm

Re: Update table column value when print a label

Post by Kyleodonnell » Mon Jul 01, 2019 6:31 pm

User12,

Which email did you use for the support team? If you used the website we were having issues with the website. Did you email support.americas@nicelabel.com or support@nicelabel.com

user12
Valued Contributor
Posts: 22
Joined: Fri Jun 14, 2019 8:28 am

Re: Update table column value when print a label

Post by user12 » Mon Jul 08, 2019 10:55 am

Thank you for the information. I hope the problem with the site is now cleared. They contacted me later. Thankyou.

Post Reply