foreighnkey列显示为null [英] foreighnkey column is showing as null

问题描述

我有两个表，这里附加表格详细信息，同时将数据插入数据库foreighnkey列显示为null。请帮帮我。



第一张表

I have two tables, table details are attached here while inserting data into database foreighnkey column shows as null. Please help me.

First table

CREATE TABLE  MASTER1 ( 
ID int  (primary key), 
EMPNAME varchar(50) 
,EMPID varchar(50) ,
DEPARTMENT varchar(50)
,TITLE varchar(50) , 
CATEGORY varchar(50) ,  OTHERDETAILS varchar(50),)

第二张表

second table

CREATE TABLE  addition(
ID_EMP  int (foreighnkey connect with ID of EMPMASTER),
EMP_NAME varchar(50) 
    EMP_ID varchar(50) ,
 EMP_DEPARTMENT varchar(50) )

存储过程

Stored procedure

CREATE PROCEDURE [dbo].[EmployeeIns]

@EMPNAME VARCHAR(50),@EMPID VARCHAR(50),@DEPARTMENT VARCHAR(50),
@TITLE VARCHAR(500),@CATEGORY VARCHAR(500),@OTHERDETAILS varchar(500)
AS
BEGIN

INSERT INTO MASTER1(EMPNAME,EMPID,DEPARTMENT,TITLE ,CATEGORY,OTHERDETAILS)VALUES(@EMPNAME,@EMPID,@DEPARTMENT,@TITLE,@CATEGORY,@OTHERDETAILS)
END

    public void fillgrid()
  
 for (int i = 0; i < GridView1.Rows.Count; i++)
      
SqlConnection SC = sqlconnection;
            SC.Open();
            string strEMPNAME = GridView1.Rows[i].Cells[0].Text.ToString().Trim();
            string strEMPID = GridView1.Rows[i].Cells[1].Text.ToString().Trim();
            string strDEPARTMENT = GridView1.Rows[i].Cells[2].Text.ToString().Trim();
            SqlCommand cmd = new SqlCommand("insert into addition (EMP_NAME,EMP_ID,EMP_DEPARTMENT) values('" + strEMPNAME + "','" + strEMPID + "','" + strDEPARTMENT + "' )", SC);
            cmd.ExecuteNonQuery();
            SC.Close();

    protected void Button1_Click(object sender, EventArgs e)
         
 SC.Open();
             SqlCommand cmd = new SqlCommand("EmployeeIns", SC);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EMPNAME", txtname.Text);
            cmd.Parameters.AddWithValue("@EMPID", txteid.Text);
            cmd.Parameters.AddWithValue("@DEPARTMENT", drpdept.SelectedValue);
            cmd.Parameters.AddWithValue("@TITLE", Txttitle.Text);
            cmd.Parameters.AddWithValue("@CATEGORY", drpcatgry.SelectedValue);
            cmd.Parameters.AddWithValue("@OTHERDETAILS", txtothers.Text);
             cmd.ExecuteNonQuery();
            fillgrid();
          sc.close();

推荐答案

在fillgrid（）中，您将插入ADDITION表中的一堆记录而不指定ID_EMP（FK）的值。你为什么感到惊讶他们是NULL？如果您在@ADDITIONS参数（XML）中提供这些记录，您的SP似乎可以插入ADDITION记录。您需要决定如何插入。



编辑

在你的SP中你看到了加法参数作为属性存储在ADDITION节点中。您需要以下列形式构造XML：

In fillgrid() you are inserting into ADDITION table bunch of records without specifying the value for ID_EMP (FK). Why are you surprised they are NULL? It seems your SP can insert ADDITION records if you provide those in @ADDITIONS parameter (XML). You need to decide how you want to do the insert.

EDIT
In your SP you see that addition parameters are stored in ADDITION nodes as attributes. You need to construct the XML in following form:

<doc>
    <ADDITION EMP_NAME="..." EMP_ID="..." EMP_DEPARTMENT="..."/>
</doc>

您的代码将会显示为li ke this：

Your code will look like this:

protected void Button1_Click(object sender, EventArgs e)
{
    string additions = ... // construct the XML here
    SC.Open();
    SqlCommand cmd = new SqlCommand("EmployeeIns", SC);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@EMPNAME", txtname.Text);
    cmd.Parameters.AddWithValue("@EMPID", txteid.Text);
    cmd.Parameters.AddWithValue("@DEPARTMENT", drpdept.SelectedValue);
    cmd.Parameters.AddWithValue("@TITLE", Txttitle.Text);
    cmd.Parameters.AddWithValue("@CATEGORY", drpcatgry.SelectedValue);
    cmd.Parameters.AddWithValue("@OTHERDETAILS", txtothers.Text);
    cmd.Parameters.AddWithValue("@ADDITIONS", additions); // add parameter

    cmd.ExecuteNonQuery();
    fillgrid();
    sc.close();
}

您可以删除fillgrid（），不再需要它。

And you can drop fillgrid(), it's not needed anymore.

这篇关于foreighnkey列显示为null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！