Get SQL Foreign key columns values from Primary Key asp.net c#

Question

I have two tables one is Teacher and the other one is Scheduling. The teacher table has Primary key TID and other columns. The Scheduling table has primary key, other columns and Foreign Key TID.

Now, I insert teachers data in one page. It inserts successfully. I have to insert data in other page to schedule the class of the teacher with the student. My problem is this, I cannot insert the pk values of teachers in the fk column of Scheduling Table.

Here's what I have done so far.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace E_Tutor_Manager
{
    public partial class ScheduleClass : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            {
                string connc = @"Data Source=KHAWAR-PC.\SQLEXPRESS;Initial Catalog=ETManager;Integrated Security=True";
                SqlConnection con = new SqlConnection(connc);
                string query = "SELECT Username FROM Teachers";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Connection.Open();
                SqlDataReader ddlValues;
                ddlValues = cmd.ExecuteReader();
                TUName.DataSource = ddlValues;
                TUName.DataValueField = "Username";
                TUName.DataTextField = "Username";
                TUName.DataBind();
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

            if (!IsPostBack)
            {
                if (Session["New"] != null)
                {
                    welcome.Text += Session["New"].ToString();
                }
                else
                    Response.Redirect("Login.aspx");
            }
        }

        protected void Button1_Schedule_Click(object sender, EventArgs e)
        {

            try
            {
                {
                    string connc = @"Data Source=KHAWAR-PC.\SQLEXPRESS;Initial Catalog=ETManager;Integrated Security=True";
                    SqlConnection con = new SqlConnection(connc);
                    con.Open();

                    string query = "SELECT * FROM Teachers WHERE StartTime = @StartTime";
                    string query1 = "INSERT INTO Schedules(Grade,Subjects,STime,TID) VALUES ('" + txtGrd.Text + "','" + Sbj.SelectedValue + "','" + txtTime.Value + "')";
                    SqlCommand sql = new SqlCommand(query1, con);
                    SqlCommand sql1 = new SqlCommand(query, con);
                    sql.ExecuteNonQuery();
                    var cmd = new SqlCommand(query, con);
                    cmd.Parameters.AddWithValue("@StartTime", txtTime.Value);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    if (dt.Rows.Count > 0)
                    {
                        labelmsg.Text = "Sorry!!! This teacher is not available at this time. Please select the other teacher";
                    }
                    else
                    {
                        labelmsg.Text = "Class Scheduled Succesfully!!!";
                    }
                    con.Close();
                }        
            }
            catch (Exception ee)
            {
                throw (ee);
            }
        }

    }
}

So can anybody help me?

Where are you getting TID from? The string query1 above doesn't seem to include it in its values. — Michael McMullin
– Michael McMullin, Commented Sep 14, 2015 at 9:16

Niar · Accepted Answer · 2015-09-14 09:44:21Z

1

Try this. This will work only if the select query returns only one value.

string query1 = "INSERT INTO Schedules(Grade,Subjects,STime,TID) VALUES ('" + txtGrd.Text + "','" + Sbj.SelectedValue + "','" + txtTime.Value + "',(SELECT TID FROM Teachers WHERE StartTime = @StartTime))";

answered Sep 14, 2015 at 9:44

Niar

5402 gold badges11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vect0rZ · Accepted Answer · 2015-09-14 09:19:37Z

0

string query1 = "INSERT INTO Schedules(Grade,Subjects,STime,TID) VALUES ('" + txtGrd.Text + "','" + Sbj.SelectedValue + "','" + txtTime.Value + "')";

This is the line where you want to insert the FK. First you must retrieve it like so:

int FK_ID; // This is where we store the foreign key
using(SqlReader reader = query.ExecuteReader())
{
    while(reader.Read())
    {
         FK_ID = (int)reader["ID"]; // where supposingly ID is the field name
         //But this FK_ID will be the one you want 
         //only if the query returns a single result, otherwise it will set FK_ID 
         //to the last returned row
    }
}

Then you can use the ID to set it in the query1 values

answered Sep 14, 2015 at 9:19

Vect0rZ

4012 silver badges6 bronze badges

Collectives™ on Stack Overflow

Get SQL Foreign key columns values from Primary Key asp.net c#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related