Link to home
Create AccountLog in
Avatar of jpadkins49
jpadkins49

asked on

ASP.net return autonumber field after insert

I have code below that inserts form fields into a table in my sql server database. I have tried different methods, but I have head that the @@identity method is the best. I would like to perform the insert statement in the code below and then assign @@identity field for the autonumber to a session variable such as Session("id") = @@identity. Could someone please show me how to do this. It would be greatly appreciated.


Dim dashdatasource As New SqlDataSource
        dashdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("wvda_credentialsConnectionString1").ToString
       
        'Initialize insert command.
        dashdatasource.InsertCommandType = SqlDataSourceCommandType.Text
        dashdatasource.InsertCommand = "Insert INTO Requird_Course (Course_num, Description, Category, Fixed_date, Renewal_Type ) values (@Course_num, @Description, @Category, @Fixed_date, @Renewal_Type)"
        
        'Create Parameters for Insert Command.
        dashdatasource.InsertParameters.Add("Course_num", Course_num.Text)
        dashdatasource.InsertParameters.Add("Description", description.Text)
        dashdatasource.InsertParameters.Add("Category", Category.SelectedValue.ToString)
        dashdatasource.InsertParameters.Add("Fixed_date", Fixed_Date.SelectedValue.ToString)
        dashdatasource.InsertParameters.Add("Renewal_Type", Renewal_Type.SelectedValue.ToString)     
        
        'Create Variable to hold the number of rows that was updateed. It will automatically increment based on #rows.
        Dim rowsaffected As Integer = 0
        
        
        'Error Handling Issues with fixed date frame.
        If Fixed_Date.SelectedValue = "No" Then
            Renewal_Type.SelectedValue = "none"
            Renewal_Type.Visible = False
            
        End If
        
        'Try to Insert the data and catch any problems that might arise.
        Try
            rowsaffected = dashdatasource.Insert()
            
            'Redirect to the main index.aspx page.
            Response.Redirect("~/index.aspx")
        Catch ex As Exception
            
            
        End Try
        dashdatasource = Nothing

Open in new window

Avatar of talker2004
talker2004
Flag of United States of America image

You will have to create a stroed procedure with an output parameter. Set the output parameters value to the ScopeIdentity()
Avatar of chapmandew
Why aren't you using stored procedures to run queries against your database.  It would be so easy to have a stored procedure give you the newest identity value after it has ran...as an output parameter.

Also, DO NOT use @@IDENTITY to determine the last identity value inserted.  Use SCOPE_IDENTITY() instead.  If you use @@IDENTITY are you eventually going to run into problems if there are a lot of inserts on the table, or if there are triggers on the table that insert records into other tables.   User SCOPE_IDENTITY() instead.

Having said that, will something like this work?


Dim dashdatasource As New SqlDataSource
        dashdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("wvda_credentialsConnectionString1").ToString
       
        'Initialize insert command.
        dashdatasource.InsertCommandType = SqlDataSourceCommandType.Text
        dashdatasource.InsertCommand = "Insert INTO Requird_Course (Course_num, Description, Category, Fixed_date, Renewal_Type ) values (@Course_num, @Description, @Category, @Fixed_date, @Renewal_Type)  SET @RowsAffected = SCOPE_IDENTITY()"
        
        'Create Parameters for Insert Command.
        dashdatasource.InsertParameters.Add("Course_num", Course_num.Text)
        dashdatasource.InsertParameters.Add("Description", description.Text)
        dashdatasource.InsertParameters.Add("Category", Category.SelectedValue.ToString)
        dashdatasource.InsertParameters.Add("Fixed_date", Fixed_Date.SelectedValue.ToString)
        dashdatasource.InsertParameters.Add("Renewal_Type", Renewal_Type.SelectedValue.ToString)  
 
		'New Parmeter for the output...will probably have to tell the object that it is an output param   
         dashdatasource.InsertParameters.Add("RowsAffected", Renewal_Type.SelectedValue.ToString)  
 
        'Create Variable to hold the number of rows that was updateed. It will automatically increment based on #rows.
        Dim rowsaffected As Integer = 0
        
        
        'Error Handling Issues with fixed date frame.
        If Fixed_Date.SelectedValue = "No" Then
            Renewal_Type.SelectedValue = "none"
            Renewal_Type.Visible = False
            
        End If
        
        'Try to Insert the data and catch any problems that might arise.
        Try
            rowsaffected = dashdatasource.Insert()
            
            'Redirect to the main index.aspx page.
            Response.Redirect("~/index.aspx")
        Catch ex As Exception
            
            
        End Try
        dashdatasource = Nothing

