Solved

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

Posted on 2008-10-26
17
903 Views
Last Modified: 2013-11-26
hello experts,

i have a windows form application using visual studio 2008 and sql server 2005.

i am connecting to a sql server 2005 database, called "test" and a table "mytable"

 it has 5 fields. id,name,surname,age,comments. the id column has a identity-autonumber turned on with an identity number of 1.

i want to have a button in my form and a textbox. when clicking the button i want to connect to the database(local in my sql server-sqlexpress) and if the table has a column with identity, to return the name of the column in the textfield. in this example to return the value "id" in the textfield, thus this is the column with identity turned on. i want to use vb.net-2005

thanks

0
Comment
Question by:nikolaosk
  • 8
  • 6
  • 3
17 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 22807220
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
0
 
LVL 12

Expert Comment

by:techExtreme
ID: 22807245
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!

0
 

Author Comment

by:nikolaosk
ID: 22808832
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 22809085
you can surely do that yourself if you already have a Windows Forms application. The hardest part was the SQL query.
0
 
LVL 12

Expert Comment

by:techExtreme
ID: 22810773
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.


0
 

Author Comment

by:nikolaosk
ID: 22811545
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
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 22811695
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.
0
 
LVL 12

Accepted Solution

by:
techExtreme earned 250 total points
ID: 22811991
Well the application blocks dll is used to simplify the process and is optimized with SQL server.
Here's the code without using it.
Arrange the code as required.

'the import statements
import System.Data.SqlClient;
import System.Data;

Dim strDBConnection As String = "server=(local);database=DatabaseName;user id=UserName;password=Pwd;"

Dim dbConnection As SqlConnection

dbConnection = New SqlConnection(strDBConnection)

Dim strSelectSql 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'"

'Open the connection
dbConnection.Open()

'Create a command
Dim selectSqlCommand As New SqlCommand(strSelectSql, dbConnection)
Dim sqlData As New SqlDataAdapter(selectSqlCommand)
Dim dsSelectData As New DataSet()
sqlData.Fill(dsSelectData)

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

textbox1.Text = strPrimaryKeyName
0
 

Author Comment

by:nikolaosk
ID: 22812122
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....
0
 
LVL 12

Expert Comment

by:techExtreme
ID: 22812192
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.

0
 

Author Comment

by:nikolaosk
ID: 22812294
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'
0
 

Author Comment

by:nikolaosk
ID: 22812310
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?
0
 
LVL 12

Expert Comment

by:techExtreme
ID: 22812327
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?
0
 

Author Comment

by:nikolaosk
ID: 22812389
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...
0
 

Author Comment

by:nikolaosk
ID: 22812416
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
0
 
LVL 12

Assisted Solution

by:techExtreme
techExtreme earned 250 total points
ID: 22812468
Ok, use this query

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

these should return you some result.
Post the result then here .
0
 

Author Comment

by:nikolaosk
ID: 22812987
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
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question