?
Solved

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

Posted on 2008-10-26
17
Medium Priority
?
912 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

 
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 1000 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 1000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

765 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