Writing SQL API, Problems with Queries

[dseaver]

I posted this in the SQL forum, and I got directed here, here is the problem:

I am new to writing SQL code, but I am following a Tutorial from asp.net. I have written the following insert for my SQL DB, and I want it to return a scalar of the created records Primary key.

--example
INSERT INTO MyTable
                      (OtherTableReference, Descrip)
VALUES     (@OtherTableReference,@Descrip)

SELECT SCOPE_IDENTITY() --Error here: Unable to parse query text.

I am trying to write an API in C#, which would return the Index, but I can't get this to work in the DAL level.

Here is the link to the Tutorial that is implementing a similar insert into there DAL. It's under "STEP 4: Inserting Updating and Deleting Data" under "Creating Custom Insert, Update, and Delete Methods". Its about 3/4 down the page:

http://www.asp.net/learn/data-access/tutorial-01-cs.aspx

 

[dseaver]

I was casting in the BLL, when I switch from (int)Adapter.Insert(...) to Convert.Int32(Adapter.Insert(..)) it return the correct value.

 

[nicklieb]

never used scope_idenetity,

I always use @@IDENTITY, it give the ID of the last insert in the stored proc.

 

[AlexCuse]

Never use @@IDENTITY, especially on tables with triggers. SCOPE_IDENTITY() is much more reliable, and I think it would be a good practice to just use it all the time.

See #9 on this list:

http://dotnetjunkies.com/Article/86F0988E-FED4-414F-BA2E-D01D953C11BE.dcik

dseaver - A stored proc would make your life much easier, but I know this is not what you are after. Can you post the code you are using now to try and execute this query?

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[dseaver]

I am using the same code as above, my problem was with casting in the BLL level. I was using the following as a BLL

public int AddData(int OtherTableReference, string Descrip)
{
     return (int)Adapter.CustomInsert(OtherTableReference, Descrip);
}

That was causing a casting error, and I thought the problem was in the query. I found that the function worked when I did it as

return [b][red]Convert.Int32[/red][/b](Adapter.CustomInsert(OtherTableReference, Descrip));