Stack Exchange
Issues in insert function in SQL Server and C#
C#:
protected void btnsearch_Click(object sender, EventArgs e)
{
    SqlConnection con = Connection.DBconnection();   {

    SqlCommand com = new SqlCommand("sp_studentresult", con);
    com.CommandType = CommandType.StoredProcedure;  
    com.Parameters.AddWithValue("@id", textstudentid.Text);
    com.Parameters.AddWithValue("@id_student", textstudentid.Text.Trim());
    SqlParameter retval = new SqlParameter("@output", SqlDbType.VarChar, 50);

    com.Parameters.AddWithValue("@tamil", txttamil.Text.Trim());
    com.Parameters.AddWithValue("@english", txtenglish.Text.Trim());
    com.Parameters.AddWithValue("@maths", txtmaths.Text.Trim());
    com.Parameters.AddWithValue("@science", txtscience.Text.Trim());
    com.Parameters.AddWithValue("@socialScience", txtsocialscience.Text.Trim()); 

    retval.Direction = ParameterDirection.Output;
    com.Parameters.Add(retval);

    com.ExecuteNonQuery();

    string Output = retval.Value.ToString();

    textstudentid.Text = string.Empty;
    txttamil.Text = string.Empty;
    txtenglish.Text = string.Empty;
    txtmaths.Text = string.Empty;
    txtscience.Text = string.Empty;
    txtsocialscience.Text = string.Empty;   

    SqlDataAdapter adp = new SqlDataAdapter(com);
    DataSet ds = new DataSet();
    adp.Fill(ds);

    if (ds.Tables[0].Rows.Count > 0)
    {
        tblid.Visible = true;
        txtid.Text = ds.Tables[0].Rows[0]["id"].ToString();
        txttamil.Text = ds.Tables[0].Rows[0]["Tamil"].ToString();
        txtenglish.Text = ds.Tables[0].Rows[0]["English"].ToString();
        txtmaths.Text = ds.Tables[0].Rows[0]["Maths"].ToString();
        txtscience.Text = ds.Tables[0].Rows[0]["Science"].ToString();
        txtsocialscience.Text = ds.Tables[0].Rows[0]["SocialScience"].ToString();
    }
    else
    {
        tblid.Visible = false;
        output.Text = Output;
    }
}            

What I have done:
Step 1
When I enter invalid id (which means id doesnot contain in student table) and search, it shows "doesn't exist".
Step 2
When I enter valid id (which means id contains in student and also contain in studentresult table) and search, it shows student marks, if I want to edit the marks and update, so it shows "marks updated".
Step 3
But when I enter id (which means id contains in student but doesn't contain in studentresult table) and search, it works updated function again with all the textboxes contains 0, instead it works insertion.
May I know, what my mistake in the above code?
Can anyone guide me?
I'm struggling for an hour, I'm beginner in .net.
Thanks,

Its pretty obvious that it will go into the Insertion block. If you are providing a valid ID that exists in student table, and your code is calling sp_studentresult, then as per the code branching, the execution will step into the second area, that is Insertion block.

i can't understand...can you please elaborate? thanks

Where are you calling sp_searchupdate ?

in update_Click function..

for me.. insertion didn't work..

When you click search, your sp_studentresult is called and in that you have ELSE IF EXISTS (SELECT * FROM student WHERE id=@id_student). This means that if you supply a valid ID here, it will directly insert it. What do you want to achieve exactly ?

that insertion didn't work.. p://s8.postimg.org/e2ne0c5qt/untitled.jpg

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all!

sp_studentresult is broken: it should not insert into studentresult if there already is a row for the given id.  Just add
AND NOT EXISTS (SELECT * FROM studentresult WHERE id_student=@id_student) 

to 
ELSE IF EXISTS (SELECT * FROM student WHERE id=@id_student)

Resulting to:
ALTER PROCEDURE sp_studentresult
(
    @id int,
    @output varchar(50) output,
    @id_student varchar(50),
    @Tamil Varchar (100),
    @English varchar (50),
    @Maths Varchar (50),
    @Science Varchar (50),
    @SocialScience Varchar (50) 
)
AS
IF NOT EXISTS (SELECT * FROM student WHERE id=@id_student)
BEGIN
SET @output='Doesn not EXIST'
END
ELSE IF EXISTS (SELECT * FROM student WHERE id=@id_student)
        AND NOT EXISTS (SELECT * FROM studentresult WHERE id_student=@id_student) 
BEGIN
INSERT into studentresult (id_student,Tamil,English,Maths,Science,SocialScience) values (@id_student,@Tamil,@English,@Maths,@Science,@SocialScience)
SET @output='Inserted'
END
SELECT * from studentresult where id_student=@id


@Rani: I added the resulting alter procedure

it didn't work for me.. here is my screenshot of output: http://s8.postimg.org/e2ne0c5qt/untitled.jpg

for example: i put 112 id and search.. this id contain in student table but not in studentresult table.. so i have to insert marks in this 112 id.. but it shows textboxes with 0 value.. after that i remove 0 and and enter marks and click update.. it shows "updated".. so finally insertion didn't work :(

After altering the procedure, the studentresult  table should be resetted once (delete from studentresult  ). The problem is, that there are multiple rows per student now.

@Rani - Probably you need to write one more query or code block in the sp_updateresult which will have an INSERT query apart from the existing UPDATE query.

@MattMurdock: i created one more query.. but how where i can add in search_click?..thanks..

just call btnsearch_Click at the end of update_Click to refresh view

@Rani - Try the procedure that Mikey has shown in his answer. That probably should do it.
