Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sirmilt
Starting Member
49 Posts |
Posted - 2013-03-10 : 12:51:44
|
I am working on a Visual Basic project that utilizes three SQL Server tables. Each of the tables has an optional field for the year. The field designation in all 3 tables is NVarChar(4).In 2 of the tables when the field is left blank and the record saved to the database the result is a blank field with a length of 0 in the table, which when tested in Visual Basic results in a field length of 0, exactly what I want. The third table, however, contains 4 spaces and a text length of 4 in Visual Basic.The "Insert" code for all three tables is identical and I can't find any differences in the Visual Basic code which would account for this.Can anyone help?Milt |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-10 : 12:58:57
|
I know you said all the columns are NVARCHAR(4), but check again - you can use the following code to check:SELECT DATA_TYPE FROM INFORMATION_SCHEMA.[COLUMNS] WHERE TABLE_NAME = 'thirdtablename' AND COLUMN_NAME = 'Yearcolumname' If it returns NCHAR, that is the issue; the data type needs to be NVARCHAR(4), not NCHAR(4) |
|
|
sirmilt
Starting Member
49 Posts |
Posted - 2013-03-10 : 17:20:10
|
Thank You,James for the suggestion,I solved it by deleting he field in SQL Server and then reentering it. When I first layed out the database the field was NChar. Apparently when I changed it to NVarChar it didn't take, even tho the visible signs indicated that it did. I deleted the field, reentered with the new property and refreshed the database and it works fine now.Milt |
|
|
Kamalaveni
Starting Member
1 Post |
Posted - 2013-03-11 : 00:11:40
|
Hi James,i have read the reply which u have given for the question which is asked by sirmilt.if ur motto is to give code to check the datatype of the column means u can give sp_help Table_name.. right..?why u have given the code to retrieve from information_schema, is der any specific reason for dat..?i am new to sql, please clarify my doubt.. ] |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-11 : 00:32:53
|
Hi Kamalaveni,--James PostSELECT DATA_TYPE FROM INFORMATION_SCHEMA.[COLUMNS] WHERE TABLE_NAME = 'thirdtablename' AND COLUMN_NAME = 'Yearcolumname'Here you can get only that specific column related datatype whereas sp_help will give you all the properties of each column in that table.. By seeing that again we have to search for specific column.. right?--Chandu |
|
|
|
|
|
|
|