Re: Need Help with Select @@ Identity


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
<Tushar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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.FormViewInsertedEventArgs) 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

.

Relevant Pages