By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

Need Help with Select @@ Identity

P: n/a

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
>Imports System.Data.OleDb
Partial Class adminMarcell_CatalogAdd
Inherits System.Web.UI.Page

Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArg s) Handles FormView1.ItemInserted
'Declare & Define Connection string
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/App_Data/MarcellCatalog.mdb")
'Declare & Define SQL
Dim strSQL As String = "SELECT @@Identity"

'Create connection object with connection string
Dim Conn As New OleDbConnection(strConn)

'Create command object with SQL and connection oject
Dim Cmd As New OleDbCommand(strSQL, Conn)

'Declare & Define variable for new id value
Dim newId As Integer
Conn.Open()

'Execute the command and pass the returend result to newId
newId = Cmd.ExecuteScalar()
Conn.Close()

'Declare and Define new SQL statement using parameter marker (?)
Dim strSQL2 As String = "Insert into CATALOGIMAGE (CatID, ImgID) Values (" & newId & ",?)"
Dim Cmd2 As New OleDbCommand(strSQL2, Conn)

'Add the parameter to the command, and specify its source
'Reference the DropDownList using FindControl()
Dim ddl As DropDownList = CType(FormView1.FindControl("ImageDropDown"), DropDownList)

'This is the dropdownlist's selectedValue
Cmd2.Parameters.AddWithValue("", ddl.SelectedValue)
Conn.Open()

'Execute the SQL to insert the values
Cmd.ExecuteNonQuery()
Conn.Close()

End Sub
End Class
Apr 15 '07 #1
Share this Question
Share on Google+
13 Replies


P: n/a
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" wrote:
>
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
Imports System.Data.OleDb
Partial Class adminMarcell_CatalogAdd
Inherits System.Web.UI.Page

Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArg s) Handles FormView1.ItemInserted
'Declare & Define Connection string
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/App_Data/MarcellCatalog.mdb")
'Declare & Define SQL
Dim strSQL As String = "SELECT @@Identity"

'Create connection object with connection string
Dim Conn As New OleDbConnection(strConn)

'Create command object with SQL and connection oject
Dim Cmd As New OleDbCommand(strSQL, Conn)

'Declare & Define variable for new id value
Dim newId As Integer
Conn.Open()

'Execute the command and pass the returend result to newId
newId = Cmd.ExecuteScalar()
Conn.Close()

'Declare and Define new SQL statement using parameter marker (?)
Dim strSQL2 As String = "Insert into CATALOGIMAGE (CatID, ImgID) Values (" & newId & ",?)"
Dim Cmd2 As New OleDbCommand(strSQL2, Conn)

'Add the parameter to the command, and specify its source
'Reference the DropDownList using FindControl()
Dim ddl As DropDownList = CType(FormView1.FindControl("ImageDropDown"), DropDownList)

'This is the dropdownlist's selectedValue
Cmd2.Parameters.AddWithValue("", ddl.SelectedValue)
Conn.Open()

'Execute the SQL to insert the values
Cmd.ExecuteNonQuery()
Conn.Close()

End Sub
End Class
Apr 15 '07 #2

P: n/a

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

On Sat, 14 Apr 2007 21:38:04 -0700, Tushar
<Tu****@discussions.microsoft.comwrote:
>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" wrote:
>>
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
>Imports System.Data.OleDb
Partial Class adminMarcell_CatalogAdd
Inherits System.Web.UI.Page

Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArg s) Handles FormView1.ItemInserted
'Declare & Define Connection string
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/App_Data/MarcellCatalog.mdb")
'Declare & Define SQL
Dim strSQL As String = "SELECT @@Identity"

'Create connection object with connection string
Dim Conn As New OleDbConnection(strConn)

'Create command object with SQL and connection oject
Dim Cmd As New OleDbCommand(strSQL, Conn)

'Declare & Define variable for new id value
Dim newId As Integer
Conn.Open()

'Execute the command and pass the returend result to newId
newId = Cmd.ExecuteScalar()
Conn.Close()

'Declare and Define new SQL statement using parameter marker (?)
Dim strSQL2 As String = "Insert into CATALOGIMAGE (CatID, ImgID) Values (" & newId & ",?)"
Dim Cmd2 As New OleDbCommand(strSQL2, Conn)

'Add the parameter to the command, and specify its source
'Reference the DropDownList using FindControl()
Dim ddl As DropDownList = CType(FormView1.FindControl("ImageDropDown"), DropDownList)

'This is the dropdownlist's selectedValue
Cmd2.Parameters.AddWithValue("", ddl.SelectedValue)
Conn.Open()

'Execute the SQL to insert the values
Cmd.ExecuteNonQuery()
Conn.Close()

End Sub
End Class
Apr 15 '07 #3

P: n/a
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.
--
Regards,
Tushar
MCTS - .NET 2.0 Web App
"PinkBishop" wrote:
>
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

On Sat, 14 Apr 2007 21:38:04 -0700, Tushar
<Tu****@discussions.microsoft.comwrote:
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" wrote:
>
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

