?
Solved

Is it possible to construct a full EAN 13 barcode string in a SQL SELECT query?

Posted on 2010-11-17
7
Medium Priority
?
1,997 Views
Last Modified: 2012-05-10
I am developing an Access "project" (.adp) as a front-end to a SQL Server database.

My client has his own EAN 13 barcode prefix and each stock item has its own unique number. I have been able to produce VBA code to pack put the stock number with leading zeroes, join it to the prefix and calculate the 13th checksum digit.

That all works OK for working within Access, but I am now using the SQL Server data as an ODBC source and would like to offer the full 13 digit barcode as a column from a new "View".

Can anyone suggest how I could create the full barcode in a SQL SELECT statement?

Many thanks. Colin.

0
Comment
Question by:colinasad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34158094
We could translate the VBA code to make sure it is doing the same things?
Need the VBA code though.
0
 
LVL 6

Expert Comment

by:tncode
ID: 34158201
You may want to try this VBA encoder; it contains the ability to encode EAN barcodes. Though you must also use a barcode font.

http://www.idautomation.com/fonts/tools/vba/

Hope this helps.
0
 

Author Comment

by:colinasad
ID: 34158365
Hello again, cyberkiwi. Thanks for the prompt response.

I have attached my VBA procedure for building a full 13 character barcode string.
I wouldn't know how to start to do this with SQL. I would be able to construct the first 12 digits OK but wouldn't know how to do the checksum calculation.

Regards. Colin.
Public Function genBuildFullBarcode(strPrefix As String, lngNumber As Long, blnSeparate As Boolean) As String
    ' Build a full 13 digit barcode from a prefix and unique number - checksum digit is calculated
    
    ' strPrefix = Barcode prefix digits
    ' lngNumber = Item's unique number
    ' blnSeparate = Do we want to show gaps between main components ie ppppppp-nnnnn-c
    
    Dim intEvenTot As Integer
    Dim intOddTot As Integer
    Dim intCounter As Integer
    Dim strBarcode As String
    
    intEvenTot = 0
    intOddTot = 0
    strBarcode = ""
    
    ' Pack out the unique number with leading zeroes and make it 5 digits long
    strBarcode = Trim(Str(lngNumber))
    strBarcode = Right("00000" & strBarcode, 5)
    
    ' Concatenate the unique number to the prefix (already 7 digits long)
    strBarcode = strPrefix & strBarcode
    
    ' Now calculate the 13th checksum digit
    If Len(strBarcode) = 12 Then
        For intCounter = 1 To 12
            If (intCounter Mod 2) = 0 Then
                intEvenTot = intEvenTot + Asc(Mid(strBarcode, intCounter, 1)) - Asc("0")
            Else
                intOddTot = intOddTot + Asc(Mid(strBarcode, intCounter, 1)) - Asc("0")
            End If
        Next intCounter
                                        
        intEvenTot = (intEvenTot * 3) + intOddTot
        intEvenTot = (intEvenTot Mod 10)
        If intEvenTot > 0 Then
            intEvenTot = 10 - intEvenTot
        End If
        
        
        If blnSeparate Then
            ' Add a separator char to show how string is composed
            strBarcode = Trim(Str(lngNumber))
            strBarcode = Right("00000" & strBarcode, 5)
            strBarcode = strPrefix & "-" & strBarcode & "-" & Trim(Str(intEvenTot))
        Else
            strBarcode = strBarcode & Trim(Str(intEvenTot))
        End If
    End If
    
    genBuildFullBarcode = strBarcode
End Function ' genBuildFullBarcode

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34158564
Create the following function on SQL Server.
You can use this function in an expression, e.g.

select col1, col2, dbo.genBuildFullBarcode('MYPREFX', barcodenum, 1) as ean13barcode
from sometable
create Function dbo.genBuildFullBarcode
(@strPrefix varchar(50), @lngNumber int, @blnSeparate bit) returns varchar(50)
as
begin
    -- Build a full 13 digit barcode from a prefix and unique number - checksum digit is calculated
    
    -- strPrefix = Barcode prefix digits
    -- lngNumber = Item's unique number
    -- blnSeparate = Do we want to show gaps between main components ie ppppppp-nnnnn-c
    
    declare @intEvenTot int
    declare @intOddTot int
    declare @intCounter int
    declare @strBarcode varchar(50)
    
    set @intEvenTot = 0
    set @intOddTot = 0
    set @strBarcode = ''
    
    -- Pack out the unique number with leading zeroes and make it 5 digits long
    set @strBarcode = right('00000' + convert(varchar(50), @lngNumber), 5)
    
    -- Concatenate the unique number to the prefix (make sure it is 7 digits long, left pad with 0)
    set @strPrefix = right('0000000' + @strPrefix, 7)
    set @strBarcode = @strPrefix + @strBarcode
    
    -- Now calculate the 13th checksum digit
    If Len(@strBarcode) = 12
    begin
		set @intCounter = 1
		while @intCounter <= 12
		begin
            If (@intCounter % 2) = 0
                set @intEvenTot = @intEvenTot + ascii(substring(@strBarcode, @intCounter, 1)) - ascii('0')
            Else
                set @intOddTot = @intOddTot + ascii(substring(@strBarcode, @intCounter, 1)) - ascii('0')
            set @intCounter = @intCounter + 1
        end
                                        
        set @intEvenTot = (@intEvenTot * 3) + @intOddTot
        set @intEvenTot = (@intEvenTot % 10)
        if @intEvenTot > 0
            set @intEvenTot = 10 - @intEvenTot
        
        -- add checksum
        set @strBarcode = @strBarcode + convert(varchar(10),@intEvenTot)

        if @blnSeparate = 1
            -- Add a separator char to show how string is composed
            set @strBarcode = left(@strBarcode,7) + '-' + substring(@strBarcode, 8, 5) + '-' + right(@strBarcode, 1)
    end
    
    return @strBarcode
end
GO

Open in new window

0
 

Author Comment

by:colinasad
ID: 34162513
Many thanks, cyberkiwi, that worked fine.
I'm ashamed to say that I have not created any SQL Functions before. Because my application is in Access, nearly all of my programming is done in VBA and I have limited by SQL input to Views and Stored Procedures.
You have opened a new door for me.

Just before I award the well-earned points, can I ask if you painstakingly edited my VBA code by hand (replacing the comment prefix characters, changing "dim" to "declare", adding in "set" etc.) or is there a "VBA to SQL" convertor that does most of this?

Many thanks. Colin.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34168367
By hand, sir! :)
It was a hack job line for line.
If you expressed just the requirements, you may see a different approach, but I think this will do.

Have a good day.
Regards
0
 

Author Closing Comment

by:colinasad
ID: 34171580
Many thanks, cyberkiwi
Your conversion of my Access VBA procedure to a SQL Server SQL function worked a treat.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question