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))
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.
Saineshwar BageriPosted Oct 15, 2014, 1:32 AM
CREATE PROCEDURE Insert_MobileDetails @mobilename NVARCHAR(50) = NULL
,@mobilepricefrom NUMERIC(19, 2) = 0
,@mobilepriceto NUMERIC(19, 2) = 0
,@mobileos NVARCHAR(50) = NULL
,@Result NVARCHAR(10) OUTPUT ---// Declaring output paramereter
AS
BEGIN
INSERT INTO MobileQuote (
mobilename
,mobilepricefrom
,mobilepriceto
,mobileos
)
VALUES (
@mobilename
,@mobilepricefrom
,@mobilepriceto
,@mobileos
)
DECLARE @id INT
SET @id = @@ROWCOUNT
IF (@id > 0)
BEGIN
SET @Result = 'Success' --// setting value to output parameter
END
END
C# code
public void InsertmobileDetailstoDB() {
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mymobcon"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("Insert_MobileDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@mobilename", mobilename.text);
cmd.Parameters.AddWithValue("@mobilepricefrom", mobilepricefrom.text);
cmd.Parameters.AddWithValue("@mobilepriceto", MQ.mobilepriceto.text);
cmd.Parameters.AddWithValue("@mobileos", MQ.mobileos.text);
cmd.Parameters.Add("@Result", SqlDbType.Char, 500);
cmd.Parameters["@Result"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
MQ = null;
con.Close();
string RESULT = cmd.Parameters["@Result"].Value.ToString();
}