Need Help with Select @@ Identity

[PinkBishop]

I am using VS 2005 with a formview control trying to insert a record
to my access db. The data is submitted to the main table no problem,
but I need to carry the catID to the bridge table CatalogImage where
imgID also needs to be placed.

Below is my code behind to carry the catID using the Select @@Identity
and insert imgID to the bridge table.

No data is being entered into the bridge table.

Your help is greatly appreciated.

TRU

 

[Guest]

Hi ,
First of all you need to understand that "SELECT @@IDENTITY " is a
connection specific command. It means that if you take two connections to the
database , Insert a single row in a table and run this command using
different connections the result would be different.

So you should do the following to solve your problem :
1. Create a database connection.
2. Create a command object.
3. Open the connection.
4. Insert a row
5. Execute "SELECT @@IDENTITY " without closing or creating a new connection
6.. Performs rest of the steps as you were doing.


Another problem with the following line "newId = Cmd.ExecuteScalar()" .
newId is integer and ExecuteScalar() returns an object. Use need to convert
that to integer. You can do something like this
Int32.Parse(Cmd.ExecuteScalar().ToString()) .

I hope this solves your problem. Feel free to get back in case of any
difficulties.

Regards,
Tushar

 

[PinkBishop]

Let me start by saying I am very new to VS and .Net. I guess i am
jumping in with both feet to complete this first project. With this
form to be the last part of the project.

Unless I do not understand you, which is a good possiblity, I believe
I have accomplished the first four items of your list. with my form
page. As explained below, I am able to get my data submitted into the
first table with my form. It was my hope through help from another
board that a code behind doing the select @@identity would pass the
catID and imgID to the bridge table. Isn't the code behind steps 5
and 6?

Thanks,

TRU

 

[Guest]

Hi
It's ok. You will learn soon.
You are right . You performed steps 1-4 and then 5-6. But the problem is
that all these steps should be performed in a single connection.
From the code that you have written what i feel is that you
open a connection
perform steps 1-4
close a connection

open a connection
perform steps 5-6...
close the connection

In this case "select @@IDENTITY" won't work because it is connection
specific. All the steps need to be performed under a single connection
object.
The steps 5-6 are been performed when the item has already been inserted.
When you create a new connection to the database , SELECT @@IDENTITY doesn't
work. It doesn't know which value was inserted last.

Just try and perform all these steps in a single go and I am sure it'll
work. Even then if you face a problem feel free to get back to me. I'll
explain it to you through mail or chat.

 

[PinkBishop]

Tushar,

I understand what you are saying about the need for everthing to
happen under a single connection.

Right now I have steps 1-4 completed in the .aspx file then my
attempt was to complete 5-6 with the .aspx.vb code behind. Now I
realize that can't be done.

How do I complete this in one connection?

or


Is there a way to get the primary key of the last record inserted into
table 1 so to insert into table 2?

Now that I think more, I still need the value from the dropdown as
well.

So, after the insert into table 1 is there a way to call the catID and
imgID from Table 1 and insert into table 2?

There will not be a risk of getting the wrong catID as there will
always be only 1 person entering into the db at a time. I do say
always lightly though.

TRU

 

[Eliyahu Goldin]

Run the first insert in the same batch with the select that will return you
the id:

"INSERT ...;SELECT SCOPE_IDENTITY()"

Then use the returned id in the second insert.

--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net

 

[Mark Rae]

Is there a way to get the primary key of the last record inserted into
table 1 so to insert into table 2?

Don't even think of trying to do this... It will almost certainly fail as
soon as you have more than one user on the system... Use Eliyahu's
suggestion.

 

[Guest]

Hi Pink,
There is a work around way. But that is a bad practise. As you said only
one user will be using this application you can do this.
Insteat of writing SELECT @@IDENTITY in your code , you can write SELECT TOP
CATID FROM Categories . Since it is an auto increment , you'll always get the
highest value which would be same like SELECT @@IDENTITY . But this would
only work if one user is using the app.
This is a bad practise. I would suggest you to follow SELECT @@IDENTITY and
that too in a single batch.

 

[PinkBishop]

Ok... I am scrapping the hole idea of the Formview and going with a
simple form with parameters in the code behind. As the code below
shows the data is submitted off the click of the button to table 1
(Catalog)

Where and how do I implement the Select @@Identity.

I need catID and imgID passed to table 2 CatalogImage


--------------.ASPX PAGE-------------


<%@ Page Language="VB" AutoEventWireup="false"
CodeFile="CatalogAdd.aspx.vb" Inherits="admin_CatalogAdd" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
&nbsp;<br />
&nbsp;<asp:AccessDataSource ID="CatalogDataSource"
runat="server" DataFile="~/App_Data/DATABASE.mdb"
DeleteCommand="DELETE FROM [Catalog] WHERE [catID] = ?"
InsertCommand="INSERT INTO [Catalog] ([catNum], [catPrice],
[catDescription], [imgID], [venID], [subID]) VALUES (?, ?, ?, ?, ?,
?)"
SelectCommand="SELECT [catID], [catNum], [catPrice],
[catDescription], [imgID], [venID], [subID] FROM [Catalog]"
UpdateCommand="UPDATE [Catalog] SET [catNum] = ?,
[catPrice] = ?, [catDescription] = ?, [imgID] = ?, [venID] = ?,
[subID] = ? WHERE [catID] = ?">
<DeleteParameters>
<asparameter Name="catID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asparameter Name="catNum" Type="Int32" />
<asparameter Name="catPrice" Type="Int32" />
<asparameter Name="catDescription" Type="String" />
<asparameter Name="imgID" Type="Int32" />
<asparameter Name="venID" Type="Int32" />
<asparameter Name="subID" Type="Int32" />
<asparameter Name="catID" Type="Int32" />
</UpdateParameters>
<InsertParameters>

<asparameter Name="catNum" Type="Int32" />
<asparameter Name="catPrice" Type="Int32" />
<asparameter Name="catDescription" Type="String" />
<asparameter Name="imgID" Type="Int32" />
<asparameter Name="venID" Type="Int32" />
<asparameter Name="subID" Type="Int32" />
</InsertParameters>
</asp:AccessDataSource>
<asp:AccessDataSource ID="ImageDataSource" runat="server"
DataFile="~/App_Data/DATABASE.mdb"
SelectCommand="SELECT [imgID], [imgName] FROM [Images]
ORDER BY [imgName]"></asp:AccessDataSource>
</div>
<asp:AccessDataSource ID="VendorDataSource" runat="server"
DataFile="~/App_Data/DATABASE.mdb"
DeleteCommand="DELETE FROM [Vendors] WHERE [venID] = ?"
InsertCommand="INSERT INTO [Vendors] ([venID], [venName]) VALUES (?,
?)"
SelectCommand="SELECT [venID], [venName] FROM [Vendors]
ORDER BY [venName]" UpdateCommand="UPDATE [Vendors] SET [venName] = ?
WHERE [venID] = ?">
<DeleteParameters>
<asparameter Name="venID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asparameter Name="venName" Type="String" />
<asparameter Name="venID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asparameter Name="venID" Type="Int32" />
<asparameter Name="venName" Type="String" />
</InsertParameters>
</asp:AccessDataSource>
<asp:AccessDataSource ID="CategoryDataSource" runat="server"
DataFile="~/App_Data/DATABASE.mdb"
DeleteCommand="DELETE FROM [SubCategory] WHERE [subID] =
?" InsertCommand="INSERT INTO [SubCategory] ([subID], [SubCategory])
VALUES (?, ?)"
SelectCommand="SELECT [subID], [SubCategory] FROM
[SubCategory] ORDER BY [SubCategory]"
UpdateCommand="UPDATE [SubCategory] SET [SubCategory] = ?
WHERE [subID] = ?">
<DeleteParameters>
<asparameter Name="subID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asparameter Name="SubCategory" Type="String" />
<asparameter Name="subID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asparameter Name="subID" Type="Int32" />
<asparameter Name="SubCategory" Type="String" />
</InsertParameters>
</asp:AccessDataSource>
<br />
<br />
<asp:Label ID="Label1" runat="server" Style="position: static"
Text="Item"></asp:Label>
<asp:TextBox ID="catNumTxt" runat="server" Style="position:
static"></asp:TextBox><br />
<asp:Label ID="Label2" runat="server" Style="position: static"
Text="Price"></asp:Label>
<asp:TextBox ID="catPriceTxt" runat="server" Style="position:
static"></asp:TextBox><br />
<asp:Label ID="Label3" runat="server" Style="position: static"
Text="Description"></asp:Label>
<asp:TextBox ID="CatDescriptionTxt" runat="server"
Style="position: static"></asp:TextBox><br />
<asp:Label ID="Label4" runat="server" Style="position: static"
Text="Image"></asp:Label>
<aspropDownList ID="ImageDropDown" runat="server"
DataSourceID="ImageDataSource"
DataTextField="imgName" DataValueField="imgID"
Style="position: static">
</aspropDownList><br />
<asp:Label ID="Label5" runat="server" Style="position: static"
Text="Vendor"></asp:Label>
<aspropDownList ID="VendorDropDown" runat="server"
DataSourceID="VendorDataSource"
DataTextField="venName" DataValueField="venID"
Style="position: static">
</aspropDownList><br />
<asp:Label ID="Label6" runat="server" Style="position: static"
Text="Category"></asp:Label>
<aspropDownList ID="CategoryDropDown" runat="server"
DataSourceID="CategoryDataSource"
DataTextField="SubCategory" DataValueField="subID"
Style="position: static">
</aspropDownList><br />
<br />
<asp:Button ID="Button1" runat="server" Style="position:
static" Text="Submit" />
</form>
</body>
</html>




