I have a gridview "new" command that insert a new row into a postgresql 
database.that uses the odbc driver.

Newbie as I am i thought that iReturn would have the new index that was 
created.

SqlDS1.InsertCommand = "INSERT INTO \"Algorithms\" (source_code_id,  comment 
VALUES (69, 'haleys comet', 1)";

int iResult = SqlDS_Algorithms.Insert();

Ok, instead of getting back an id I got back a "1" which just indicates the 
command ran ok.  Looking at the database I see the new id value and it is 
318.  Is there some mechanism I can use to obtain the index value that was 
autogenerated?

googleing around I see where some people are writing stored procedures and I 
see one here:

http://www.thescripts.com/forum/thread173510.html



Was just wondering if the is a way to use the gridview or sqldataset1 
control to get that index value and avoid writing a stored procedure.



...thanks..


-- 
======================================================================
Joseph "Beemer  Biker" Stateson
http://ResearchRiders.org     Ask about my 99'R1100RT
======================================================================

"Beemer Biker"  wrote in message 
news:134hsi499grbg21@corp.supernews.com...

>I have a gridview "new" command that insert a new row into a postgresql 
>database.that uses the odbc driver.
>
> Newbie as I am i thought that iReturn would have the new index that was 
> created.
>
> SqlDS1.InsertCommand = "INSERT INTO \"Algorithms\" (source_code_id, 
> comment VALUES (69, 'haleys comet', 1)";
>
> int iResult = SqlDS_Algorithms.Insert();
>
> Ok, instead of getting back an id I got back a "1" which just indicates 
> the command ran ok.  Looking at the database I see the new id value and it 
> is 318.  Is there some mechanism I can use to obtain the index value that 
> was autogenerated?
>
> googleing around I see where some people are writing stored procedures and 
> I see one here:
>
> http://www.thescripts.com/forum/thread173510.html
>
>
>
> Was just wondering if the is a way to use the gridview or sqldataset1 
> control to get that index value and avoid writing a stored procedure.


There is.
Still you need a "mini" stored procedure.
Just extend your INSERT command on the datasourcecontrol as follows:
"INSERT INTO myTable (x,y,z) VALUES (@x,@y,@y);SELECT @newID = @@IDENTITY;"

Then add an additional parameter @newID to your datasourcecontrol's 
InsertParameters, marking it as Direction="output" instead of the default 
input.

In the OnInserted event of the datasourcecontrol, you can get the value of 
this parameter with e.Command.Parameters("@newID").Value.

In Sql Server 2005, SELECT @@IDENTITY can be replaced by
SELECT SCOPE_IDENTITY() which is a little bit better.

Riki

"Beemer Biker"  wrote in message 
news:134hsi499grbg21@corp.supernews.com...

>I have a gridview "new" command that insert a new row into a postgresql 
>database.that uses the odbc driver.
>
> Newbie as I am i thought that iReturn would have the new index that was 
> created.
>
> SqlDS1.InsertCommand = "INSERT INTO \"Algorithms\" (source_code_id, 
> comment VALUES (69, 'haleys comet', 1)";
>
> int iResult = SqlDS_Algorithms.Insert();
>
> Ok, instead of getting back an id I got back a "1" which just indicates 
> the command ran ok.  Looking at the database I see the new id value and it 
> is 318.  Is there some mechanism I can use to obtain the index value that 
> was autogenerated?
>
> googleing around I see where some people are writing stored procedures and 
> I see one here:
>
> http://www.thescripts.com/forum/thread173510.html
>
>
>
> Was just wondering if the is a way to use the gridview or sqldataset1 
> control to get that index value and avoid writing a stored procedure.


There is.
Still you need a "mini" stored procedure.
Just extend your INSERT command on the datasourcecontrol as follows:
"INSERT INTO myTable (x,y,z) VALUES (@x,@y,@y);SELECT @newID = @@IDENTITY;"

Then add an additional parameter @newID to your datasourcecontrol's 
InsertParameters, marking it as Direction="output" instead of the default 
input.

In the OnInserted event of the datasourcecontrol, you can get the value of 
this parameter with e.Command.Parameters("@newID").Value.

In Sql Server 2005, SELECT @@IDENTITY can be replaced by
SELECT SCOPE_IDENTITY() which is a little bit better.

Riki

>> int iResult = SqlDS_Algorithms.Insert();
>>
>> Ok, instead of getting back an id I got back a "1" which just indicates 
>> the command ran ok.  Looking at the database I see the new id value and 
>> it is 318.  Is there some mechanism I can use to obtain the index value 
>> that was autogenerated?


Well, it returns the number of rows affected! And that's what you get as the 
iResult.

Follow what you got from thescripts-article or what Riki wrote. That's the 
sole way out.


-- 
Happy Hacking,
Gaurav Vaish | www.mastergaurav.com
www.edujini-labs.com
http://eduzine.edujini-labs.com
-----------------------------------------

>> int iResult = SqlDS_Algorithms.Insert();
>>
>> Ok, instead of getting back an id I got back a "1" which just indicates 
>> the command ran ok.  Looking at the database I see the new id value and 
>> it is 318.  Is there some mechanism I can use to obtain the index value 
>> that was autogenerated?


Well, it returns the number of rows affected! And that's what you get as the 
iResult.

Follow what you got from thescripts-article or what Riki wrote. That's the 
sole way out.


-- 
Happy Hacking,
Gaurav Vaish | www.mastergaurav.com
www.edujini-labs.com
http://eduzine.edujini-labs.com
-----------------------------------------