@@identity not working using SQL Express 2005 Sept CTP

I have just inherited some code from another company which uses SQL Server.
The logic is dependent on @.@.IDENTITY to retrieve the record just inserted -
so that more info can be added to the record. kind of a silly implementation
- but it uses a bunch of generic code to insert a new row and generate a
GUID, etc.. and it would be significant work to rewrite the entire
application.
The problem is the even though the table has an id column that is defined
with IDENTITY - and through queries I can easily see that each row added has
the proper values for the id column - @.@.IDENTITY inside the .asp vb scripting
app is returning NULL... I am using SQL Server Express 2005 Sept CTP set up
using a system DSN with the SQL Native Client (2005) driver.
here is the code:
Function NewRow(con,tab,col,val)
set nrconn = Server.CreateObject("ADODB.Connection")
nrconn.open SiteConnectionString
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
Set rs = nrconn.Execute(cmd)
cmd = "select @.@.IDENTITY as 'newid' from "&tab
Set rs = nrconn.Execute(cmd)
set NewRow = rs
End Function
After returning the code uses NewRow("newid") to fetch the record and update
it. of course, it returns NULL and no record is retrieved... Any help
would be much appreciated.
--
-Steve-oHi
You are submitting it as 2 batches, so it is not available to the 2nd one.
Any reason why you are not using stored procedures to do this? Dynamic SQL
is asking for security problems.
SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
@.@.identity's value if it does another insert.
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
SCOPE_IDENTITY as 'newid' from "&tab "
Set rs = nrconn.Execute(cmd)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>I have just inherited some code from another company which uses SQL Server.
> The logic is dependent on @.@.IDENTITY to retrieve the record just
> inserted -
> so that more info can be added to the record. kind of a silly
> implementation
> - but it uses a bunch of generic code to insert a new row and generate a
> GUID, etc.. and it would be significant work to rewrite the entire
> application.
> The problem is the even though the table has an id column that is defined
> with IDENTITY - and through queries I can easily see that each row added
> has
> the proper values for the id column - @.@.IDENTITY inside the .asp vb
> scripting
> app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> up
> using a system DSN with the SQL Native Client (2005) driver.
> here is the code:
> Function NewRow(con,tab,col,val)
> set nrconn = Server.CreateObject("ADODB.Connection")
> nrconn.open SiteConnectionString
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> Set rs = nrconn.Execute(cmd)
> cmd = "select @.@.IDENTITY as 'newid' from "&tab
> Set rs = nrconn.Execute(cmd)
> set NewRow = rs
> End Function
> After returning the code uses NewRow("newid") to fetch the record and
> update
> it. of course, it returns NULL and no record is retrieved... Any help
> would be much appreciated.
> --
> -Steve-o|||Hi
Strange ... the code works on older SQL server version. Nonetheless I
changed as you suggested and same result. I did a little more digging and
the execute line is return rs as type of 'Fields' instead of RecordSet AND
the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
help !
--
-Steve-o
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> You are submitting it as 2 batches, so it is not available to the 2nd one.
> Any reason why you are not using stored procedures to do this? Dynamic SQL
> is asking for security problems.
> SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> @.@.identity's value if it does another insert.
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> SCOPE_IDENTITY as 'newid' from "&tab "
> Set rs = nrconn.Execute(cmd)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> >I have just inherited some code from another company which uses SQL Server.
> > The logic is dependent on @.@.IDENTITY to retrieve the record just
> > inserted -
> > so that more info can be added to the record. kind of a silly
> > implementation
> > - but it uses a bunch of generic code to insert a new row and generate a
> > GUID, etc.. and it would be significant work to rewrite the entire
> > application.
> >
> > The problem is the even though the table has an id column that is defined
> > with IDENTITY - and through queries I can easily see that each row added
> > has
> > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> > scripting
> > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> > up
> > using a system DSN with the SQL Native Client (2005) driver.
> >
> > here is the code:
> >
> > Function NewRow(con,tab,col,val)
> > set nrconn = Server.CreateObject("ADODB.Connection")
> > nrconn.open SiteConnectionString
> > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> > Set rs = nrconn.Execute(cmd)
> > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> > Set rs = nrconn.Execute(cmd)
> > set NewRow = rs
> >
> > End Function
> >
> > After returning the code uses NewRow("newid") to fetch the record and
> > update
> > it. of course, it returns NULL and no record is retrieved... Any help
> > would be much appreciated.
> >
> > --
> > -Steve-o
>
>|||solved the "Fields" problem - I had erroneously removed the "set" fro the rs
= stmt (WHOOPS !). So now the type is returned as Recordset, however,
rs.Fields.Count is 0 ... so still nothing is being returned...
--
-Steve-o
"steve-o" wrote:
> Hi
> Strange ... the code works on older SQL server version. Nonetheless I
> changed as you suggested and same result. I did a little more digging and
> the execute line is return rs as type of 'Fields' instead of RecordSet AND
> the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> help !
> --
> -Steve-o
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > You are submitting it as 2 batches, so it is not available to the 2nd one.
> >
> > Any reason why you are not using stored procedures to do this? Dynamic SQL
> > is asking for security problems.
> >
> > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> > @.@.identity's value if it does another insert.
> >
> > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> > SCOPE_IDENTITY as 'newid' from "&tab "
> > Set rs = nrconn.Execute(cmd)
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> > >I have just inherited some code from another company which uses SQL Server.
> > > The logic is dependent on @.@.IDENTITY to retrieve the record just
> > > inserted -
> > > so that more info can be added to the record. kind of a silly
> > > implementation
> > > - but it uses a bunch of generic code to insert a new row and generate a
> > > GUID, etc.. and it would be significant work to rewrite the entire
> > > application.
> > >
> > > The problem is the even though the table has an id column that is defined
> > > with IDENTITY - and through queries I can easily see that each row added
> > > has
> > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> > > scripting
> > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> > > up
> > > using a system DSN with the SQL Native Client (2005) driver.
> > >
> > > here is the code:
> > >
> > > Function NewRow(con,tab,col,val)
> > > set nrconn = Server.CreateObject("ADODB.Connection")
> > > nrconn.open SiteConnectionString
> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> > > Set rs = nrconn.Execute(cmd)
> > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> > > Set rs = nrconn.Execute(cmd)
> > > set NewRow = rs
> > >
> > > End Function
> > >
> > > After returning the code uses NewRow("newid") to fetch the record and
> > > update
> > > it. of course, it returns NULL and no record is retrieved... Any help
> > > would be much appreciated.
> > >
> > > --
> > > -Steve-o
> >
> >
> >|||more info ...
If I follow the original sequence of insert followed by select query I do
get a recordset back with a field count of 1. the name of the item is indeed
'newid' but the value is NULL.
strangely enough when using SQLCMD and doing this sequence of operations by
hand - SQLCMD outputs one line for each record in the table with the newid
field value for each being the "latest" value (i.e. value of 23 if the last
inserted record had and identity value of 23). so it seems like it is
working there, but not in the scripted app...
-Steve-o
"steve-o" wrote:
> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
> rs.Fields.Count is 0 ... so still nothing is being returned...
> --
> -Steve-o
>
> "steve-o" wrote:
> > Hi
> >
> > Strange ... the code works on older SQL server version. Nonetheless I
> > changed as you suggested and same result. I did a little more digging and
> > the execute line is return rs as type of 'Fields' instead of RecordSet AND
> > the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> > help !
> >
> > --
> > -Steve-o
> >
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > You are submitting it as 2 batches, so it is not available to the 2nd one.
> > >
> > > Any reason why you are not using stored procedures to do this? Dynamic SQL
> > > is asking for security problems.
> > >
> > > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> > > @.@.identity's value if it does another insert.
> > >
> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> > > SCOPE_IDENTITY as 'newid' from "&tab "
> > > Set rs = nrconn.Execute(cmd)
> > >
> > > Regards
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > IM: mike@.epprecht.net
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> > > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> > > >I have just inherited some code from another company which uses SQL Server.
> > > > The logic is dependent on @.@.IDENTITY to retrieve the record just
> > > > inserted -
> > > > so that more info can be added to the record. kind of a silly
> > > > implementation
> > > > - but it uses a bunch of generic code to insert a new row and generate a
> > > > GUID, etc.. and it would be significant work to rewrite the entire
> > > > application.
> > > >
> > > > The problem is the even though the table has an id column that is defined
> > > > with IDENTITY - and through queries I can easily see that each row added
> > > > has
> > > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> > > > scripting
> > > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> > > > up
> > > > using a system DSN with the SQL Native Client (2005) driver.
> > > >
> > > > here is the code:
> > > >
> > > > Function NewRow(con,tab,col,val)
> > > > set nrconn = Server.CreateObject("ADODB.Connection")
> > > > nrconn.open SiteConnectionString
> > > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> > > > Set rs = nrconn.Execute(cmd)
> > > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> > > > Set rs = nrconn.Execute(cmd)
> > > > set NewRow = rs
> > > >
> > > > End Function
> > > >
> > > > After returning the code uses NewRow("newid") to fetch the record and
> > > > update
> > > > it. of course, it returns NULL and no record is retrieved... Any help
> > > > would be much appreciated.
> > > >
> > > > --
> > > > -Steve-o
> > >
> > >
> > >|||Try adding SET NOCOUNT ON before the INSERT statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...
> more info ...
> If I follow the original sequence of insert followed by select query I do
> get a recordset back with a field count of 1. the name of the item is indeed
> 'newid' but the value is NULL.
> strangely enough when using SQLCMD and doing this sequence of operations by
> hand - SQLCMD outputs one line for each record in the table with the newid
> field value for each being the "latest" value (i.e. value of 23 if the last
> inserted record had and identity value of 23). so it seems like it is
> working there, but not in the scripted app...
>
> --
> -Steve-o
>
> "steve-o" wrote:
>> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
>> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
>> rs.Fields.Count is 0 ... so still nothing is being returned...
>> --
>> -Steve-o
>>
>> "steve-o" wrote:
>> > Hi
>> >
>> > Strange ... the code works on older SQL server version. Nonetheless I
>> > changed as you suggested and same result. I did a little more digging and
>> > the execute line is return rs as type of 'Fields' instead of RecordSet AND
>> > the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
>> > help !
>> >
>> > --
>> > -Steve-o
>> >
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> > > Hi
>> > >
>> > > You are submitting it as 2 batches, so it is not available to the 2nd one.
>> > >
>> > > Any reason why you are not using stored procedures to do this? Dynamic SQL
>> > > is asking for security problems.
>> > >
>> > > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
>> > > @.@.identity's value if it does another insert.
>> > >
>> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
>> > > SCOPE_IDENTITY as 'newid' from "&tab "
>> > > Set rs = nrconn.Execute(cmd)
>> > >
>> > > Regards
>> > > --
>> > > Mike Epprecht, Microsoft SQL Server MVP
>> > > Zurich, Switzerland
>> > >
>> > > IM: mike@.epprecht.net
>> > >
>> > > MVP Program: http://www.microsoft.com/mvp
>> > >
>> > > Blog: http://www.msmvps.com/epprecht/
>> > >
>> > > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
>> > > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>> > > >I have just inherited some code from another company which uses SQL Server.
>> > > > The logic is dependent on @.@.IDENTITY to retrieve the record just
>> > > > inserted -
>> > > > so that more info can be added to the record. kind of a silly
>> > > > implementation
>> > > > - but it uses a bunch of generic code to insert a new row and generate a
>> > > > GUID, etc.. and it would be significant work to rewrite the entire
>> > > > application.
>> > > >
>> > > > The problem is the even though the table has an id column that is defined
>> > > > with IDENTITY - and through queries I can easily see that each row added
>> > > > has
>> > > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
>> > > > scripting
>> > > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
>> > > > up
>> > > > using a system DSN with the SQL Native Client (2005) driver.
>> > > >
>> > > > here is the code:
>> > > >
>> > > > Function NewRow(con,tab,col,val)
>> > > > set nrconn = Server.CreateObject("ADODB.Connection")
>> > > > nrconn.open SiteConnectionString
>> > > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
>> > > > Set rs = nrconn.Execute(cmd)
>> > > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
>> > > > Set rs = nrconn.Execute(cmd)
>> > > > set NewRow = rs
>> > > >
>> > > > End Function
>> > > >
>> > > > After returning the code uses NewRow("newid") to fetch the record and
>> > > > update
>> > > > it. of course, it returns NULL and no record is retrieved... Any help
>> > > > would be much appreciated.
>> > > >
>> > > > --
>> > > > -Steve-o
>> > >
>> > >
>> > >|||YAY !!! Mike gave me the first half of the solution and Tibor the second
half. Thanks !!!
--
-Steve-o
"Tibor Karaszi" wrote:
> Try adding SET NOCOUNT ON before the INSERT statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...
> > more info ...
> > If I follow the original sequence of insert followed by select query I do
> > get a recordset back with a field count of 1. the name of the item is indeed
> > 'newid' but the value is NULL.
> >
> > strangely enough when using SQLCMD and doing this sequence of operations by
> > hand - SQLCMD outputs one line for each record in the table with the newid
> > field value for each being the "latest" value (i.e. value of 23 if the last
> > inserted record had and identity value of 23). so it seems like it is
> > working there, but not in the scripted app...
> >
> >
> >
> > --
> > -Steve-o
> >
> >
> > "steve-o" wrote:
> >
> >> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
> >> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
> >> rs.Fields.Count is 0 ... so still nothing is being returned...
> >> --
> >> -Steve-o
> >>
> >>
> >> "steve-o" wrote:
> >>
> >> > Hi
> >> >
> >> > Strange ... the code works on older SQL server version. Nonetheless I
> >> > changed as you suggested and same result. I did a little more digging and
> >> > the execute line is return rs as type of 'Fields' instead of RecordSet AND
> >> > the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> >> > help !
> >> >
> >> > --
> >> > -Steve-o
> >> >
> >> >
> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >
> >> > > Hi
> >> > >
> >> > > You are submitting it as 2 batches, so it is not available to the 2nd one.
> >> > >
> >> > > Any reason why you are not using stored procedures to do this? Dynamic SQL
> >> > > is asking for security problems.
> >> > >
> >> > > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> >> > > @.@.identity's value if it does another insert.
> >> > >
> >> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> >> > > SCOPE_IDENTITY as 'newid' from "&tab "
> >> > > Set rs = nrconn.Execute(cmd)
> >> > >
> >> > > Regards
> >> > > --
> >> > > Mike Epprecht, Microsoft SQL Server MVP
> >> > > Zurich, Switzerland
> >> > >
> >> > > IM: mike@.epprecht.net
> >> > >
> >> > > MVP Program: http://www.microsoft.com/mvp
> >> > >
> >> > > Blog: http://www.msmvps.com/epprecht/
> >> > >
> >> > > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> >> > > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> >> > > >I have just inherited some code from another company which uses SQL Server.
> >> > > > The logic is dependent on @.@.IDENTITY to retrieve the record just
> >> > > > inserted -
> >> > > > so that more info can be added to the record. kind of a silly
> >> > > > implementation
> >> > > > - but it uses a bunch of generic code to insert a new row and generate a
> >> > > > GUID, etc.. and it would be significant work to rewrite the entire
> >> > > > application.
> >> > > >
> >> > > > The problem is the even though the table has an id column that is defined
> >> > > > with IDENTITY - and through queries I can easily see that each row added
> >> > > > has
> >> > > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> >> > > > scripting
> >> > > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> >> > > > up
> >> > > > using a system DSN with the SQL Native Client (2005) driver.
> >> > > >
> >> > > > here is the code:
> >> > > >
> >> > > > Function NewRow(con,tab,col,val)
> >> > > > set nrconn = Server.CreateObject("ADODB.Connection")
> >> > > > nrconn.open SiteConnectionString
> >> > > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> >> > > > Set rs = nrconn.Execute(cmd)
> >> > > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> >> > > > Set rs = nrconn.Execute(cmd)
> >> > > > set NewRow = rs
> >> > > >
> >> > > > End Function
> >> > > >
> >> > > > After returning the code uses NewRow("newid") to fetch the record and
> >> > > > update
> >> > > > it. of course, it returns NULL and no record is retrieved... Any help
> >> > > > would be much appreciated.
> >> > > >
> >> > > > --
> >> > > > -Steve-o
> >> > >
> >> > >
> >> > >
>