Open in new window

Here is the stored procedure

CREATE PROCEDURE dbo.InsertCategory
  @CategoryName nvarchar(15),
  @IdentityValue int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @IdentityValue = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
Actually, that is not true....your procedure doesn't take into affect the other parameters that the user needs....


create procedure usp_myinsert
(
@Course_num INT, 
@Description VARCHAR(100),
@Category VARCHAR(20),
@Fixed_date DATETIME, 
@Renewal_Type VARCHAR(20),
@RowsAffected INT OUTPUT
)
AS
BEGIN
Insert INTO Requird_Course (Course_num, Description, Category, Fixed_date, Renewal_Type ) 
values (@Course_num, @Description, @Category, @Fixed_date, @Renewal_Type)  
 
SET @RowsAffected = SCOPE_IDENTITY()
 
 
END

Open in new window

Sorry that is just a usage example of the stored procedure. If you want to use it you will have to change it to apply to your database schema.

Let me ask you champandew your RowsAffected parameter does not appear to store the rows affected. It looks like to me it is going to store the identity field genreated by the insert statement. Don't you think that the name of that parameter is a little bit misleading.

Yes, I originally changed it to NewID when I posted by last code sample, but changed it back to what I had before because I didn't want to confuse the reader with a new param.  Good catch though.
Yeah, well we are working for points you know and you got to be pretty competitive in order to score a few.

Avatar of jpadkins49
jpadkins49

ASKER

I usually use stored procedures for all output. I was just asking a general question to to tailor my code. With the code sample above, then how would we call that scope identity to a session variable so that I can pass it on to the next page. Thanks for the help and quick replies guys. You are both doing a good job.
If I utulize the store procedure above, then how to I return this value to a session variable so that I can pass the @@Rowsaffected or @rowscount. Disregard the previous post. I was not specific in the previous post. I have rewritten my code to utilize the stored procedure, but I am at a loss on how to return the value back to a session variable.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Gennerally you are going to use a dataset and pass it through the update method of your data adapter. Considering your parameters are setup correctly your dataset will reflect the changes as soon as it is passed through the update method.
try

da.update(ds)

session("MyIdentityValue") = ds.tables(0).rows(0)("MyIdentityColumn")

catch

end try
Thanks for all the help. Here is a sample of all of the code that I used to test. I used the same stored procedure as above and then called this procedure in the code below.


Dim cs As String = ConfigurationManager.ConnectionStrings("trying").ToString()
 
        Dim myConnection As New SqlConnection(cs)
        myConnection.Open()
 
        Dim myCommand As New SqlCommand("getid", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure
 
        'Create a SqlParameter object to hold the output parameter value
        Dim retValParam As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
 
        'IMPORTANT - must set Direction as ReturnValue
        retValParam.Direction = ParameterDirection.ReturnValue
 
        'Finally, add the parameter to the Command's Parameters collection
        myCommand.Parameters.Add(retValParam)
        myCommand.Parameters.AddWithValue("course_year", make.Text)
        myCommand.Parameters.AddWithValue("course_make", make.Text)
        'Call the sproc...
        Dim reader As SqlDataReader = myCommand.ExecuteReader()
 
        'Now you can grab the output parameter's value...
        Dim retValParam1 As Integer = Convert.ToInt32(retValParam.Value)
 
 
        Label3.Text = retValParam1
 
        Session("passing") = retValParam1
        Response.Redirect("default2.aspx")
    End Sub

Open in new window

Thank you for your quick response. I appreciate the links you provided and you answered my question with specific detail to my scenario.
Does it work as you need it to?
...I guess so.  Glad to help.  :)

Tim