February 11, 2011 at 5:32 am
I'm looking for a barcode generating Function (in SQL 2005), that uses the standard Code128.
The result of calling the function will then be mailmerged in Word in a document with font Code128.TTF (this shows the barcode as such).
Now, the problem is that I have the correct function in Access (code attached in TXT) but not in SQL Server.
Does anyone have the equivalent SQL Server function (or knows how to translate this Access into SS) ?
Thanks in advance, a.
February 11, 2011 at 6:12 am
a quick google lead me to this solution posted from Dalton from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70901
declare @myString varchar(255)
select @myString = 'BarCode 1'
-- Define the string of characters that we'll need to pull the reference of
declare @asciiString varchar(255)
select @asciiString = ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~'
select @asciiString = @asciiString + char(195) -- 0xC3
select @asciiString = @asciiString + char(196) -- 0xC4
select @asciiString = @asciiString + char(197) -- 0xC5
select @asciiString = @asciiString + char(198) -- 0xC6
select @asciiString = @asciiString + char(199) -- 0xC7
select @asciiString = @asciiString + char(200) -- 0xC8
select @asciiString = @asciiString + char(201) -- 0xC9
select @asciiString = @asciiString + char(202) -- 0xCA
-- Define the stop and start characters
declare @stopchar char(1)
declare @startchar char(1)
declare @spacechar char(1)
select @stopchar = char(206) -- 0xCE
select @startchar = char(204) -- 0xCC
select @spacechar = char(194) -- 0xC2
-- Define the final holding place of our output string
declare @finalArray varchar(255)
-- Define the variables that we'll need to be using
declare @checksumTotal int
declare @checksum int
select @checksumTotal = 104;
select @checksum = 0;
-- Start building our output
select @finalArray = @startchar
-- Loop through our input variable and start pulling out stuff
declare @position int
declare @thisChar char(1)
select @position = 1
while @position <= len(@myString)
begin
select @thisChar = substring(@myString, @position, 1)
select @checksumTotal = @checksumTotal + (@position * (ascii(@thischar)-32))
select @finalArray = @finalArray + @thisChar
select @position = @position + 1
end -- We've gone past the length now
-- Now we need to figure out and add the checksum character
select @checksum = @checksumTotal % 103
if @checksum = 0
select @finalArray = @finalArray + @spacechar
else
-- Barcorde array assumes 0 as initial offset so we need to add 1 to checksum
select @finalArray = @finalArray + substring(@asciiString, @checksum+1, 1)
-- Now we append the stop character
select @finalArray = @finalArray + @stopchar
-- The @final Array represents the barcode encoded string
select @finalArray
Lowell
February 11, 2011 at 7:29 am
Thanks Lowell, but it doesn't seem to be the same standard I'm referring to.
In standard Code128, if you code 'bananas', you'll get ÑbananasÈÓ (it always starts with capital Ñ and ends with Ó).
Pass 'bananas' to this function and you'll get 'š' and a weird character in the end... they must be pretty similar standards, but not equal.
July 26, 2013 at 12:56 am
anyone know a way that can create these codes without cost?
July 30, 2013 at 6:32 am
Maybe try this?
July 30, 2013 at 6:57 am
take a look at teh script mister magoo built in this similar barcode 128b thread:
http://www.sqlservercentral.com/Forums/Topic1470917-392-1.aspx
with that and a bar code font, i think you would have everything you need
Lowell
June 26, 2014 at 9:19 pm
a_ud (2/11/2011)
I'm looking for a barcode generating Function (in SQL 2005), that uses the standard Code128.The result of calling the function will then be mailmerged in Word in a document with font
Code128.TTF (this shows the barcode as such).Now, the problem is that I have the correct function in Access (code attached in TXT) but not in SQL Server.
Does anyone have the equivalent SQL Server function (or knows how to translate this Access into SS) ?
Thanks in advance, a.
I have seen few threads on this issue, like http://www.sqlservercentral.com/Forums/Topic1065554-149-1.aspx, but does anyone have successfully solved this question with own experence?
December 4, 2014 at 9:39 am
Hi all,
I hope someone can help. I´m trying to resolve this but no luck. ALl the functions I got from the internet don´t make the CODE128 as requested by the client.
the only website that does for code 128:
http://www.barcode-generator.org/
Example:
for the code entered:1304083202
He gets back using the VB code below:Ò-$(@"rÓ
with the TSQL function I´ve got I get back: Í1304083202sÎ
Completly different. If some1 can help It would be great.
This is the code he wants to translate to T-SQL
Public Function code128$(chaine$)
'This function is governed by the GNU Lesser General Public License (GNU LGPL)
'V 2.0.0
'Parameters : a string
'Return : * a string which give the bar code when it is dispayed with CODE128.TTF font
' * an empty string if the supplied parameter is no good
Dim i%, checksum&, mini%, dummy%, tableB As Boolean
code128$ = ""
If Len(chaine$) > 0 Then
'Check for valid characters
For i% = 1 To Len(chaine$)
Select Case Asc(Mid$(chaine$, i%, 1))
Case 32 To 126, 203
Case Else
i% = 0
Exit For
End Select
Next
'Calculation of the code string with optimized use of tables B and C
code128$ = ""
tableB = True
If i% > 0 Then
i% = 1 'i% become the string index
Do While i% <= Len(chaine$)
If tableB Then
'See if interesting to switch to table C
'yes for 4 digits at start or end, else if 6 digits
mini% = IIf(i% = 1 Or i% + 3 = Len(chaine$), 4, 6)
GoSub testnum
If mini% < 0 Then 'Choice of table C
If i% = 1 Then 'Starting with table C
code128$ = Chr$(210)
Else 'Switch to table C
code128$ = code128$ & Chr$(204)
End If
tableB = False
Else
If i% = 1 Then code128$ = Chr$(209) 'Starting with table B
End If
End If
If Not tableB Then
'We are on table C, try to process 2 digits
mini% = 2
GoSub testnum
If mini% < 0 Then 'OK for 2 digits, process it
dummy% = Val(Mid$(chaine$, i%, 2))
dummy% = IIf(dummy% < 95, dummy% + 32, dummy% + 105)
code128$ = code128$ & Chr$(dummy%)
i% = i% + 2
Else 'We haven't 2 digits, switch to table B
code128$ = code128$ & Chr$(205)
tableB = True
End If
End If
If tableB Then
'Process 1 digit with table B
code128$ = code128$ & Mid$(chaine$, i%, 1)
i% = i% + 1
End If
Loop
'Calculation of the checksum
For i% = 1 To Len(code128$)
dummy% = Asc(Mid$(code128$, i%, 1))
dummy% = IIf(dummy% < 127, dummy% - 32, dummy% - 105)
If i% = 1 Then checksum& = dummy%
checksum& = (checksum& + (i% - 1) * dummy%) Mod 103
Next
'Calculation of the checksum ASCII code
checksum& = IIf(checksum& < 95, checksum& + 32, checksum& + 105)
'Add the checksum and the STOP
code128$ = code128$ & Chr$(checksum&) & Chr$(211)
End If
End If
Exit Function
testnum:
'if the mini% characters from i% are numeric, then mini%=0
mini% = mini% - 1
If i% + mini% <= Len(chaine$) Then
Do While mini% >= 0
If Asc(Mid$(chaine$, i% + mini%, 1)) < 48 Or Asc(Mid$(chaine$, i% + mini%, 1)) > 57 Then Exit Do
mini% = mini% - 1
Loop
End If
Return
End Function
February 5, 2015 at 7:45 pm
Generating barcode Code-128 in C# Class example (C# Code 128 Generator Complete Demo Source Code):
Linear code128 = new Linear();
code128.Type = BarcodeType.CODE128;
code128.Data = "0123456789";
code128.X = 1;
code128.Y = 60;
// Generate Code-128 and encode barcode to gif format
code128.Format = System.Drawing.Imaging.ImageFormat.Gif;
code128.drawBarcode("C://csharp-code128.gif");
// Print Code 128 C# Bitmap object
Bitmap code128Bitmap = code128.drawBarcode();
Generating barcode Code-128 in VB.NET Class example (VB.NET Code 128 Generator Complete Demo Source Code):
Dim code128 As OnBarcode.Barcode.Linear
code128 = New OnBarcode.Barcode.Linear()
code128.Type = OnBarcode.Barcode.BarcodeType.CODE128
code128.Data = "0123456789"
code128.X = 1
code128.Y = 60
' Generate Code-128 and encode barcode to png format
code128.Format = System.Drawing.Imaging.ImageFormat.Png
code128.drawBarcode("c:/vbnet-code128.png")
' Print Code-128 to vb.net Bitmap object
Dim code128Bitmap As Bitmap
code128Bitmap = code128.drawBarcode()
Hope it will help you.
February 6, 2015 at 1:10 am
micagordon20 (2/5/2015)
Generating barcode Code-128 in C# Class example (C# Code 128 Generator Complete Demo Source Code):
Linear code128 = new Linear();
code128.Type = BarcodeType.CODE128;
code128.Data = "0123456789";
code128.X = 1;
code128.Y = 60;
// Generate Code-128 and encode barcode to gif format
code128.Format = System.Drawing.Imaging.ImageFormat.Gif;
code128.drawBarcode("C://csharp-code128.gif");
// Print Code 128 C# Bitmap object
Bitmap code128Bitmap = code128.drawBarcode();
Generating barcode Code-128 in VB.NET Class example (VB.NET Code 128 Generator Complete Demo Source Code):
Dim code128 As OnBarcode.Barcode.Linear
code128 = New OnBarcode.Barcode.Linear()
code128.Type = OnBarcode.Barcode.BarcodeType.CODE128
code128.Data = "0123456789"
code128.X = 1
code128.Y = 60
' Generate Code-128 and encode barcode to png format
code128.Format = System.Drawing.Imaging.ImageFormat.Png
code128.drawBarcode("c:/vbnet-code128.png")
' Print Code-128 to vb.net Bitmap object
Dim code128Bitmap As Bitmap
code128Bitmap = code128.drawBarcode()
Hope it will help you.
thanks for your help. Finally I found a sql function (client was requesting that) and replacing some start/ending chars, and some other stuff made it work. thanks for your code anyway!
🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply