perform check on inseert query

[johnny.g]

hii,,i am using asp.net 2005 and sql server 2005.i hav a insert page thr which i can insert details to a table in the database,,thrs a column called sme_id in the table.....what i want is when a user inserts the sme_id from the webpage,it should check from the table whether tht id exists or not just to avoid duplicate of sme_id,,,,,the code given below is working fine,,i am using sqldatasource for the insert query.....

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
            ConnectionString="<%$ ConnectionStrings:sme_trackerConnectionString %>" 

InsertCommand="INSERT INTO SME_Master(SME_Id, FirstName, LastName, Type_of_SME, Agency_id, Agency_Name, Email, Address, Phone, Mobile, Fax, TimeZone_Id, Experience, City, State, Status, Level_Of_Exam, Other_Comments, Certificate, Expertise) 
VALUES (@SME_Id, @FirstName, @LastName, @Type_of_SME, @Agency_id, @Agency_Name, @Email, @Address, @Phone, @Mobile, @Fax, @TimeZone_Id, @Experience, @City, @State, @Status, @Level_Of_Exam, @Other_Comments, @Certificate, @Expertise)"

 OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [SME_Master]">

hope u understand what i want,,,,reply as soon as possible,,thnks in advance

[serkan sendur]

make the id column primary key and identifier. Then regenerate the sqldatasource, it wont insert any duplicate keys

[johnny.g]

make the id column primary key and identifier. Then regenerate the sqldatasource, it wont insert any duplicate keys

thnks boss,its working,,,but now it has created 1 prob...how to display tht the id already exists,,,coz now if i insert a same id it displays an error page with primary key problem..instead i would lik to display a popup window or a label tht ths id already exists,,,,,
ne idea how to do ths...thnks for the 1st answer

[serkan sendur]

protected void GridView1_RowUpdated(object sender, GridViewUpdatedEventArgs e)
    {
        if(e.Exception != null)
       Label1.Text =e.Exception.Message;
        e.ExceptionHandled = true;
    }

[serkan sendur]

forget about my previous post, if you are making an insert using sqldatasource you can handle the error like this :

protected void Button1_Click(object sender, EventArgs e)
    {

        SqlDataSource1.InsertParameters["department"].DefaultValue = TextBox1.Text ;
        try
        {
            SqlDataSource1.Insert();
        }
        catch (Exception ex)
        {
            Label1.Text = ex.Message;
        }
    }

[johnny.g]

forget about my previous post, if you are making an insert using sqldatasource you can handle the error like this :

protected void Button1_Click(object sender, EventArgs e)
{

    SqlDataSource1.InsertParameters["department"].DefaultValue = TextBox1.Text ;
    try
    {
        SqlDataSource1.Insert();
    }
    catch (Exception ex)
    {
        Label1.Text = ex.Message;
    }
}

end quote.

well bro, thnks for ur help..i m using sql datasource and formview, thrs a link button for insert, its an inbuilt feature of formview, i guess so, thn how can i use the code given by u, any solution, pls reply

[serkan sendur]

select the event of formview from properties window's events tabl(opens when flash button is clicked) , double click item inserted event. Create the handler as follows

protected void FormView1_ItemInserted(object sender, FormViewInsertedEventArgs e)
    {
        if(e.Exception != null)
Label1.Text =e.Exception.Message;
e.ExceptionHandled = true;

    }

[johnny.g]

select the event of formview from properties window's events tabl(opens when flash button is clicked) , double click item inserted event. Create the handler as follows

protected void FormView1_ItemInserted(object sender, FormViewInsertedEventArgs e)
    {
        if(e.Exception != null)
Label1.Text =e.Exception.Message;
e.ExceptionHandled = true;

    }

end quote.

wow, thnks man, my prob is finally solvd, thnks 1ce again :P