How to update my data in grid view

Question

1.00/5 (1 vote)

See more:

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK__STANDARDS__S_ID__4AB81AF0". The conflict occurred in database "SAMPLE_1", table "dbo.SCHOOL", column 'S_ID'. The statement has been terminated.

PLS CORRECT IT IF AM WRONG ,IAM NEW TO WORK SO THAT I CAN LEARN IT

MY TASK IS
JUST WANT TO UPDATE THE S_NAME IN FRONTEND AND THE ID OF THE S_NAME SHOULD BE THE SAVED IN BACKEND ALL IN STANDARDS.ASPX

What I have tried:

THESE AR MY CODE
SCHOOL.ASPX

ASP.NET

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SCHOOL_1.aspx.cs" Inherits="WebApplication1.SCHOOL_1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1"  runat="server">
    <title></title>
</head>
<body>
    <form id="form1"  runat="server">
    <div>
    
        <asp:Label ID="Label1" runat="server" Text="SCHOOL NAME">
                
        <asp:TextBox ID="TextBox1" runat="server">
        <br />
        <br />
        <br />
        <br />
        <asp:Label ID="Label2" runat="server" Text="NO_OF_STANDARDS">
      
        <asp:TextBox ID="TextBox2" runat="server">
        <br />
        <br />
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="SUBMIT" />
        <br />
        <br />
        <br />
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server" Height="190px" 
             Width="266px" 
            DataSourceID="sql1">
        
        <asp:SqlDataSource ID="sql1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ApplicationServices_1 %>"
        SelectCommand="SELECT S_ID,S_NAME,NO_OF_STANDARD FROM [SCHOOL]">
        
        <br />
        <asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="Back" />
        <br />
        <br />
        
    
    </div>
    </form>
</body>
</html>

SCHOOL.ASPX.CS

ASP.NET

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace WebApplication1
{
    public partial class SCHOOL_1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=CSI60-PC\SQLEXPRESS;Initial Catalog=SAMPLE_1;Integrated Security=True");


            SqlCommand cmd = new SqlCommand("insert into school values( '" + TextBox1.Text + "' ,'" + TextBox2.Text + "')", con);

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, "school");
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            Response.Redirect("HOME_1.aspx");
        }
    }
}

STANDARDS.ASPX

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="STANDARDS_1.aspx.cs" Inherits="WebApplication1.STANDARDS_1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1"  runat="server">
    <title></title>
</head>
<body>
    <form id="form1"  runat="server">
    <div>
    
        <asp:Label ID="Label1" runat="server" Text="CLASS">
        
        <asp:TextBox ID="TextBox1" runat="server">
        <br />
        <br />
        <br />
        SCHOOL NAME        
        <asp:DropDownList ID="DropDownList1" runat="server" 
              DataSourceID="SqlDataSource1" DataTextField="S_NAME" DataValueField="S_ID"> 
           
        
        
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ApplicationServices_1 %>" 
            SelectCommand="SELECT [S_ID], [S_NAME] FROM [SCHOOL]">
      
        
            
        
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server" Height="214px" Width="255px" DataSourceID="sql2" 
         AutoGenerateColumns="false" AutoGenerateEditButton="true"
         AllowSorting="True" AllowPaging="True" DataKeyNames="STD_ID" >
          
         <columns>
         <asp:BoundField ReadOnly="True" HeaderText="std_id"
		DataField="std_id" SortExpression="std_id">
		<asp:BoundField HeaderText="class" DataField="class"
		SortExpression="class">
     	<asp:TemplateField HeaderText="S_NAME" SortExpression="S_ID">
        <edititemtemplate>
        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="Sql3" DataTextField="S_NAME" DataValueField="S_ID">
        
        </edititemtemplate>
     <itemtemplate>
		<asp:Label ID="Label1" runat="server" Text='<%# Bind("S_NAME") %>'>
	</itemtemplate>   
           
         </columns>
        
        <asp:SqlDataSource ID="sql2" runat="server"
        ConnectionString="<%$ ConnectionStrings:ApplicationServices_1 %>"
        SelectCommand="select STANDARDS.STD_ID,STANDARDS.CLASS,SCHOOL.S_NAME
                           from SCHOOL
                           left join STANDARDS
                           on STANDARDS.S_ID=SCHOOL.S_ID"
                           
       
            UpdateCommand="update [STANDARDS] set [CLASS]=@CLASS,[S_ID]=@STD_ID where [STD_ID]=@STD_ID">    
       <updateparameters>
      <asp:Parameter Type="Int16" Name="CLASS" />
      <asp:Parameter Type="Int16" Name="S_ID" />
       </updateparameters>  
        
        <br />
        <br />
        
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="SUBMIT" />
    
    </div>
    <asp:SqlDataSource ID="Sql3" runat="server"
    ConnectionString="<%$ ConnectionStrings:ApplicationServices_1 %>"
    SelectCommand="SELECT [S_ID],[S_NAME] FROM [SCHOOL]" >
           
    <br />
    <br />
    <br />
    <br />
    <asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="back" />
    </form>
