hi everyone i need some help. am using visual studio 2008 with sql server 2005. i have a table name "mca1sub1" nd its fields are "cStudentCode,vassignmentI,vassignmentII,Internal,Total". now in my project whenever a faculty adds a new student thn "cstudentcode" in table "mca1sub1" is automatically updated . i have left all the remaining fields(cstudentcode,vassignmentI,vassignmentII,etc) to allow null values. now i hve a created result form. here i have a combobox which has all the ids. if i select one value from combobox nd click save button
it should check if id exists in table thn only it should update the result. if not present , it should return a error "Such id doesnt exist !! please enter valid id to proceed";
now there are two things to be done
1- if id exists or not ,
2- if exists it should update it. how can i acheive this. please help.

******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mca1sub1](
    [cStudentCode] [varchar](10) NOT NULL,
    [vAssignmentI] [float] NOT NULL,
    [vAssignmentII] [float] NOT NULL,
    [Attendance] [float] NOT NULL,
    [Internal] [float] NOT NULL,
    [Viva_Presentn] [float] NOT NULL,
    [Total] [float] NOT NULL,
 CONSTRAINT [PK_mca1sub1] PRIMARY KEY CLUSTERED 
(
    [cStudentCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
*/ above is the table /*
*/c# code for save_button_click /*
String studentCode=combobox1.text.ToString();
if (comboBox2.Text == "Sub1")
                {
                   SqlCommand cmd5 = new SqlCommand("Update mca1sub1 set vAssignmentI='" + textBox1.Text + "',vAssignmentII = '" + textBox2.Text + "',Attendance='" + textBox3.Text + "', Internal = '" + textBox4.Text + "',Viva_Presentn = '" + textBox5.Text.ToString() + "',Total = '" + textBox6.Text + "' where cStudentCode ='" + studentCode + "'", cn1, trans1);
                    cmd5.ExecuteNonQuery();
                    trans1.Commit();
                        {
                            MessageBox.Show("Results Saved");
                        }
    //Updation not working as well as how can i check if student id exists in the above table
    //if exists update the table, if not thn error

Well there are three ways to do this.

1) Quick and dirty; try and do the update. If it errors, likely it doesn't exist. This is the worst possible way to do it.
2) It'll Do...; try and select the ID first. If you get a result, it exists and can be updated. This requires an extra round trip to the DB and so I don't recommend it.
3) The Right Way™; create a stored procedure that checks if the id exists using an "if" statement. If the result is true, the row can be updated, otherwise, return a custom error. This is the right way to do what you ask, however, option two may "suffice".

This article has been dead for over six months. Start a new discussion instead.