CODEWITHSUNDEEP
vb.netsql-server-2008-r2
user867621
user867621

Reputation: 1197

How to convert an string to a Char(20) sql datatype

I am trying to insert a string into a char(20) column in my SQL Server database.

I set the string to be equal to a value and then add it to a parameter

thisstring = "1914"
cmd.Parameters.AddWithValue("@code", thisstring)

However every time I try it ends up looking like this in the database

picture

I need it to look like the 1913, 000000, 000001 ones.

When I try to pad the string with spaces 

thisstring= thisstring.PadLeft(20, " ")

or

thisstring = "     " & thisstring

I am getting

String or binary data would be truncated

even if the field wasn't 20 characters total

What am I doing wrong?

Edit*** here is the column in SQL Server

http://imgur.com/BbV6VQv

Upvotes: 0

Views: 1710

Answers (1)

Steve
Steve

Reputation: 216293

I am not absolutely sure, but I think the problem lies in the AddWithValue.
While convenient this method doesn't allow to specify the exact datatype to pass to the database engine and neither the size of the parameter. It pass always an nvarchar parameter for a C# UNICODE string.

I think you should try with the standard syntax used to build a parameter

Dim p = new SqlParameter("@code", SqlDbType.Char, 20)
p.Value = thisstring.PadLeft(20, " ")

See this very interesting article on MSDN

Upvotes: 1

Related Questions