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

Recommended Answers

All 2 Replies

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".

try to use if exists in sql.. on that way, that's the best to handle duplicate entry in the dbase..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.