Imports System.Data.OleDb
Partial Class adminMarcell_CatalogAdd
Inherits System.Web.UI.Page

Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArg s) Handles FormView1.ItemInserted
'Declare & Define Connection string
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/App_Data/MarcellCatalog.mdb")
'Declare & Define SQL
Dim strSQL As String = "SELECT @@Identity"

'Create connection object with connection string
Dim Conn As New OleDbConnection(strConn)

'Create command object with SQL and connection oject
Dim Cmd As New OleDbCommand(strSQL, Conn)

'Declare & Define variable for new id value
Dim newId As Integer
Conn.Open()

'Execute the command and pass the returend result to newId
newId = Cmd.ExecuteScalar()
Conn.Close()

'Declare and Define new SQL statement using parameter marker (?)
Dim strSQL2 As String = "Insert into CATALOGIMAGE (CatID, ImgID) Values (" & newId & ",?)"
Dim Cmd2 As New OleDbCommand(strSQL2, Conn)

'Add the parameter to the command, and specify its source
'Reference the DropDownList using FindControl()
Dim ddl As DropDownList = CType(FormView1.FindControl("ImageDropDown"), DropDownList)

'This is the dropdownlist's selectedValue
Cmd2.Parameters.AddWithValue("", ddl.SelectedValue)
Conn.Open()

'Execute the SQL to insert the values
Cmd.ExecuteNonQuery()
Conn.Close()

End Sub
End Class
Apr 15 '07 #4

P: n/a
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

On Sat, 14 Apr 2007 22:38:02 -0700, Tushar
<Tu****@discussions.microsoft.comwrote:
>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.
Apr 15 '07 #5

P: n/a
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
"PinkBishop" <pi********@hotmail.comwrote in message
news:t3********************************@4ax.com...
>
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
>>Imports System.Data.OleDb
Partial Class adminMarcell_CatalogAdd
Inherits System.Web.UI.Page

Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e
As System.Web.UI.WebControls.FormViewInsertedEventArg s) Handles
FormView1.ItemInserted
'Declare & Define Connection string
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" & Server.MapPath("~/App_Data/MarcellCatalog.mdb")
'Declare & Define SQL
Dim strSQL As String = "SELECT @@Identity"

'Create connection object with connection string
Dim Conn As New OleDbConnection(strConn)

'Create command object with SQL and connection oject
Dim Cmd As New OleDbCommand(strSQL, Conn)

'Declare & Define variable for new id value
Dim newId As Integer
Conn.Open()

'Execute the command and pass the returend result to newId
newId = Cmd.ExecuteScalar()
Conn.Close()

'Declare and Define new SQL statement using parameter marker (?)
Dim strSQL2 As String = "Insert into CATALOGIMAGE (CatID, ImgID)
Values (" & newId & ",?)"
Dim Cmd2 As New OleDbCommand(strSQL2, Conn)

'Add the parameter to the command, and specify its source
'Reference the DropDownList using FindControl()
Dim ddl As DropDownList =
CType(FormView1.FindControl("ImageDropDown"), DropDownList)

'This is the dropdownlist's selectedValue
Cmd2.Parameters.AddWithValue("", ddl.SelectedValue)
Conn.Open()

'Execute the SQL to insert the values
Cmd.ExecuteNonQuery()
Conn.Close()

End Sub
End Class

Apr 15 '07 #6

P: n/a
"PinkBishop" <pi********@hotmail.comwrote in message
news:vu********************************@4ax.com...
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.
Apr 15 '07 #7

P: n/a
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.

--
Regards,
Tushar
MCTS - .NET 2.0 Web App
"PinkBishop" wrote:
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

On Sat, 14 Apr 2007 22:38:02 -0700, Tushar
<Tu****@discussions.microsoft.comwrote:
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.
Apr 15 '07 #8

P: n/a
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>
<asp:Parameter Name="catID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="catNum" Type="Int32" />
<asp:Parameter Name="catPrice" Type="Int32" />
<asp:Parameter Name="catDescription" Type="String" />
<asp:Parameter Name="imgID" Type="Int32" />
<asp:Parameter Name="venID" Type="Int32" />
<asp:Parameter Name="subID" Type="Int32" />
<asp:Parameter Name="catID" Type="Int32" />
</UpdateParameters>
<InsertParameters>

