How to convert int into Byte Array?

Question

I have a table in which there is one column named:

'eZip' (varbinary(5), null).

Now I am adding value from a web form like this:

 cmd.Parameters.Add("@eZip", SqlDbType.VarBinary).Value = BitConverter.GetBytes(int.Parse(txtZip.Text.ToString()));

Its working but instead of putting a valid zip code, it is inserting this into my column:

<Binary data>

What I am doing wrong here?

What makes you say that it's inserting that into your column? — Adam Robinson
– Adam Robinson, Commented Apr 21, 2011 at 16:44
If you're storing zip codes in a database, make the column char or varchar. Converting a zip code to an int is a bad idea in the first place, and then converting the int to binary is another bad idea. — MusiGenesis
– MusiGenesis, Commented Apr 21, 2011 at 16:50
@Musi: so what should be the best Datatype for Zip in MSSQL 2008? — RG-3
– RG-3, Commented Apr 21, 2011 at 17:00
@Mayank: The best type for a zipcode in any RDBMS is a string type, either char or varchar. If you're only going to be handling US zip codes, char(5) (or char(10) if you want to handle the hyphen+4) would be the best choice. — Adam Robinson
– Adam Robinson, Commented Apr 21, 2011 at 17:06

Mike Marshall · Accepted Answer · 2011-04-21 18:06:11Z

3

I don't think you are doing anything wrong per-se. if you define the field as varbinary you will always see "Binary Data" in SQL Server's management tools, regardless of the data.

Are you sure you don't want just CHAR(n) OR VARCHAR(n) for the zip code?

edited Apr 21, 2011 at 18:06

answered Apr 21, 2011 at 16:46

Mike Marshall

7,8805 gold badges46 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Adam Robinson Over a year ago

Zip codes are not integers, they're alphanumeric data that (in the US) only consist either of five numeric digits or nine numeric digits and a hyphen.

RG-3 Over a year ago

so what should be the best Datatype for Zip in MSSQL 2008?

Mike Marshall Over a year ago

@Adam You are correct, I will edit to specify Char or Varchar

Mike Marshall Over a year ago

@Mayank The most comprehensive zip code format in the US is the "Zip + 4" format, which is nnnnn-xxxx format. So VARCHAR(10) would probably suffice, because it can hold the 5-digit or 9-digit versions.

Kimberly · Accepted Answer · 2011-04-21 17:20:40Z

1

If you can't change the data type of that column, all you need to do is convert the value when you get the data back out. As recommended by others, you should at least change your code to assume that the five bytes are characters, not integers.

Insert the value:

 cmd.Parameters.Add("@eZip", SqlDbType.VarBinary).Value = System.Text.Encoding.ASCII.GetBytes(txtZip.Text.ToString());

Retrieve the value in SSMS tools:

SELECT CONVERT(VARCHAR(5), [eZip]) AS [eZip] FROM @zip_table;

Retrieve the value from a DataRow dr in C#:

System.Text.Encoding.ASCII.GetString(dr["eZip"]);

If you can change the column to a CHAR(5) or VARCHAR(5), then no conversion will be necessary.

edited Apr 21, 2011 at 17:20

answered Apr 21, 2011 at 17:06

Kimberly

2,7122 gold badges17 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to convert int into Byte Array?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related