I'm using SQL Server as my DBMS and I have 2 tables namely: Person and Voter
Person has the following fields: PersonId (int, primary key auto-increment int_identity), FirstName (nvarchar(50)), LastName (nvarchar(50))
Voter has the following fields: VoterPersonId (int, foreign key), VoterPlace (nvarchar(50))
These 2 tables are related. For 1 person there can only be 1 voter place. So you may call it a one is to one relationship. I created 2 tables because there are also people who aren't voters that I must include in my database and I don't want to create null values so I made a separate table which is the 'voter' table. Basically, I wanted to know the number registered voters and non-voters in our city. Now, I have created a 'save' button in VB.NET and used the following code to insert information into my tables:
Dim sql As New SQLControl
Private Sub cmdSave_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
sql.con.Open()
Using cmdPerson As New SqlClient.SqlCommand("INSERT INTO Person(FirstName, LastName) VALUES('" & txtFirstName.Text & "', '" & txtLastName.Text & "')", sql.con)
cmdPerson.ExecuteNonQuery()
End Using
Using cmdVoter As New SqlClient.SqlCommand("INSERT INTO Voter(VoterPlace) VALUES('" & txtVoterPlace.Text & "')", sql.con)
cmdVoter.ExecuteNonQuery()
End Using
sql.con.Close()
End Sub
Now, my problem is I don't know how to transfer the value of 'PersonId' primary key which is auto-increment in_identity into 'VoterPersonId' the moment I click on the 'save' button. Is it possible? Can you please help me on this matter? I would really appreciate it.