<asp:Parameter Name="catNum" Type="Int32" />
<asp:Parameter Name="catPrice" Type="Int32" />
<asp:Parameter Name="catDescription" Type="String" />
<asp:Parameter Name="imgID" Type="Int32" />
<asp:Parameter Name="venID" Type="Int32" />
<asp:Parameter 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>
<asp:Parameter Name="venID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="venName" Type="String" />
<asp:Parameter Name="venID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="venID" Type="Int32" />
<asp:Parameter 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>
<asp:Parameter Name="subID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="SubCategory" Type="String" />
<asp:Parameter Name="subID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="subID" Type="Int32" />
<asp:Parameter 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>
<asp:DropDownList ID="ImageDropDown" runat="server"
DataSourceID="ImageDataSource"
DataTextField="imgName" DataValueField="imgID"
Style="position: static">
</asp:DropDownList><br />
<asp:Label ID="Label5" runat="server" Style="position: static"
Text="Vendor"></asp:Label>
<asp:DropDownList ID="VendorDropDown" runat="server"
DataSourceID="VendorDataSource"
DataTextField="venName" DataValueField="venID"
Style="position: static">
</asp:DropDownList><br />
<asp:Label ID="Label6" runat="server" Style="position: static"
Text="Category"></asp:Label>
<asp:DropDownList ID="CategoryDropDown" runat="server"
DataSourceID="CategoryDataSource"
DataTextField="SubCategory" DataValueField="subID"
Style="position: static">
</asp:DropDownList><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").Defau ltValue =
catNumTxt.Text
CatalogDataSource.InsertParameters("catPrice").Def aultValue =
catPriceTxt.Text
CatalogDataSource.InsertParameters("catDescription ").DefaultValue =
CatDescriptionTxt.Text
CatalogDataSource.InsertParameters("imgID").Defaul tValue =
ImageDropDown.SelectedValue
CatalogDataSource.InsertParameters("venID").Defaul tValue =
VendorDropDown.SelectedValue
CatalogDataSource.InsertParameters("subID").Defaul tValue =
CategoryDropDown.SelectedValue

CatalogDataSource.Insert()
End Sub
End Class

Apr 15 '07 #9

P: n/a
"PinkBishop" <pi********@hotmail.comwrote in message
news:t6********************************@4ax.com...
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...
Apr 15 '07 #10

P: n/a
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

On Sun, 15 Apr 2007 18:59:02 +0100, "Mark Rae"
<ma**@markNOSPAMrae.netwrote:
>"PinkBishop" <pi********@hotmail.comwrote in message
news:t6********************************@4ax.com.. .
>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...
Apr 15 '07 #11

P: n/a
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
"PinkBishop" <pi********@hotmail.comwrote in message
news:ft********************************@4ax.com...
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

On Sun, 15 Apr 2007 18:59:02 +0100, "Mark Rae"
<ma**@markNOSPAMrae.netwrote:
>>"PinkBishop" <pi********@hotmail.comwrote in message
news:t6********************************@4ax.com. ..
>>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...

Apr 15 '07 #12

P: n/a
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.

On Apr 15, 6:09 pm, "David"
<david.colliver.N...@revilloc.REMOVETHIS.comwrot e:
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"PinkBishop" <pinkbis...@hotmail.comwrote in message

news:ft********************************@4ax.com...
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
On Sun, 15 Apr 2007 18:59:02 +0100, "Mark Rae"
<m...@markNOSPAMrae.netwrote:
>"PinkBishop" <pinkbis...@hotmail.comwrote in message
news:t6********************************@4ax.com.. .
>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...- Hide quoted text -

- Show quoted text -

Apr 16 '07 #13

P: n/a
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.
>Partial Class xxxxxx_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").Defau ltValue = catNumTxt.Text
CatalogDataSource.InsertParameters("catPrice").Def aultValue = catPriceTxt.Text
CatalogDataSource.InsertParameters("catDescription ").DefaultValue = CatDescriptionTxt.Text
CatalogDataSource.InsertParameters("imgID").Defaul tValue = ImageDropDown.SelectedValue
CatalogDataSource.InsertParameters("venID").Defaul tValue = VendorDropDown.SelectedValue
CatalogDataSource.InsertParameters("subID").Defaul tValue = CategoryDropDown.SelectedValue

CatalogDataSource.Insert()

Dim catID As Integer

'create datasource object, point to db, set select statement
'mz - select changed...
Dim CatalogImageDataSource As New AccessDataSource("~/app_data/Database.mdb", "select max(catid) from [catalog] ")

' we are simply reading data, use simpler option
CatalogImageDataSource.DataSourceMode = SqlDataSourceMode.DataReader

' go and read the database... point to results in myReader object
Dim myReader As Data.IDataReader = CType(CatalogImageDataSource.Select(DataSourceSele ctArguments.Empty), Data.IDataReader)

'be sure something was read
If myReader.Read Then
'grab the value of the first column of the query...
catID = myReader(0)
End If

Dim strSQL As String = "Insert into CATALOGIMAGE (catID, imgid) Values (" & catID & ", " + ImageDropDown.SelectedValue + ")"

'new code - mz
'define the specific command you want to execute for insert..
CatalogImageDataSource.InsertCommand = strSQL
CatalogImageDataSource.InsertCommandType = SqlDataSourceCommandType.Text
' end - mz
CatalogImageDataSource.Insert()

End Sub
End Class
Apr 19 '07 #14

This discussion thread is closed

Replies have been disabled for this discussion.