Solved

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

Posted on 2008-10-26
17
890 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 69

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
 
LVL 69

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 69

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now