Solved Field Length Of Vchar() Can Be Changeable?

[Ashfaque]

I have a strange question.

I have shifted my BE from Access to SQL Server and as far it has no issue but strange thing I noticed. There is a text box on my form where the position of higher management like General Manager, Operations Manager etc recorded. This field data I have taken on my report and in OnLoad event of report I am just checking if the textbox data is "General Manager" then....so on.

Unfortunately, when I debug the code the value in text box is reading as "General Manager " and hence it is not matching with my condition to be true. I checked length of nchar in my server table it was 20.

How to sort this out? Bcz when I am trying to reduce the field length of server tbl, it says I need to drop the table and again create the table but this way I will lose my data in it.

Another thing, there will be different positions to store in that text box so it is not necessary the length of field should be fixed. Suppose if the data is “HR” so it will consider “HR “ which will then not satisfy the if condition.

I don’t know how to tackle this issue. Can anyone of you please guide me.

 

[Galaxiom]

Some trailing spaces have crept in. Access usually suppresses them in tables but SQL Server accepts them.
Trim them off with an update query on the server.

UPDATE yourtable
SET thecolumn = RTRIM(thecolumn)

You certainly don't want to be reducing the column width to fix something like that.

 

[Ashfaque]

Thanks Galaxiom,

Do you mean I have make query in Access itself and perform the action? Kindly elaborate the ans please...I am using first time SQL server as BE.

 

[Minty]

I suspect you have used the nchar rather than Varchar data type.
You have used both in your post so I am unsure which you have actually used in your database.

An nchar behaves like a fixed-width data type, whereas the Varchar will simply store the number of characters entered.

 

[Ashfaque]

I suspect you have used the nchar rather than Varchar data type.
You have used both in your post so I am unsure which you have actually used in your database.

An nchar behaves like a fixed-width data type, whereas the Varchar will simply store the number of characters entered.

nchar is used.... andI not able to change it to VarChar.....

I deleted the complete field and replaced with Varchar. Hopefully it will work.

 

[Galaxiom]

The n in nChar means it is internationally capable because it uses two bytes instead of one.

The v in vchar means variable number of characters as a single byte so no international support.

These is also nvchar which is double byte variable character and of course, just plain char whose meaning aught to be obvious.

If you don't specify the number of characters then they get the defaults which I think might be able to be set on each system.

BTW In SQL Server there are two different length functions for length. Len() and DataLength()

 

[Galaxiom]

Minty spotted it. The number of characters is fixed so the extra available spaces are blanks.

It might seem odd to use a fixed width character column and store all those blanks but there are advantages. They are supposed to perform better in joins. With 64 bit (8 byte) computers and Terabyte drives, it doesn't much matter whether the characters take up one or two bytes.

 

[Ashfaque]

Finally I replaced it with nText and it worked......

Thanks all of you......

 

[Minty]

These days I would always use an nVarChar(255) unless data storage is a massive problem.
IMHO, that matches an Access Short text field storage capability most accurately.

 

[Isaac]

I just always tend to use Varchar(rightsize), unless I want to extra-nice modernize my database for downstream unicode apps then i use nvarchar.

Never would i use something that added padding, it just seems to annoying but that's just me personal.

 

[Pat Hartman]

If you use combos for things like job titles, you don't have to worry about stray spaces. Either store the ID or the string but set the LimitToList property to yes to prevent random updates.