CODEWITHSUNDEEP
asp.net
anay
anay

Reputation: 145

asp.net sql query help

i have a sql query in asp.net

SqlCommand QueidCmd = new SqlCommand("select scope_identity() from table1", conn);

i want to take the output and store it in variable use this variable in another insert query which is just below it...both of these queries are in loop...can this be done??

Upvotes: 0

Views: 95

Answers (3)

RickNZ
RickNZ

Reputation: 18654

First, don't SELECT SCOPE_IDENTITY() from a table; it will do the wrong thing.

Next, the key to the answer to your question is that you can combine multiple commands / queries into a single statement for SqlCommand by separating them with semicolons. For example:

string sql = "DECLARE @scope INT;SET @scope = SCOPE_IDENTITY();UPDATE MyTable SET Val = @scope";
SqlCommand cmd = new SqlCommand(sql);
cmd.ExecuteNonQuery();

If you want to introduce variables, you should use a parameterized query, not string concatenation.

You can also use stored procedures -- they're often a cleaner way to go.

Upvotes: 2

Wim
Wim

Reputation: 12082

Use:

object retVal = QueidCmd.ExecuteScalar();
int pk = Convert.ToInt32(retVal); // use this in your next insert

Upvotes: 1

user211416
user211416

Reputation: 111

You can do it in one SQL query:

DECLARE @myVariable YOUR_TYPE;
SELECT @myVariable = SCOPE_IDENTITY() FROM table1;
UPDATE table2 SET something = @myVariable;

Upvotes: 2

Related Questions