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

Question

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

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

what is the SQL or stored procedure call with cmd, i.e. cmd.CommandText? — shahkalpesh
– shahkalpesh, Commented Jun 21, 2013 at 19:10
thisstring= thisstring.PadLeft(20, " ") should have worked. Can you double check the parameter definition. This could be different than the table definition. — Bill Gregg
– Bill Gregg, Commented Jun 21, 2013 at 19:10
cmd = New SqlCommand(strSQL, conn) and the execute is cmd.ExecuteNonQuery() — user867621
– user867621, Commented Jun 21, 2013 at 19:12

Steve · Accepted Answer · 2013-06-21 19:24:32Z

1

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

answered Jun 21, 2013 at 19:24

Steve

217k22 gold badges242 silver badges296 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related