how can i display the last insert id into a textbox on my web form?

here is the code i have so far...

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim MyConnection As SqlConnection
MyConnection = New SqlConnection() 'declare new connection object
MyConnection.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myfirstdatabase.mdf;Integrated Security=True;User Instance=True"
MyConnection.Open() 'open connection to database
Dim MyCommand As New SqlCommand() 'declare new command object
MyCommand.Connection = MyConnection

MyCommand.CommandText = "INSERT INTO mytemptable (firstname,surname) VALUES(@Name1,@Name2)"
MyCommand.Parameters.Add(New SqlParameter("@Name1", TextBox1.Text.ToString))
MyCommand.Parameters.Add(New SqlParameter("@Name2", TextBox2.Text.ToString))
MyCommand.ExecuteNonQuery()
MyCommand.CommandText = "SELECT SCOPE_IDENTITY()"
'MyCommand.ExecuteScalar()
MyConnection.Close()
MyCommand = Nothing
End Sub

Recommended Answers

All 2 Replies

Use Stored Procedures for inserting values and use @@Identity to get the last inserted ID (hope this is your primary key)

Cheers
Shasur

Hi Guys

I have a stored procedure where I am adding a new record to a table and I have RETURN SCOPE_IDENTITY() at the end...on testing it returns the ID value that I want. However I want to be able to use this ID value and pass it to a variable within my C# code which I could then use to pass into another stored procedure to add data to a new table.

command.ExecuteNonQuery();
            command.ExecuteScalar();
            i_VehicleID = (int)command.ExecuteScalar();

The code snippet above shows how I have tried to store the integer value in a variable.

I would be so grateful for some assistance :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.