set a value in database

Label printing integration (NiceWatch middleware and ActiveX programming interface)

Moderators: Georges, milos, NiceLabel Support Team

Post Reply
thomasjo
Newcomer
Posts: 1
Joined: Tue May 15, 2007 1:28 am

set a value in database

Post by thomasjo » Tue May 15, 2007 1:33 am

Hello.

I sit and works with a form to a customer,he uses a mysql database and in the exists a field that is called printed out, he wants to that when the operator chooses a post and print so will the value in the field printed out be changed from no to yes or 0 to 1 some in that style

how, I fix the?


I use NiceLabel 5.0.1 (version 1446)

Rsas
NiceLabel
NiceLabel
Posts: 305
Joined: Tue Oct 24, 2006 3:20 pm
Location: Kranj, Slovenia
Contact:

Post by Rsas » Wed May 23, 2007 9:55 am

Hello!

If I understand you correctly, you would like to update your database after the label is printed.

In NiceLabel samples ("C:\Program Files\EuroPlus\NiceLabel 5\Samples\Labels\Access Update.lbl" ) you can find a sample label "Access Update.lbl". Open the label, and take a look to Data/Functions.

There is a VB Script Function which updates a single field in an existing MS Access database table with a value from NiceLabel.

You have MySQL database so you have to use MySQL command instead Access to update the database but the main procedure is the same.

Regards,
Sasa
NiceLabel Technical Support

sqlstew
Enthusiast
Posts: 8
Joined: Wed Oct 10, 2007 5:27 pm
Location: Visalia, CA

posting acknowledgement to sql server db

Post by sqlstew » Wed Oct 10, 2007 5:45 pm

I too am trying to interact with a db to set a flag to acknowledge the printing of a label.

My questions:

What's a good reference to the commands I can use with VB Script to perform SQL Server Database updates that will work with Nicelabel? Are there any samples or tutorials? and In what versions of nicelabel is this functionality included?

Thanks

User avatar
lpatty
NiceLabel
NiceLabel
Posts: 60
Joined: Mon Sep 11, 2006 4:15 pm
Location: Milwaukee, WI
Contact:

Post by lpatty » Fri Oct 12, 2007 11:55 pm

Here is the scripting used in NiceLabel to replace values in a record or to insert a new record. You will have to insert code for your MySQL database:

Function InsertAccessRecord(filename, tablename, fields(), values())
Dim conn, SQL, fieldsString, valuesString, f
Set conn = CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & filename & ";"
SQL = "INSERT INTO " & tablename & "("
For f = LBound(fields) to UBound(fields)
If fieldsString = "" Then
fieldsString = fields(f)
valuesString = "'" & values(f) & "'"
Else
fieldsString = fieldsString & ", " & fields(f)
valuesString = valuesString & ", " & "'" & values(f) & "'"
End If
Next
SQL = SQL & fieldsString & ") VALUES (" & valuesString & ");"
conn.Execute(SQL)
conn.Close
Set conn = Nothing
End Function

Function UpdateAccessRecord(filename, tablename, Updatefield, Updatevalue, filterfield, filtervalue)
Dim conn, SQL
Set conn = CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & filename & ";"
SQL = "UPDATE " & tablename & " SET " & Updatefield & " = '" & updatevalue & "' WHERE " & filterfield & " = '" & filtervalue & "';"
conn.Execute(SQL)
UpdateDBRecord = 1
conn.Close
Set conn = Nothing
End Function

sqlstew
Enthusiast
Posts: 8
Joined: Wed Oct 10, 2007 5:27 pm
Location: Visalia, CA

Problem solved...

Post by sqlstew » Tue Oct 23, 2007 7:05 pm

Thanks lpatty, that did the trick...

User avatar
Saso
NiceLabel
NiceLabel
Posts: 2973
Joined: Mon Sep 04, 2006 8:09 am
Contact:

Post by Saso » Fri Dec 07, 2007 5:11 pm

There is a possibility to improve the solution.

You can detect in which state the label is currently in. There are three label states: design, preview and print.

At the end you probably want to write data to the database only when you are printing labels.

See this sample script how to detect the label state:

Code: Select all

Dim CurrentState

CurrentState = Label.GetCurrentState
select case CurrentState
  case 0 Result = "Label design"
  case 1 Result = "Print"
  case 2 Result = "Print preview"
end select
Saso Fleiser
Senior Technical Product Specialist

User avatar
adamscottmartin
Addict
Addict
Posts: 130
Joined: Mon Apr 21, 2008 5:12 pm
Location: Kenosha, WI

Post by adamscottmartin » Wed Aug 27, 2008 11:56 pm

Now that InsertAccessRecord and UpdateAccessRecord are standard functions in the NiceLabel 5, could you possibly post examples of syntax/usage here or direct us where to find it in the help files?

Personally I am more concerned with UpdateAccessRecord, but for those looking for InsertAccessRecord here is how mine works (as with many other examples on this forum):

Code: Select all

Dim dbopen, rs, Value

Set dbopen=CreateObject("ADODB.Connection")
dbopen.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\TG3SBS\G_SHARED\Quality\DR - Discrepancy Reports\DR_ISSUE_FORM_SUBMISSIONS.mdb"

sqlString= "SELECT MAX(drnumber) AS last_counter_value FROM DR_ISSUE_SUBMISSIONS"
Set rs = dbopen.execute (sqlString)
Value = rs("last_counter_value")
DRNumber = Value + 1

dbopen.Close

PartNumber = UCase(PartNumber)

Dim field(9), val(9)
field(0) = "PARTNUMBER"          : val(0) = PartNumber
field(1) = "QTY"        	      : val(1) = Quantity
field(2) = "REASONCODE"          : val(2) = ReasonCode
field(3) = "ISSUEDESCRIPTION"    : val(3) = IssueDescription
field(4) = "ENTRYPERSON"         : val(4) = EntryPerson
field(5) = "SUBMITDATE"          : val(5) = Date
field(6) = "PROJECTNAME"         : val(6) = ProjectName
field(7) = "DRNUMBER"            : val(7) = DRNumber
field(8) = "WORKORDER"           : val(8) = WorkOrderNumber
field(9) = "LOCATION"            : val(9) = Location

InsertAccessRecord "\\TG3SBS\G_SHARED\Quality\DR - Discrepancy Reports\DR_ISSUE_FORM_SUBMISSIONS.mdb", "DR_ISSUE_SUBMISSIONS", field, val

UserReply = MsgBox("Thank you for reporting the discrepancy.  Your issue has been sent to the Quality department for review.  Please submit any defective materials and the printed DR Form to the appropriate location (if in doubt see Lyn, Charlie, or Jimmy).", vbOKOnly)
So....anyone with some insight/examples of UpdateAccessRecord?

Post Reply