</body>
</html>

STANDARDS.ASPX.CS
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace WebApplication1
{
    public partial class STANDARDS_1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=CSI60-PC\SQLEXPRESS;Initial Catalog=SAMPLE_1;Integrated Security=True");


            SqlCommand cmd = new SqlCommand("insert into STANDARDS values( '" + TextBox1.Text + "' ,'" + DropDownList1.SelectedValue + "')", con);

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, "STANDARDS");
            
            
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            Response.Redirect("HOME_1.aspx");
        }
   
    }
}

SQL TABLE STRUCTURE

SQL

CREATE TABLE SCHOOL(
	[S_ID] [int] IDENTITY (1000,1)  NOT NULL PRIMARY KEY ,
	[S_NAME] [varchar](255) NOT NULL,
	[NO_OF_STANDARD] [int] NOT NULL,
)



CREATE TABLE STANDARDS(
	[STD_ID] [int] IDENTITY (100,1) NOT NULL PRIMARY KEY,
	[CLASS] [int]NOT NULL,
	[S_ID] [int] NOT NULL FOREIGN KEY REFERENCES SCHOOL(S_ID),
	)

Posted 20-Jun-16 23:21pm

vignash rk

Updated 21-Jun-16 10:10am

Maciej Los

v3

Add a Solution

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)

Vincent Maverick Durano · Accepted Answer · 2016-06-21T07:59:00

First off, don't hard code your connection string your code behind file. You should put the config in your web.config file under connectionString element.

Second, appending the values from your input to your SQL statement is a big NO NO, as it can lead you to SQL injection attack. Read: Protect Your Data: Prevent SQL Injection[^]

Third, make it a habit to put objects that eat resources such as SqlConnection, SqlCommand and SqlDataAdapter within a using statement to ensure that objects will be properly disposed and closed after they are used.

Fourth, Do not use DataSet when you are only dealing with 1 result set. Instead you could use a DataTable.

Fifth, Don't mix SqlDataSource with your ADO.NET code. If want to do database operation by hand then stick to ADO.NET way.

You can rewrite your code to this:

C#

protected void Button1_Click(object sender, EventArgs e) {  
            DataTable dt = new DataTable();
            using (SqlConnection sqlConn = new SqlConnection
            (ConfigurationManager.ConnectionStrings["YourDBConnectionString"].ConnectionString)){
                string sql = "INSERT INTO dbo.STANDARDS VALUES (@Param1,@Param2)";
                using(SqlCommand sqlCmd = new SqlCommand(sql,sqlConn)){
                    sqlCmd.Parameters.AddWithValue("@Param1", TextBox1.Text);
		    sqlCmd.Parameters.AddWithValue("@Param1", DropDownList1.SelectedValue);
                    sqlConn.Open();
                    using(SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd)){
                        sqlAdapter.Fill(dt);
                    }
                }
            }

            if(dt.Rows.Count > 0){
                //do something with the data here
            }
 }

Your error typically means that you are trying to update foreign key column (S_ID) in your STANDARDS table which do not exists in reference table (SCHOOL table).
You should check your constraint again.