@@Identity not returning Value

I have a stored procedure that inserts a record. I call the @.@.Identity variable and assign that to a variable in my SQL statement in my asp.net page.

That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @.@.Identity field is no longer being returned.

As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?

When I pass the value of the TicketID variable to a text field after the insert, it gives me "@.TicketID".

Anyone have any ideas?

CREATE PROCEDURE [iguser].[newticket]
(
@.Category nvarchar(80),
@.Description nvarchar(200),
@.Detail nvarchar(3000),
@.OS nvarchar(150),
@.Browser nvarchar(250),
@.Internet nvarchar(100),
@.Method nvarchar(50),
@.Contacttime nvarchar(50),
@.Knowledge int,
@.Importance int,
@.Sendcopy bit,
@.Updateme bit,
@.ClientID int,
@.ContactID int,
@.TicketID integer OUTPUT
)
 AS
INSERT INTO Tickets
(
Opendate,
Category,
Description,
Detail,
OS,
Browser,
Internet,
Method,
Contacttime,
Knowledge,
Importance,
Sendcopy,
Updateme
)
VALUES
(
Getdate(),
@.Category,
@.Description,
@.Detail,
@.OS,
@.Browser,
@.Internet,
@.Method,
@.Contacttime,
@.Knowledge,
@.Importance,
@.Sendcopy,
@.Updateme
)
SELECT
@.TicketID = @.@.Identity
INSERT INTO Contacts_to_Tickets
(
U2tUserID,
U2tTicketID
)
VALUES
(
@.ContactID,
@.TicketID
)
INSERT INTO Clients_to_Tickets
(
C2tClientID,
C2tTicketID
)
VALUES
(
@.ClientID,
@.TicketID
)

Fixed the problem, it was with my .net code|||The best practice is to constrain, you should use IDENT_CURRENT('Tickets') instead of @.@.IDENTITY when you are inserting into multiple tables. IDENT_CURRENT gives you the last Identity generated in a specific table, as @.@.IDENTITY has no constraint and returns the last Identity of any table in the session or scope.

Based on your execution @.@.IDENTITY will work, but I figured I would throw this out there anyway.

@@Identity c# help...

I am trying to follow other examples I have seen on the site, and am still getting the

Must declare the scalar variable "@.@.INDENTITY".

string sqlAdd =string.Format("INSERT INTO " + siteCode +"_campaign_table (campaign_name, prod_id, type) "

+

"VALUES('{0}', '{1}', '{2}'); SELECT @.@.INDENTITY", campaignName, prodID, type);SqlCommand comAdd =newSqlCommand(sqlAdd, con);

comAdd.CommandType =

CommandType.Text;

con.Open();

//comAdd.ExecuteNonQuery();int identity;

identity =

Decimal.ToInt32((decimal)comAdd.ExecuteScalar());

lblErrorMessageAdd.Text = identity.ToString();

con.Close();

You have a typo. It should be@.@.IDENTITY

Also, your code should use parameters; what you have currently (placeholders for string replacement) is insecure and subject to SQL injection attacks.|||thanks, Corrected a few things there and works as intended.|||Actually, since you are using SQL Server, the best idea is to SELECT SCOPE_IDENTITY(). See, for example, this blog post for an explanation:SCOPE_IDENTITY() and @.@.IDENTITY Demystified. I should have pointed this out on my last post; I am sorry for missing it.

@ local variable makes query slower?

Hi all,

I have a query that scans huge table consists of 8 or more millions
records. The funny thing is that if I use the query with local
variable, the query takes more than 1 minutes, whereas if I hard code
the value into the query, it takes about 1 second. Here are the
queries:

WITH VARIABLE:
------

DECLARE @.i_StartDate DATETIME
DECLARE @.i_EndDate DATETIME
SET @.i_StartDate = '2004-04-26'
SET @.i_EndDate = '2004-04-28'

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)

...And some other conditions

-------

WITHOUT VARIABLE:

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')

...And some other conditions

-------

The later one runs significantly faster than the first one. I've
isolated the problem at the local variable @.i_StartDate and
@.i_EndDate. Can somebody help me out, Please...

