Solved

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

Posted on 2010-11-17
7
1,639 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
  • 3
  • 3
7 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Many thanks, cyberkiwi
Your conversion of my Access VBA procedure to a SQL Server SQL function worked a treat.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now