[Message Deleted] - Database Discussion Boards

Obtaining value of identity field when inserting into SQL

Dewald28-Nov-07 23:49

28-Nov-07 23:49

Hi all,

I've been trying to find a similar question but couldn't find one so forgive me if this has already been dealt with.

Is there a way that I can immediately obtain the value of an auto incrementing identity field in SQL if I insert a new record?

Let's say I run the following command:
SqlCommand myCommand = new SqlCommand("INSERT INTO Users (UserName, Age) VALUES ('Joe Bloggs', 30)", myConnection);
but the Users table has an auto incrementing identity field called UserID. Is there a way I can obtain the new value of UserID without having to run a subsequent SELECT UserID FROM Users WHERE... query?

Thanks

Re: Obtaining value of identity field when inserting into SQL

Colin Angus Mackay29-Nov-07 0:07

Colin Angus Mackay

29-Nov-07 0:07

After the INSERT do: SELECT SCOPE_IDENTITY();
This is all in one command. So you then do a ExecuteScalar() to get the value back to your application.

Upcoming FREE developer events:
* Developer Day Scotland

My website

Re: Obtaining value of identity field when inserting into SQL

Tobias Schoenig29-Nov-07 0:31

Tobias Schoenig

29-Nov-07 0:31

You could also use a SELECT @@identity;. But I think that it's better to use the Scope_Identity()-function, because @@identity returns the last identity value set in the current connection, while Scope_Identity() returns the last identity value that was set in the current scope (e.g. in a stored procedure).

Re: Obtaining value of identity field when inserting into SQL

Colin Angus Mackay29-Nov-07 0:40

Colin Angus Mackay

29-Nov-07 0:40

Tobias Schoenig wrote:
You could also use a SELECT @@identity;. But I think that it's better to use the Scope_Identity()-function, because @@identity returns the last identity value set in the current connection, while Scope_Identity() returns the last identity value that was set in the current scope (e.g. in a stored procedure).

Yes, so if there there are triggers involved then SCOPE_IDENTITY() will ensure the correct identity is returned.

Upcoming FREE developer events:
* Developer Day Scotland

My website

Re: Obtaining value of identity field when inserting into SQL

Dewald29-Nov-07 1:16

Dewald

29-Nov-07 1:16

Brilliant! Thanks guys, much appreciated.

Rank(sql)

r a j u u28-Nov-07 23:44

r a j u u

28-Nov-07 23:44

Hi friends..I am using SQL 2005..

I have table field id and bid_amount
id bid_amount
1 2000
1 2500
1 2200
2 2000
2 1500

here how get the rank for individual id...I want to display like

id bid_amount Rank
1 2500 1
1 2200 2
1 2000 3
2 2000 1
2 1500 2
plz help me urgent.....

Re: Rank(sql)

pmarfleet29-Nov-07 1:07

pmarfleet

29-Nov-07 1:07

Google is a wonderful tool. I searched for 2 seconds and found this[^].

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

[Message Deleted]

gubba28-Nov-07 23:42

gubba

28-Nov-07 23:42

[Message Deleted]

Re: Regarding retreiving values from table

pmarfleet29-Nov-07 1:01

pmarfleet

29-Nov-07 1:01

gubba wrote:
my question is that is there any method that we can retreive the values not using the colmn names but the column numbers.

I don't think so. You can define aliases for the columns though, e.g. SELECT Option_1 AS [1], Select Option_2 AS [2] etc...

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

insert new row in sql server

break_day28-Nov-07 21:20

break_day

28-Nov-07 21:20

Hi,
i insert new row in sql server with this syntax

Dim insert_transaction As SqlCommand
insert_transaction = New SqlCommand("insert into Transaction (Transaction_Id,Account_Id,Type,Amount,Date,Time) values ('30','" & account & "','Account Transfer','" & money.Text & "','" & Today & "','" & time & "')", myConnection)
insert_transaction.ExecuteNonQuery()

the error appear in ExecuteNonQuery()
the question is
how i insert new row in vb.net connect with sql server ?

Re: insert new row in sql server

yaad fridman28-Nov-07 21:51

yaad fridman

28-Nov-07 21:51

which error you get?

Re: insert new row in sql server

break_day28-Nov-07 22:13

break_day

28-Nov-07 22:13

the error "Icorrect synyax near the keyword 'Transaction'" and i have table transaction in database
and anther question : how can insert primary key ( Transaction_Id) automatic without i insert it manual

Re: insert new row in sql server

yaad fridman28-Nov-07 22:40

yaad fridman

28-Nov-07 22:40

declear the field Transaction_Id as identity and write the query with @@IDENTITY AS ID
like this
insert_transaction = New SqlCommand("insert into [Transaction] (Transaction_Id,Account_Id,Type,Amount,Date,Time) values ('30','" & account & "','Account Transfer','" & money.Text & "','" & Today & "','" & time & "') @@IDENTITY AS ID ", myConnection)

object autonumber = insert_transaction.ExecuteScalarIdentity()

Re: insert new row in sql server

break_day29-Nov-07 0:01

break_day

29-Nov-07 0:01

1. new error appear :"there is no source code available for the current loaction
if i click yes the same last error appear"

