foreighnkey column is showing as null

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

First table

SQL

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

SQL

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

SQL

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

C#

    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();

Posted 7-May-15 0:26am

Unni R

Updated 7-May-15 1:50am

v4

Add a Solution

Comments

Richard Deeming 7-May-15 8:34am

Your fillgrid method is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Accepted Answer · 2015-05-07T00:53:00

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:

XML

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

Your code will look like this:

C#

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();
}

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