check if a table column has identity turned on with vb.net

from http://weblogs.asp.net/psteele/archive/2003/12/03/41051.aspx

select o.name + '.' + c.name
from syscolumns c, sysobjects o
where c.status & 128 = 128
and o.id = c.id
order by o.name

Hello,
Following query will return you list of identity columns for the selected table.

dim strQuery as string = "select c.name from syscolumns c, sysobjects o where o.id = c.id and c.autoval is not null and o.name='mytable' "

Just execute this query and see the result.

Please note this will return multiple columns if you have a composite key. That's the only catch.
Enjoy Coding!

hey there, thanks for the prompt answers. i do not need to get that answer from an sql query. i need to have a windows form application in vb.net.

when i click a button i need to run the query you wrote above and get in a textbox the name of the column that has a identity key.

you can surely do that yourself if you already have a Windows Forms application. The hardest part was the SQL query.

Sure, here's the code which you probably would write.
make sure you add a reference of Microsoft.Applicationblocks.dll to your project.

import Microsoft.Applicationblocks  'Add to the namespace import statments on top of form

Sub Button1_Click(...)

dim strQuery as string = "select c.name from syscolumns c, sysobjects o where o.id = c.id and c.autoval is not null and o.name='mytable' "

'cnString variable must contain your valid connection string.
dim ds as Dataset = SqlHelper.ExecuteDataset(cnString, CommandType.Text, strQuery);

'assuming you only have a single key as primary key in table

Dim strPrimaryKeyName as string = Convert.ToString(ds.Tables(0).Rows(0)(0))

textbox1.Text = strPrimaryKeyName

End Sub

The above sample code will give you the column name in a textbox as you required.
Plz note that I have written this  just in notepad as I do not have Visual Studio installation on this machine. you can easily debug it in vs as required.

hi there techextreme,

is there any way without using  Microsoft.Applicationblocks  ?
i mean you must go and download it from somewhere,
and then build it and then use it.

it does not build Applicationblocks in my machine.

so i cannot import it and cannot use it. can i use some othe classes and methods?

thanks

nikolaosk, if you don't know how to open a dataset, it is worth the time reading on it because it is the base of all data access in .Net.

hi techextreme,

what you sent me is almost perfect. but the  sql query does not return anything....

so tha code fails.

if you use the sql script you sent me, in a northwind table, like categories, it returns nothing back....

The sql query returns nothing back means the table you are firing that statement on doesnot have a primary key, try it on the table which has atleast one primary key (may be some other table other than your 'mytable' ? )  , ALso make sure you have changed the connection string I have written over there (strDBConnection  variable). For the 'nothing' part, you can handle the null (nothing in vb.net) before fetching values from the dataset.  I do not have development environment setup on the pc i'm logged in and hence only can guide you.
Let me know of the results.

my table has primary key.

i tried the sql query, in the northwind database in the categories table.

it returns nothing back

select c.name from syscolumns c, sysobjects o where o.id = c.id
and c.autoval is not null and o.name='Categories'

if this sql works then the code will not fail. so i do not have to correct something in the code.

are you sure the sql code works fine?

first of all can you try executing it in sql query analyser window using sql server and see if you get any results /rows back in the query output?

yes that is exactly what i do. i fire the sql server 2005. i choose northwind database. i open a query window and i write


select c.name from syscolumns c, sysobjects o where o.id = c.id
and c.autoval is not null and o.name='Categories'

and i expect to get the categoryid column, cause this is the primary key. but i get back no results.

if you try it yourself you will see that will not work...

by the way the sql query that emoreau wrote

select o.name + '.' + c.name
from syscolumns c, sysobjects o
where c.status & 128 = 128
and o.id = c.id
order by o.name


works, but returns from the database all identity columns (across all tables). i want to get the identity column from a specific table

it works now. as i said it was the query's problem

i will have a look at the code to grasp it. i will be coming back with the points very soon