Thank you,
Michelle."Michelle" <michelletran@.harmonyremote.com> wrote in message
news:56c5b7ab.0404260656.281cfc40@.posting.google.c om...
> Hi all,
> I have a query that scans huge table consists of 8 or more millions
> records. The funny thing is that if I use the query with local
> variable, the query takes more than 1 minutes, whereas if I hard code
> the value into the query, it takes about 1 second. Here are the
> queries:
> WITH VARIABLE:
> ------
> DECLARE @.i_StartDate DATETIME
> DECLARE @.i_EndDate DATETIME
> SET @.i_StartDate = '2004-04-26'
> SET @.i_EndDate = '2004-04-28'
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)
> ...And some other conditions
> -------
> WITHOUT VARIABLE:
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')
> ...And some other conditions
> -------
> The later one runs significantly faster than the first one. I've
> isolated the problem at the local variable @.i_StartDate and
> @.i_EndDate. Can somebody help me out, Please...
> Thank you,
> Michelle.

This may be an example of parameter sniffing - see this post, for example,
which describes an almost identical case:

http://groups.google.com/groups?hl=...ftngp13.phx.gbl

Simon|||Thanks Simon.
Michelle.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

(system) Variable that holds the record count of a result set?

Hello
is there a variable that is available to me that contains the number
of rows contained in a dataset return from a database call?

have a class that runs a stored proc and returns a dataset/resultset
looking to simply assign an integer this value if it is possible

i'm using (learning) vb.net and sql server

thanks in advance

If you are using a dataset, you can get the number of rows with this syntax:

DataSet1.tables(0).rows.count (assuming your Dataset returns only 1 resultset. If you have more than 1, just replace the zero with whatever index you need.)

If you use a DataReader, which tends to be faster, this property is not available, unfortunately.|||

hello. thank you for the reply. that is exactly what i was looking for (DataSet1.tables(0).rows.count). a question about how to reference this from the codebehind...

i have a codebehind that Dims a class, the class returns the dataset, in the codebehind i have a line likeDropDownList1.DataSource = myClass.function1(parm1) to populate the dropdown with the data from the dataset. i get how i could reference this value in the class code, but how would i reference it in the codebehind? is there a way. would i have to do it in the class function and store it in a session variable or something? would be like to reference it directly in the codebehind as that is where i would be using the value.

thanks again.

|||

i believe i have figured my question out (yup, i'm new), but if you have any comments i'd appreciate it

instead of directly coding the line as DDL1.DataSource = myClass.function1(parm1)

i modified the code to have...

Dim ds as dataSet = myClass.function1(parm1)
Dim dsCnt as integer = ds.Tables(0).Rows.Count()
DDL1.DataSource = ds

this seemed to get me what i was after. if there is a more efficient or elegant way to do this, i'm all ears

|||The way you've done it is fine. An alternative is to create a property of type dataset in your class, then fill that property in your function. The code behind could then reference your property.

Public Class myClass

Private mMyDataset as dataset

Public Sub New()
MyBase.new()
End Sub

Property MyDataset() As dataset
Get
Return mMyDataset
End Get
Set(ByVal Value As dataset)
mMyDataset = Value
End Set
End Property

Public Sub function1(param) 'can change to a sub since you are filling a property rather than using a return value
myDataset = Database call goes here
End Sub

From your code behind:

dim objClass as new myClass()

with objClass
.function1(param)
DDL1.datasource = .myDataset
DDL1.databind
end with

(sub)case syntax Question

Hi,

How can i perform a subcase ? I need to do a subcase because when a choose one condition (cdu_integrador='1'), there is another variable that could influence the result of cdu_parcerias (tipoterceiro ='TP' or not).

I've tried 2 ways , one using "AND" on the WHEN line (when 1 and terceiro=....) and use cascading case (when 1 (case tipoterceiro when 'TP'..)..) ... both ways didnt worked...

i get a

Incorrect syntax near the keyword 'CASE'.

ANY IDEA ?

CDU_PARCERIAS =

CASE CDU_INTEGRADOR

WHEN '1' AND TIPOTERCEIRO<>'TP' THEN TIPOTERCEIRO+' + TP'

ELSE TIPOTERCEIRO

END

CDU_PARCERIAS =

CASE CDU_INTEGRADOR

WHEN '1'

CASE TIPOTERCEIRO

WHEN 'TP' THEN TIPOTERCEIRO

ELSE TIPOTERCEIRO+' + TP'

END

ELSE TIPOTERCEIRO

END

Try it like:

CDU_PARCERIAS =
CASE WHEN CDU_INTEGRADOR = '1'
AND TIPOTERCEIRO <> 'TP'
THEN TIPOTERCEIRO + ' + TP'
ELSE TIPOTERCEIRO
END

|||

Use as follow as,

CDU_PARCERIAS =

CASE CDU_INTEGRADOR

WHEN '1' THEN

CASE TIPOTERCEIRO

WHEN 'TP' THEN TIPOTERCEIRO

ELSE TIPOTERCEIRO +' + TP'

END

ELSE TIPOTERCEIRO

END