Difficulties getting @@identity to work in transaction

I am having some problems wit a form where i need to insert a record into a table and then get the id of the record i just inserted and use it to insert a record in another table for a many to many relationship. The code is below (I cut out as much as i could to make it more readable).

The erro message i get is this:

Error saving file ATLPIXOFC.txt Reason: System.Data.SqlClient.SqlException: Prepared statement '(@.FileName varchar(13),@. i' expects parameter @.FileID, which was not supplied. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microvit_Document_Product_Document_Add_v2.insertDocumentToDB() in e:\inetpub\wwwroot\Microvit\Document\Product_Document_Add_v2.aspx.vb:line 127 at Microvit_Document_Product_Document_Add_v2.btnInsert_Click(Object sender, EventArgs e) in e:\inetpub\wwwroot\Microvit\Document\Product_Document_Add_v2.aspx.vb:line 37

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, ParameterDirection.Output))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute 1st Command

'****************************************************************************

myCommand.CommandText ="INSERT INTO [Files] ([FileName) VALUES (@.FileName); SELECT @.FileID = @.@.Identity;"

myCommand.ExecuteNonQuery()

'****************************************************************************

' Execute 2nd Command

'****************************************************************************

'myCommand.Parameters("@.FileID").Value = ddlProduct.SelectedValue

myCommand.CommandText ="INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, @.FileID)"

myCommand.ExecuteNonQuery()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

Has nothing to do with transactions.

You defined the @.FileID parameter as such:

myCommand.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, ParameterDirection.Output))

Note, you said output. The second command expects it as input.

You can fix this a number of ways. Use two different mycommand objects with different parameters, or set variables to the parameters, clear and rebuild the parameters, and reset the parameter values from your variables, change your first query to accept the same parameters, or just use a single command.

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute Command

'****************************************************************************

myCommand.CommandText ="INSERT INTO [Files] ([FileName) VALUES (@.FileName) INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, SCOPE_IDENTITY())"

myCommand.ExecuteNonQuery()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

|||

Or if you really need the identity back in your program:

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute Command

'****************************************************************************

myCommand.CommandText ="SET NOCOUNT ON INSERT INTO [Files] ([FileName) VALUES (@.FileName) SET NOCOUNT OFF SELECT SCOPE_IDENTITY() SET NOCOUNT ON INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, SCOPE_IDENTITY()) SET NOCOUNT OFF"

dim MyIdentity=myCommand.ExecuteScaler()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

|||Oops, I guess I should mention that you can of course, create a stored procedure to do the insert as well which takes all the things you want to insert into your various tables as parameters.sql