--------------.VB CODE BEHIND-------------


Partial Class admin_CatalogAdd
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
CatalogDataSource.InsertParameters("catNum").DefaultValue =
catNumTxt.Text
CatalogDataSource.InsertParameters("catPrice").DefaultValue =
catPriceTxt.Text
CatalogDataSource.InsertParameters("catDescription").DefaultValue =
CatDescriptionTxt.Text
CatalogDataSource.InsertParameters("imgID").DefaultValue =
ImageDropDown.SelectedValue
CatalogDataSource.InsertParameters("venID").DefaultValue =
VendorDropDown.SelectedValue
CatalogDataSource.InsertParameters("subID").DefaultValue =
CategoryDropDown.SelectedValue

CatalogDataSource.Insert()
End Sub
End Class

 

[Mark Rae]

Where and how do I implement the Select @@Identity.

This has already been explained to you at least twice...

You simply include your insert statement and your SELECT @@IDENTITY
statement in the same SQL batch:

INSERT.....
SELECT @@IDENTITY

Then, when you run your SQL, it will first do the insert and then return you
the ID that you require...

 

[PinkBishop]

Yes, but I do not understand how or where the code is placed. This is
why I posted my code hoping somebody could tell me exactly how to
implement it.

TRU

 

[David]

An example...

This is done in the code behind.


conn.open();

sql = "insert into myTable (field1, field2) values (value1, value2)";

cmd = new SqlCommand(sql, conn);

cmd.executeNonQuery();

// All the above is the first part. Note, conn HAS NOT been closed, so is
still in reference.

// The line below will get your identity, as you are still in the same
connection.

cmd = SqlCommand("select @@Identity", conn)

int MyIdent = cmd.ExecuteScalar();

// (Note my syntax may be slightly wrong).




An alternative is...

conn.open();
sql = "insert into myTable (field1, field2) values (value1, value2); select
@@Identity";
cmd = new SqlCommand(sql, conn);

int MyIdent = cmd.ExecuteScalar();

// Note, I ran both sql statements togethor. The insert followed by a
semi-colon followed by a select.



--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available

 

[Russell]

Also worth pointing out the difference between @@IDENTITY and
SCOPE_IDENTITY()

@@IDENTITY returns the last ID generated for an identity column in any
table.

SCOPE_IDENTITY returns the last ID generated in the caller scope.

The main difference is that if your statement inserts a table with an
identity column, and that table has a trigger that populates another
table with an identity column, @@IDENTITY will return the ID generated
for the table populated by the trigger, while SCOPE_IDENTITY() will
return the ID for the table specified in the insert statement.

Also, just to reiterate since I'm still not sure it's clear to
PinkBishop, neither of these can return any value until AFTER an
insert to a table with an identity column.

 

[PinkBishop]

Thanks to all who gave their input.

Unfortunately I was not able to figure out the @@Identity.

Blame it on my ineptitude.

This is my first application built with VS and the ASP.NET framework.

For 10 years I relied on FrontPage ( no need to heckle me, I can
imagine the thoughts going through the heads of all you seasoned
programmers) and hacking up some asp scripts.

Well I am finally jumping into VS and asp.net with two feet to say
good bye to FP and move on to Expression Web and VS.

So... you can probably now understand my inability to follow your
directions in this thread.

Anyways.. my solution is below. I hope somebody finds this thread
usefull as you all shared a lot of valuable information.

Feel free to share any comments about the code below. Overall, it
works and it meets my needs.

Thanks again.