|
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 |
|
Primary key of the record just inserted