1 Replies - 2988 Views - Last Post: 14 September 2015 - 11:39 AM

#1 programmer786  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 11-May 12

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

Posted 14 September 2015 - 02:15 AM

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? Thanks in advance.

Is This A Good Question/Topic? 0
  • +

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

#2 William_Wilson  Icon User is offline

  • lost in compilation
  • member icon

Reputation: 207
  • View blog
  • Posts: 4,812
  • Joined: 23-December 05

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

Posted 14 September 2015 - 11:39 AM

It seems like you have 2 options.
I am assuming that picking the teacher is part of the insert I which case you have access to teacher information.

Either you need to query for the TID of the teacher the insert is to apply (of course this is not optimal and would require the names to be unique) or you need to store the TIDs with the teacher names so that it is accessible when the button is pressed.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1