June 22, 2005 at 11:59 am
Hello,
Have you ever needed to create a Barcode but all you could find are
articles that use graphics or some font package?
Well Here is a script that will allow you to use SQL Server and HTML
to create the BarCode. You will not need anything special other then
the ability to render the HTML.
--------------------------------------------------
SQL CODE
<code>
--
-- Create the Function first
--
CREATE FUNCTION F_TranslateToCode39(@StringToTranslate CHAR(1))
/****************************************************************
This function will be called by F_GenerateBarCode to generate the
HTML string needed to represent the Character.
****************************************************************/
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE
@ReturnString VARCHAR(2000)
,@RawCode VARCHAR(12)
,@Counter INT
--
-- First get the Raw representation of the character
-- this will tell us what color the lines need to be
-- for more references on this please refer to the following
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- http://www.barcodeisland.com/code39.phtml#Example
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SET @RawCode = (CASE UPPER(@StringToTranslate)
WHEN '0' THEN '101001101101'
WHEN '1' THEN '110100101011'
WHEN '2' THEN '101100101011'
WHEN '3' THEN '110110010101'
WHEN '4' THEN '101001101011'
WHEN '5' THEN '110100110101'
WHEN '6' THEN '101100110101'
WHEN '7' THEN '101001011011'
WHEN '8' THEN '110100101101'
WHEN '9' THEN '101100101101'
WHEN 'A' THEN '110101001011'
WHEN 'B' THEN '101101001011'
WHEN 'C' THEN '110110100101'
WHEN 'D' THEN '101011001011'
WHEN 'E' THEN '110101100101'
WHEN 'F' THEN '101101100101'
WHEN 'G' THEN '101010011011'
WHEN 'H' THEN '110101001101'
WHEN 'I' THEN '101101001101'
WHEN 'J' THEN '101011001101'
WHEN 'K' THEN '110101010011'
WHEN 'L' THEN '101101010011'
WHEN 'M' THEN '110110101001'
WHEN 'N' THEN '101011010011'
WHEN 'O' THEN '110101101001'
WHEN 'P' THEN '101101101001'
WHEN 'Q' THEN '101010110011'
WHEN 'R' THEN '110101011001'
WHEN 'S' THEN '101101011001'
WHEN 'T' THEN '101011011001'
WHEN 'U' THEN '110010101011'
WHEN 'V' THEN '100110101011'
WHEN 'W' THEN '110011010101'
WHEN 'X' THEN '100101101011'
WHEN 'Y' THEN '110010110101'
WHEN 'Z' THEN '100110110101'
WHEN ' ' THEN '100110101101'
WHEN '*' THEN '100101101101'
WHEN '%' THEN '101001001001'
WHEN '-' THEN '100101011011'
ELSE '' END)
--
-- Now that we have the Raw data check it
--
SELECT @Counter = 0
,@ReturnString = ''
--
-- Test each character for color
--
WHILE @Counter < LEN(@RawCode)+1 BEGIN
--
--
--
IF (SUBSTRING(@RawCode,@Counter,1) = '0') BEGIN
SELECT @ReturnString = @ReturnString + '<TD style="background-color:White;height:7mm;width:0.3mm;"></TD>'
GOTO COUNT_IT
END
IF (SUBSTRING(@RawCode,@Counter,1) = '1') BEGIN
SELECT @ReturnString = @ReturnString + '<TD style="background-color:Black;height:7mm;width:0.3mm;"></TD>'
GOTO COUNT_IT
END
--
--
COUNT_IT:
SET @Counter = @Counter + 1
--
--
END
--
-- Add Character Seperator REQUIRED!
--
SELECT @ReturnString = @ReturnString + '<TD style="background-color:White;height:7mm;width:0.4mm;"></TD>'
--
RETURN @ReturnString
END
GO
--
-- Now for the procedure that makes it all happen
--
CREATE PROCEDURE spGenerateHTMLBarCodeString
@StringToEncode VARCHAR(50)
,@ResultMessage VARCHAR(200) = '' OUTPUT
AS
DECLARE
@ReturnCode INT
,@Counter INT
,@CompleteString VARCHAR(60)
--
DECLARE @BarcodeTable TABLE
(BarcodeHTML VARCHAR(2000) NOT NULL DEFAULT '')
--
SET NOCOUNT ON
--
SELECT @CompleteString = '*' + LTRIM(RTRIM(@StringToEncode)) + '*'
,@Counter = 1
--
INSERT INTO @BarcodeTable
(BarcodeHTML)
VALUES
('<TABLE CELLPADDING="0" CELLSPACING="0"><TR>')
--
-- trim the string
SET @CompleteString = LTRIM(RTRIM(@CompleteString))
--
WHILE @Counter < LEN(@CompleteString)+1 BEGIN
--
--
INSERT INTO @BarcodeTable
(BarcodeHTML)
SELECT dbo.F_TranslateToCode39(SUBSTRING(@CompleteString,@Counter,1))
--
--
SET @Counter = @Counter + 1
--
--
END
--
INSERT INTO @BarcodeTable
(BarcodeHTML)
VALUES
('</TR></TABLE>')
--
--
SELECT BarcodeHTML FROM @BarcodeTable
IF (@@ERROR != 0) BEGIN
SET @ReturnCode = 1
GOTO END_PROCEDURE
END
SET @ReturnCode = 0
END_PROCEDURE:
SET @ResultMessage = (CASE @ReturnCode
WHEN 0 THEN 'OK'
WHEN 1 THEN 'An error occured generating the Barcode you requested' +
'.Please contact Information Systems (1).'
ELSE
'An unexpected error has occured, Error Number ' +
CAST(@ReturnCode AS VARCHAR(5)) + '.'
END)
RETURN @ReturnCode
GO
</code>
--------------------------------------------------
Now here is the C# Code for the Web Form
You will need a Textbox, Button, Table and a
connection to the Database where you have created
the above procedure.
--------------------------------------------------
private void Button1_Click(object sender, System.EventArgs e)
{
TableRow ANewRow = new TableRow();
TableCell BarcodeCell = new TableCell();
//
Label BarCodeLabel = new Label();
//
BarCodeLabel.Text = this.GenerateBarCode(this.txtboxStringToConvert.Text);
BarcodeCell.Controls.Add(BarCodeLabel);
//
ANewRow.Cells.Add(BarcodeCell);
this.tblBarcodeTable.Rows.Add(ANewRow);
}
private string GenerateBarCode(string StringToTest)
{
string returnValue = "";
this.spGenerateHTMLBarCodeString.Parameters["@StringToEncode"].Value = StringToTest;
SqlDataReader BarCodeReader = this.spGenerateHTMLBarCodeString.ExecuteReader();
if (BarCodeReader.HasRows)
{
while (BarCodeReader.Read())
{
returnValue += BarCodeReader["BarcodeHTML"].ToString();
}
}
BarCodeReader.Close();
BarCodeReader = null;
return returnValue;
}
--------------------------------------------------
Well that's it. Now you should be able to generate Barcodes.
Please let me know if you have found this interesting or have
any changes to add that would make it better. I would be happy
to answer any questions you have.
Thanks
William O'Malley
June 22, 2005 at 3:02 pm
Thanks for this code. This works good. We can even enhance by passing height and width to stored procedure.
But when we work with bar codes there are specific needs like Code 39, Code 93, UPC -A and a lot more specfic fonts so that they are machine readable. If we create our own barcode we have to create our own barcode readers. Or is this redable by any barcode readers?
Can you please explain how to use this.
Regards,
gova
June 22, 2005 at 3:22 pm
this code is usable by ANY barcode reader that has the ability to read Code39 bar codes.
I wrote this to be used with a WASP pen bar code reader but any should work.
give it a try.
June 23, 2005 at 5:36 am
EDIT:
Thank you for the comments:
This code was tested using the Following Barcode Reader
http://www.waspbarcode.com/scanners/wandreader_barcode_scanner.asp
-- End Edit
Any one else have any more comments?
Thanks
Will
June 23, 2005 at 2:27 pm
i threw this into a web page, looks great. but won't print -- doesn't show up?
the browser appears set not* to render table background colors.
what you see, is not what you get.
(updated)
oops, found this...
Internet Explorer
IE typically is not set to print background color or images, including table cell colors. On IE’s Print Preview, our color charts will show as white table cells with black text. To get the table cells colored backgrounds to print, click on the browser’s Tools menu. Select Internet Options, then click on the Advanced tab. Scroll down to find the Printing heading and check the box called Print Background Colors and Images. This setting affects both page backgrounds and table cell backgrounds
FYI, doesn't render well in MS Word either -- for those who use that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply