December 01, 2020, 10:01:50 pm
News: IF YOU ARE A MEMBER FROM THE ORIGINAL SITE AND CAN'T LOGIN. POST IN THE FEEDBACK FORUM (AS GUEST) AND AN ADMIN WILL ASSIST YOU TO ACCESS THE SITE!
Pages: [1]   Go Down
Author Topic: Identify column datatype programmically from VB.NET  (Read 3539 times)
Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« on: October 19, 2007, 09:00:17 am »

I'm using SQL Server 2005 Express, VB.NET 2005 Express connecting to the database via ODBC DSN (Got it to work on the first shot happydance)

I know how to check if the column exists using a little SQL trickery:
Code: (sql)
if exists (select * from syscolumns where id = object_id(N'[dbo].[PLA]') and name = N'PLA')
     BEGIN
          // do what needs to be done
     END
    GO

However, I already know that the column exists (it has to for the software to run) what I need to know is if it is VARCHAR or NVARCHAR. If it is VARCHAR then I need to alter table alter column to change it to NVARCHAR otherwise I don't do anything.
« Last Edit: October 19, 2007, 09:03:32 am by Jason Reed » Logged

-- signature --

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #1 on: October 19, 2007, 09:44:23 am »

Ha I think I figured it out.

Apparently the column in the syscolumns that I should look for is xtype and the xtype value for VARCHAR is 167. So I do an if exists for the object_id, name, with xtype = 167 then I know that the column that I'm looking for is VARCHAR or not. If it is then I make it NVARCHAR if not I leave it alone
Logged

-- signature --

Pages: [1]   Go Up
Print
 
Jump to: