sql rowcount: identity

Showing posts with label identity. Show all posts

Saturday, February 11, 2012

@@identity not working using SQL Express 2005 Sept CTP

Thursday, February 9, 2012

@@identity... am i even close here?

string ConnectionString =ConfigurationManager.ConnectionStrings["myConnString"].ConnectionString;

SqlConnection connection =newSqlConnection(ConnectionString);

SqlCommand cmd =newSqlCommand(@."INSERT INTO CreditAppFile (GI_RB_Div) VALUES(@.GI_RB_Div) SELECT @.@.idientity as 'NewID'", connection);

cmd.Parameters.Add("@.ID", SqlDbType.BigInt, 8).Value = NewID.Text;

Response.Redirect(DisplayPage.aspx?ID=@.ID);

i want the ID of the most recently added record to be passed to another page to show all entered data from the prior page... am i close?

A general way is to use a stored procedure to do the INSERT and return the value of the ID via OUTPUT Parameter. Also, please use SCOPE_IDENTITY() instead of @.@.IDENTITY.

From Books On Line:

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

Check the second part of this article:http://dotnetjunkies.com/WebLog/dinakar/articles/74220.aspx to see some sample code for using stored procedures with OUTPUT parameters.

|||

Like Dinakar, my preference is for using stored procedures, but that is not always possible. You can pass two consecutive SQL commands to the command object as you have (almost) done in your example, but they must be separated by a semi-colon. The only parameter you need to add to the command object is the one for @.GI_RB_Div. Then, using Scope_Identity(), your code would look like this:

string connstr = WebConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
string query = "INSERT INTO CreditAppFile (GI_RB_Div) VALUES(@.GI_RB_Div); Select Scope_Identity();";

SqlConnection conn = new SqlConnection(connstr);
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@.GI_RB_Div", <Value>);
conn.Open();
string NewID = cmd.ExecuteScalar().ToString();
conn.Close();
Server.Transfer("DisplayPage.aspx?ID=" + NewID);

|||

Compiler Error Message:CS1501: No overload for method 'AddWithValue' takes '3' arguments

Source Error:

Line 203: tryLine 204: {Line 205: cmd.Parameters.AddWithValue("@.GI_RB_Div", SqlDbType.VarChar, 50).Value = GI_RB_Div.SelectedValue;Line 206: cmd.Parameters.AddWithValue("@.GI_RB_ExistCust", SqlDbType.VarChar, 50).Value = GI_RB_ExistCust.SelectedValue;Line 207: cmd.Parameters.AddWithValue("@.GI_TB_LegalFirmName", SqlDbType.VarChar, 50).Value = GI_TB_LegalFirmName.Text;

is my error...... any suggesstions?

thanks!!!

|||

You are mixing the syntax of Add, and AddWithValue.

AddWithValue takes a parameters name, and a value and it tries to guess what the datatype should be based on the value. Most of the time it works, sometimes it doesn't do exactly what you want/expect, so I recommend using Add instead and specifying the type.

This is how you would do it in VB.NET, you'll need to convert it to C#:

cmd.Parameters.Add("@.GI_RB_Div", SqlDbType.VarChar, 50).Value = GI_RB_Div.SelectedValue
cmd.Parameters.Add("@.GI_RB_ExistCust", SqlDbType.VarChar, 50).Value = GI_RB_ExistCust.SelectedValue
cmd.Parameters.Add("@.GI_TB_LegalFirmName", SqlDbType.VarChar, 50).Value = GI_TB_LegalFirmName.Text

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

Dear all,

I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @.@.IDENTITY.

The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),

Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)

However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @.@.IDENTITY", connection)

Try

NewTagID = CInt(command2.ExecuteScalar().ToString)

Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @.@.IDENTITY would be the better option.

Thankful in advance for advice.Shouldn′t you use the SELECT before the SCOPE_IDENTITY() ? Perhaps you are returning 0 (rows affected) rather than the identity Value. If no identity value is available normally NULL is given back to the caller.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the suggestion, and yes, I am already using SELECT in front of SCOPE_IDENTITY(). And there should be an identity value available because I can successfully do this in Query Analyzer:

INSERT INTO mytable (col1, col2, col3)
VALUES ('', '', 1)
GO
SELECT SCOPE_IDENTITY()
GO

And I get a 1x1 grid with unnamed column showing the new ID and two rows of messages, each saying (1 row(s) affected).

I get exactly the same result (grid as well as messages) by isusing:

INSERT INTO mytable (col1, col2, col3)

VALUES ('', '', 1)

SELECT @.@.IDENTITY

In VB2005 using CLR, I can use command.ExecuteScalar() after "select @.@.identity" to retrieve the value and it works. But "select scope_identity()" does not work to retrieve the scalar. I was confused because it is not consistent with the behavior in Query Analyzer. All in all, it is not a big deal since @.@.identity works fine but perhaps there is some fundamental difference on the context connection which could be useful to be aware of.|||

There are better way to do this, with SQL Server 2005 you are able to use the OUTPUT clause which will directly and inline′will enable you to pass back a parameter from within the query. Soo the BOL for more Information and the syntax for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

Dear all,

I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @.@.IDENTITY.

However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @.@.IDENTITY", connection)

Try

NewTagID = CInt(command2.ExecuteScalar().ToString)

Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @.@.IDENTITY would be the better option.

HTH, Jens Suessmeyer.

INSERT INTO mytable (col1, col2, col3)
VALUES ('', '', 1)
GO
SELECT SCOPE_IDENTITY()
GO

And I get a 1x1 grid with unnamed column showing the new ID and two rows of messages, each saying (1 row(s) affected).

I get exactly the same result (grid as well as messages) by isusing:

INSERT INTO mytable (col1, col2, col3)

VALUES ('', '', 1)

SELECT @.@.IDENTITY

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

@@Identity returns a decimal?

After doing an insert, I am using a System.Data.SqlClient.SqlDataReader to fetch the identity of the inserted row with the following code:

dbCommand.CommandText = "SELECT @.@.IDENTITY As LogID SET NOCOUNT OFF"
Dim dataReader As System.Data.SqlClient.SqlDataReader = dbCommand.ExecuteReader
dataReader.Read
dataReader.GetInt32(0)
dataReader.Close

The problem is that I'm getting an invalid cast exception. If I use dataReader.GetDataTypeName(0), it tells me that value is a decimal? This is odd because the identity column in the SQL table is an int (size: 4). When I use very similar code for another database on the same server, it works Stick out tongue [:P]

.
Anybody have any ideas?

According to the SQL Books Online @.@.Identity is of type numeric (not integer). This would explain why it is returned as decimal instead of an integer.
Brian

|||@.@.Identity doesnt return the ID from the same scope. You should use SCOPE_IDENTITY and furthermore, you should use a stored procedure, which does the insert AND returns the new ID.|||

Hi,
I'm adding a simple stored procedure to show a sample for SonuKapoor's note

CREATE PROCEDURE SiteCreate
(
@.SiteName as nvarchar(100)
)

INSERT INTO Site SELECT @.SiteName, 1

SELECT SCOPE_IDENTITY()

GO

Eralper
http://www.kodyaz.com

|||If we're going down the, "You don't want to do it like that route" then you shouldn't 'SELECT' the value back, you should use an OUTPUT param, and also include the SET NoCount ON, oh oh oh ;) use INSERT ([cols]) VALUES ([values]) syntax, don't select into the insert like that.

@@IDENTITY question

Hi, friends. I got a question for @.@.IDENTITY when I was working on a C#.net project.

I use "SELECT @.@.IDENTITY, @.@.ERROR" in the stored procedure to retrieve the ID column of the row just inserted, and in my C# code, I try to access it with rdr.GetInt64(0) since ID column is bigint. However, there is a error. The type is not match. I must instead use rdr.GetDecimal(0) to access @.@.IDENTITY which is a bigint.

I got confused, anybody has any idea?

Thanks.

xufff::I got confused, anybody has any idea?

Sure. Get used to reading the documentation.

@.@.IDENTITY is a (documented, btw) variable that is defined by SQL Server. It is not created based on your data type in the ID column, it is predefined.

The documentation says that the data type of the @.@.IDENTITY variable is - numeric, which translates to decimal in the CLR.

So, according to the documentation (which I checked not to post garbage - took me 10 seconds) this is simply the expected behavior.

I would think that the reason for this is that it IS legal to have a decimal based ID field. Unusual, but legal. And they decided to use numeric for the variable type, simply because this is about the "largest" data type they can use, handling everything allowed for identity fields.

Now, all you have to do is convert this decimal to a bigint - which should not give you any problems.

@@IDENTITY Programming Jitters

