Primary key of the record just inserted

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Primary key of the record just inserted

Author

Topic

KushKashyap
Starting Member

3 Posts

Posted - 2009-07-15 : 13:41:54

Hello there...

I have an MS Access database table.. with "email" as my primary key... Is it possible that i can retrieve the "email" of the record i have just inserted... I have seen a lot of articles about Scope_Identity and @@Identity but all of them were for Autonumber or autoincrementing columns.. IS is it so... or these functions can be used for any type of primary keys..

Since i am a novice, so can you also please share some code snippet of how to do it.

My table is

emailid - varchar(20)

name - varchar(50)

address - varchar(Max)

city - varchar(20)

state - varchar(20)

........
And i am inserting the records as

Private Shared Sub InsertCustomer(ByVal cust As Customer)

Dim cmd As New OleDbCommand()
cmd.Connection = con
cmd.Transaction = tran

Dim cmdCount As New OleDbCommand()
cmdCount.Connection = con
cmdCount.Transaction = tran

cmdCount.CommandText = "SELECT COUNT(*) FROM Customers where email='" & cust.Email.ToString() & "'"
Dim count As Integer = Convert.ToInt32(cmdCount.ExecuteScalar())

If count = 0 Then
cmd.CommandText = "INSERT INTO Customers " + "(lastname, firstname, " _
+ "address, city, state, pincode,phone, email) " _
+ "VALUES (@LastName, @FirstName, @Address, @City, @State, @PinCode, @PhoneNumber, @Email)"

cmd.Parameters.AddWithValue("@LastName", cust.LastName)
.......................................
.......................................
cmd.Parameters.AddWithValue("@PhoneNumber", cust.PhoneNumber)
cmd.Parameters.AddWithValue("@Email", cust.Email)
cmd.ExecuteNonQuery()
Else

cmd.CommandText = "UPDATE Customers SET lastname = @LastName, firstname = @FirstName, " _
+ "address = @Address, city = @City,state = @State, pincode = @PinCode, phone = @PhoneNumber WHERE email = @Email "
cmd.Parameters.AddWithValue("@LastName", cust.LastName)
...........
...........
cmd.Parameters.AddWithValue("@PhoneNumber", cust.PhoneNumber)
cmd.Parameters.AddWithValue("@Email", cust.Email)
cmd.ExecuteNonQuery()
End If
End Sub

Thanks in advance

Kush Kashyap

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-15 : 14:00:23

you cant use @@IDENTITY or scope_identity for returning email value. they return values only for auto incrementing (identity) type columns. what you can do is make use of output parameters to return value. something like

http://support.microsoft.com/kb/225948

KushKashyap
Starting Member

3 Posts

Posted - 2009-07-15 : 16:11:17

Thanks visakh16 i will try it out. if you can then please share some code snippet as would be very helpful..

Kush Kashyap

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources