Insert data into Foreign Key and Primary Key Table using C# and VB.Net in ASP.Net

Last Reply 6 days ago By pandeyism

Posted 6 days ago

How Can I insert to two different tables on click of submit button in vb.net where CourseID is foriegn key and StudentID is primary key in Students table AND CourseID is primary key in Courses Table.

Please help me  with sql query without stored procedure.

I am not going to take primary key and foriegn key values from any text box. They are autogenerated.

Students Table

StudentID Name CourseID


Courses Table

CourseID, CourseName

Thanks.

Posted 6 days ago

Hi swathiyemp

Refer below sample.

SQL

CREATE TABLE Studentstable(StudentID INT, Name VARCHAR(10), CourseID INT IDENTITY PRIMARY KEY)
CREATE TABLE Coursestbl (CourseID INT PRIMARY KEY, CourseName VARCHAR(10), 
CONSTRAINT FK_Studentstbl FOREIGN KEY(CourseID) REFERENCES Studentstable(CourseID))
SELECT * FROM Studentstable  
SELECT * FROM Coursestbl	

HTML

StudentId :
<asp:TextBox runat="server" ID="txtStudentId" />
<br />
Student Name :
<asp:TextBox runat="server" ID="txtName" />
<br />
Cource Name :
<asp:TextBox runat="server" ID="txtCource" />
<br />
<asp:Button Text="Insert" runat="server" OnClick="Save" />

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient

Code

C#

protected void Save(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("INSERT INTO Studentstable(StudentID,Name) VALUES(@StudentID, @Name)", con);
    cmd.Parameters.AddWithValue("@StudentID", txtStudentId.Text);
    cmd.Parameters.AddWithValue("@Name", txtName.Text);
    con.Open();
    cmd.ExecuteNonQuery();
    cmd = new SqlCommand("SELECT MAX(CourseID) FROM Studentstable", con);
    int courceId = Convert.ToInt32(cmd.ExecuteScalar());
    cmd = new SqlCommand("INSERT INTO Coursestbl(CourseID,CourseName) VALUES(@CourseID, @CourseName)", con);
    cmd.Parameters.AddWithValue("@CourseID", courceId);
    cmd.Parameters.AddWithValue("@CourseName", txtCource.Text);
    cmd.ExecuteNonQuery();
    con.Close();
}

VB.Net

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = New SqlCommand("INSERT INTO Studentstable(StudentID,Name) VALUES(@StudentID, @Name)", con)
    cmd.Parameters.AddWithValue("@StudentID", txtStudentId.Text)
    cmd.Parameters.AddWithValue("@Name", txtName.Text)
    con.Open()
    cmd.ExecuteNonQuery()
    cmd = New SqlCommand("SELECT MAX(CourseID) FROM Studentstable", con)
    Dim courceId As Integer = Convert.ToInt32(cmd.ExecuteScalar())
    cmd = New SqlCommand("INSERT INTO Coursestbl(CourseID,CourseName) VALUES(@CourseID, @CourseName)", con)
    cmd.Parameters.AddWithValue("@CourseID", courceId)
    cmd.Parameters.AddWithValue("@CourseName", txtCource.Text)
    cmd.ExecuteNonQuery()
    con.Close()
End Sub