retreiving the id problem

[Reddy]

Hi,

Once after inserting a record into a table is there a more efficient way of
retreiving the id(autonumber field) value in the following example. Using a
SQL server database.

con.Open()

strSql="insert into table1(column2) values('test1')"
cmd = New OdbcCommand(strSql, con)
cmd.ExecuteNonQuery()
cmd.Dispose()

Dim id as Integer

strSql="select max(id) from table1"
cmd = New OdbcCommand(strSql, con)
id = cmd.CType(cmd.ExecuteScalar(), Int32)
cmd.Dispose()


strSql="insert into table2(column1, table1id) values('test2', " & id & ")"
cmd = New OdbcCommand(strSql, con)
cmd.ExecuteNonQuery()
cmd.Dispose()

con.Close()


Thanks

Srinivas

 

[Lasse Edsvik]

sql 2k?

strSql="set nocount on "&_
"insert into table1(column2) values('test1') "&_
"select newid=scope_identity()"
set rs=conn.execute(strSql)

newid=rs("newid")


/Lasse

 

[Reddy]

In .net can I use similar sql statement and use ExecuteScalar of command
object to reteive the newid


Srinivas