|
You mean like select * from mytable ?
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
|
Nope. At some point, you need column names.
|
|
|
|
|
There's the really bad practice of select *. You really should use column names though.
|
|
|
|
|
Given the other replies - what is it that you are trying to do - maybe we can provide some other advice?
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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).
|
|
|
|
|
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.
|
|
|
|
|
Brilliant! Thanks guys, much appreciated.
|
|
|
|
|
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.....
|
|
|
|
|
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
|
|
|
|
|
|
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
|
|
|
|
|
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 ?
|
|
|
|
|
|
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
|
|
|
|
|
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()
|
|
|
|
|
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
|
|
|
|
|
|
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.
|
|
|
|
|
Hi All,
Is there any Query to Export Table to Xml File Using Sql Server?
Plz Suggest Me ...
Thx in Advance
Nagaraju
|
|
|
|
|
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"
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
