SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Blank Fields
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sirmilt
Starting Member

USA
49 Posts

Posted - 03/10/2013 :  12:51:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 03/10/2013 :  12:58:57  Show Profile  Reply with Quote
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)
Go to Top of Page

sirmilt
Starting Member

USA
49 Posts

Posted - 03/10/2013 :  17:20:10  Show Profile  Reply with Quote
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
Go to Top of Page

Kamalaveni
Starting Member

1 Posts

Posted - 03/11/2013 :  00:11:40  Show Profile  Reply with Quote
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..
]
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/11/2013 :  00:32:53  Show Profile  Reply with Quote
Hi Kamalaveni,

--James Post
SELECT 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000