2. and I'm not understand the how declare the @@IDENTITY , and if declare it there is no meaning for insert value for transaction_Id

Re: insert new row in sql server

yaad fridman1-Dec-07 20:58

yaad fridman

1-Dec-07 20:58

1. I think that you need to rebuild your soulotion.
2.@@IDENTITY it meen the query return the new trantaction_id,
and you need to declear the columns trantaction_id as identity in your database in aqlserver ->

http://www.sitepoint.com/print/sql-server-2000-database[^]

http://www.devcity.net/Articles/276/1/article.aspx[^]

Re: insert new row in sql server

ChandraRam28-Nov-07 22:19

ChandraRam

28-Nov-07 22:19

Try it this way:

insert_transaction = New SqlCommand("insert into [Transaction] (Transaction_Id,Account_Id,Type,Amount,Date,Time) values ('30','" & account & "','Account Transfer','" & money.Text & "','" & Today & "','" & time & "')", myConnection)

The word Transaction has a special meaning to most databases.

Having said that, it would be a great deal better if you used parameters in your query, as your current query leaves your database open to SQL injection.

Export Table to Xml Using Sql

Nagaraju_Focus28-Nov-07 21:09

Nagaraju_Focus

28-Nov-07 21:09

Hi All,

Is there any Query to Export Table to Xml File Using Sql Server?

Plz Suggest Me ...

Thx in Advance

Nagaraju

Re: Export Table to Xml Using Sql

Maharishi Bhatia28-Nov-07 21:56

Maharishi Bhatia

28-Nov-07 21:56

In MS SQL Server 2000 there is a "FOR" clause which you can use to retrive the select query result in xml. Basic syntax is as follows,

<br />
FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64]<br />
<br />
Arguments<br />
XML mode<br />
<br />
Specifies the XML mode. XML mode determines the shape of the resulting XML. <br />
mode can be RAW, AUTO, or EXPLICIT.<br />
<br />
XMLDATA<br />
<br />
Specifies that an XML-Data schema should be returned. The schema is prepended to the document as an inline schema.<br />
<br />
ELEMENTS<br />
<br />
If the ELEMENTS option is specified, the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only.<br />
<br />
BINARY BASE64<br />
<br />
If the BINARY Base64 option is specified, any binary data returned by the query is represented in base64-encoded format. To retrieve binary data using RAW and EXPLICIT mode, this option must be specified. In AUTO mode, binary data is returned as a reference by default.<br />

Example : Select * from TableName for xml auto

You can search the SQL transact help for further details.

Nothing is Impossible. Even impossible spells "i m possible"

Re: Export Table to Xml Using Sql

Nagaraju_Focus28-Nov-07 23:23

Nagaraju_Focus

28-Nov-07 23:23

Thanks Maharishi,

But I need to Save the whole table Data into a xml file which is to be created with a sql query.

means
Select * from TableName to "c:\test.xml"

I need a query to send whole sql table to cml file

is there any way to do like that?

thx..........

Nagaraju

Re: Export Table to Xml Using Sql

Tobias Schoenig29-Nov-07 2:58

Tobias Schoenig

29-Nov-07 2:58

I don't know wether you can achieve this with a sql statement, but if your're using a .NET-application , to send the SQL Statement, you could do the following:

- Fill a DataSet via SqlDataAdapter with the output of your Select-statement
- Write the data to a xml-file using the DataSet.WriteXML()-method.

This would solve your problem in a very easy way, but if you really need to do that in one sql-statement I haven't got the faintest idea.

Tobias

Re: Export Table to Xml Using Sql

Maharishi Bhatia1-Dec-07 17:35

Maharishi Bhatia

1-Dec-07 17:35

Hi,

For that you will need to write a PL/SQL for this kind of stuff. In MS SQL 2000/2005 there is an extended stored proc named xp_cmdshell which can be used to access the windows command line which can be used to save file. I have got an example which you can use for this...

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = 'c:\data.txt'

SET @bcpCommand = 'bcp "SELECT * FROM TableName for xml auto" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P pwd -c'

EXEC master..xp_cmdshell @bcpCommand

But for this kind of extended proc access the procedure has to run in 'sa' context... or you need administrator rights to do that...

Hope this helps

Maharishi

Nothing is Impossible. Even impossible spells "i m possible"

remote database accessing

dayya28-Nov-07 19:18

dayya

28-Nov-07 19:18

I want to connect to a database and retrieve and insert data which is in another server.which means the database is not in our server.
it may be in another server globally remote.
this is about an asp web application.
please reply me as soon as possible

dayya <gmherath@gmail.com>

Re: remote database accessing

pmarfleet28-Nov-07 20:45

pmarfleet

28-Nov-07 20:45

Either set your remote server up as a linked server in SQL Server or write a web service to access it.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: remote database accessing

Paul Conrad29-Nov-07 14:00

Paul Conrad

29-Nov-07 14:00

dayya wrote:
please reply me as soon as possible

What kind of reply are you looking for? Suggestions or free code?

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Get size of a table row

Soumya Mathew28-Nov-07 19:10

Soumya Mathew

28-Nov-07 19:10

How can we get the newly inserted row's size from a SQL Server table?

Soumya Mathew

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.