Hi Experts,
I have done this and it is working but am getting the jitters and it is
making me spend sleepless nights:
Dim rsMaster as New ADODB.Recordset
Dim tempRS as New ADODB.Recordset
cn.BeginTrans
rsMaster.AddNew
rsMaster.Fields!Date = txtDate.Text
rsMaster.Fields!Ref = txtRef.Text
...
rsMaster.Update
Set tempRS = cn.Execute("Select @.@.IDENTITY vno")
MastID= tempRs!vno
tempRs.Close
cn.CommitTrans
I am not using INSERT INTO because i've already done the Addnew Code and
have written many programs trapping all ado errors with error trap label and
using -2147xxxxxxxx Error Number
PLEASE help in this regar
FOUR CHEERS not THREE CHEERS to technet for the Help
Manish Sawjiani
Software Innovations & TrainingI am not sure what your question is. Are you simply looking for validation
that what you have done is OK? It looks OK except that you should be using
SCOPE_IDENTITY() and not @.@.IDENTITY. Check out BooksOnLine for the
differences if you are not sure.
Andrew J. Kelly SQL MVP
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:EB7F9EB8-2715-4FF5-9268-B99E57A83F97@.microsoft.com...
> Hi Experts,
> I have done this and it is working but am getting the jitters and it is
> making me spend sleepless nights:
> Dim rsMaster as New ADODB.Recordset
> Dim tempRS as New ADODB.Recordset
> cn.BeginTrans
> rsMaster.AddNew
> rsMaster.Fields!Date = txtDate.Text
> rsMaster.Fields!Ref = txtRef.Text
> ....
> rsMaster.Update
> Set tempRS = cn.Execute("Select @.@.IDENTITY vno")
> MastID= tempRs!vno
> tempRs.Close
> cn.CommitTrans
> I am not using INSERT INTO because i've already done the Addnew Code and
> have written many programs trapping all ado errors with error trap label
> and
> using -2147xxxxxxxx Error Number
> PLEASE help in this regar
> FOUR CHEERS not THREE CHEERS to technet for the Help
> Manish Sawjiani
> Software Innovations & Training
>|||Sir I want to be sure that the identity number i receive is OK and it will
not clash in a multi user sceneario using SQL Server 7.0
This number is important because i am going to create some transactions
against these and store it in the database.
THANKS a MILLION For your Help in Advance sir
"Andrew J. Kelly" wrote:

> I am not sure what your question is. Are you simply looking for validatio
n
> that what you have done is OK? It looks OK except that you should be usin
g
> SCOPE_IDENTITY() and not @.@.IDENTITY. Check out BooksOnLine for the
> differences if you are not sure.
> --
> Andrew J. Kelly SQL MVP
>
> "Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
> message news:EB7F9EB8-2715-4FF5-9268-B99E57A83F97@.microsoft.com...
>
>|||My recommendation is to create a stored procedure and call it from your
client app. I do not have time in this moment to post an example but if you
need one let me know.
HOW TO: Refer to a Just-Inserted Record in a SQL Server 7.0
http://support.microsoft.com/defaul...kb;en-us;319724
AMB
"Manish Sawjiani" wrote:
> Sir I want to be sure that the identity number i receive is OK and it will
> not clash in a multi user sceneario using SQL Server 7.0
> This number is important because i am going to create some transactions
> against these and store it in the database.
> THANKS a MILLION For your Help in Advance sir
> "Andrew J. Kelly" wrote:
>|||SCOPE_IDENTITY() and @.@.IDENTITY values apply only to the current connection
scope. These will not be affected by other connections.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:3B88EEFA-5989-4094-89A2-7FEBBF27D6FE@.microsoft.com...
> Sir I want to be sure that the identity number i receive is OK and it will
> not clash in a multi user sceneario using SQL Server 7.0
> This number is important because i am going to create some transactions
> against these and store it in the database.
> THANKS a MILLION For your Help in Advance sir
> "Andrew J. Kelly" wrote:
>|||As Dan pointed out they are scoped only to the current session so that is
not an issue. However if you have a trigger on the table you insert into
and it does an insert on a table with an IDENTITY column you may get the
wrong IDENTITY returned in 7.0. If you have that as a possibility I suggest
you use hand generated ID's instead. If this is a new application why are
you using 7.0?
Andrew J. Kelly SQL MVP
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:3B88EEFA-5989-4094-89A2-7FEBBF27D6FE@.microsoft.com...
> Sir I want to be sure that the identity number i receive is OK and it will
> not clash in a multi user sceneario using SQL Server 7.0
> This number is important because i am going to create some transactions
> against these and store it in the database.
> THANKS a MILLION For your Help in Advance sir
> "Andrew J. Kelly" wrote:
>|||SQL Server 7.0 doesn't have an equivalent of SQL Server 2000's
SCOPE_IDENTITY function.
IDENTITY should never be the only key of a table therefore a reliable method
to retrieve the IDENTITY for the last row inserted is to use an alternative
natural key of the table. In the following example I've assumed key_col is
the key:
/* Insert a row */
INSERT INTO YourTable (key_col, col1, ...)
VALUES (@.key_col_value, @.col1_value, ...)
/* Retrieve the IDENTITY */
SELECT @.identity_value = id_col
FROM YourTable
WHERE key_col = @.key_col
As others have indicated, you really should use TSQL stored-procs for all
data modifications in preference to updating a recordset unless you have an
exceptional reason to do otherwise.
David Portas
SQL Server MVP
--

@@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
> >> > >
> >> > >
> >> > >
>

Saturday, February 11, 2012

@@identity not working using SQL Express 2005 Sept CTP

Thursday, February 9, 2012

@@identity... am i even close here?

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

@@Identity returns a decimal?

@@IDENTITY question

@@IDENTITY Programming Jitters

@@identity not working using SQL Express 2005 Sept CTP

@@identity not working using SQL Express 2005 Sept CTP

sql rowcount

Blog Archive

About Me