Showing posts with label returning. Show all posts
Showing posts with label returning. Show all posts

Tuesday, March 27, 2012

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

@@identitiy always returns a value of 1 from sp

The data is entered correctly but identity of new record is always sent back
as 1 .
Why is this always returning a value of 1 ?
sp:
Create PROCEDURE AddPlayer
@.SQLCMD nvarchar(1000)
AS
BEGIN
EXECUTE sp_executesql @.SQLCMD
END
SELECT SCOPE_IDENTITY()Without answering the original question, let me tell
you that your approach is totally wrong.
Why you have to pass all the sql from the client side
and use the SP just to call sp_executesql ?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"TJS" <nospam@.here.com> wrote in message
news:e3R8CnXHFHA.1172@.TK2MSFTNGP12.phx.gbl...
> The data is entered correctly but identity of new record is always sent
> back as 1 .
> Why is this always returning a value of 1 ?
> sp:
> Create PROCEDURE AddPlayer
> @.SQLCMD nvarchar(1000)
>
> AS
> BEGIN
> EXECUTE sp_executesql @.SQLCMD
> END
> SELECT SCOPE_IDENTITY()
>
>|||A much more important question is why are you using dynamic SQL to
insert a row? Don't do that if you can possibly avoid it. Instead, add
parameters for each column and insert them with a static INSERT
statement.
SCOPE_IDENTITY() won't see the IDENTITY value in this example because
sp_executesql has its own scope. Use @.@.IDENTITY instead, although if
you have a trigger on the table then @.@.IDENTITY will give you the last
value inserted by a trigger.
David Portas
SQL Server MVP
--|||The SQL is generated dynamically, so I just pass it through once developed.
Seemed like a good solution.
I tried using "SELECT @.@.IDENTITY" in the procedure but it still returns a
value of 1.
Is there a way to requery the table and get the maximum vaslue of the
identity , within the same procedure and have it return that value ?|||> The SQL is generated dynamically, so I just pass it through once
developed.
> Seemed like a good solution.
Then I suggest that you should do some serious studying of development
best practices in SQL. Dynamic SQL is something to be avoided unless
you have exceptional cause to need it. There are good reasons for this.
The following article discusses some of them:
http://www.sommarskog.se/dynamic_sql.html

> Is there a way to requery the table and get the maximum vaslue of the

> identity , within the same procedure and have it return that value ?
You should always be able to retrieve the IDENTITY value after an
INSERT by using an alternate key of the table because IDENTITY should
never be the only key. Having said that, the @.@.IDENTITY method should
work in the scenario you have given so if you need more help please
post some code that will reproduce the problem for us: A CREATE TABLE
statement and some code to call the SP.
David Portas
SQL Server MVP
--|||here is the revised code
sp:
Create PROCEDURE AddPlayer
@.SQLCMD nvarchar(1000)
AS
BEGIN
EXECUTE sp_executesql @.SQLCMD
END
SELECT @.@.IDENTITY|||It works for me:
CREATE TABLE T1 (x INTEGER IDENTITY PRIMARY KEY, z INTEGER NOT NULL
UNIQUE)
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (1)'
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (2)'
Result:
(1 row(s) affected)
---
1
(1 row(s) affected)
(1 row(s) affected)
---
2
(1 row(s) affected)
David Portas
SQL Server MVP
--|||Did you check to see if there is a trigger on this table?
David Portas
SQL Server MVP
--|||no triggers
I don't see where in your sp the identity being returned ?
what did you use to return the last identity
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109627060.294495.297220@.o13g2000cwo.googlegroups.com...
> Did you check to see if there is a trigger on this table?
> --
> David Portas
> SQL Server MVP
> --
>|||I was calling your SP:
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (1)'
If you get a different result then please post some complete code that
we can actually run, including the CREATE TABLE statement and INSERT
statement(s).
David Portas
SQL Server MVP
--

Sunday, February 19, 2012

*pls help (3 day bug)* If statement not returning TRUE when it should

I've had this bug for days now, please take a look to see if you can help. Thx

I made an html table to store values that VBscript pulls from an Microsoft Access data base. I modified code from w3school to do this. Here's the link: http://www.w3schools.com/ado/ado_display.asp.
All I change are the path, table, and field names. It works fine; It prints out the DB info in a table.

However, when I add a simple if statement (poined out in the code below) to ensure that the only data that gets printed is that which has the same zip code as the zip code that the user entered into the html field, nothing gets printed. The if statement always evaluates FALSE, even though I've checked the 2 sides of the condition statement, and they match up. Here's the code:

<!DOCTYPE asp>
<% Response.Buffer = True %>

<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Inetpub\wwwroot\RH25.mdb"
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT zip, email, skills FROM members"
rs.Open sql, conn
zip = rs("zip")
zw = request.querystring("zipWanted")
response.write "zip= " & zip & " zw= " & zw
%>
<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td>
<%if zip = zw Then <--here's the if statement
Response.Write (x.value)
end if%>
</td>
<%next
rs.MoveNext%>
</tr>
<%loop
rs.close
conn.close
%>
</table></body>
</html>

Thx again.You are setting zip = rs("zip") only for the first record. In your loop to process all the records, you do not reset zip so it still has the same value as it was originally set to. Should be something like this:

<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields
zip = rs("zip")
%>
<td>
<%if zip = zw Then <--here's the if statement
Response.Write (x.value)
end if%>
</td>
<%next
rs.MoveNext%>
</tr>
<%loop

Actually, this isn't a good way to do what you want anyway. If you are only interested in records where zip = zw then you should put that condition in the WHERE clause of your SELECT.|||I try to do what you suggest, but i get this error:

Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/RH25/listings_where.asp, line 20

Where line 20 is: rs.open sql, conn

Here's lines 1-20 of the code i'm using:

<% Option Explicit %>

<% Response.Buffer = True %>

<!-- #include file="adovbs.inc" -->

<html>
<body>
<%
dim zw
dim conn
dim sql
dim rs
zw = CStr(Request.Querystring("zipwanted"))
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Inetpub\wwwroot\RH25.mdb"
sql="SELECT * FROM members WHERE zip='" & zw & "'"
set rs = Server.CreateObject("ADODB.recordset")
rs.open sql, conn

Thx again|||What is the data type of column members.zip? If it is a number then you don't want the single quotes around the zw value.