Validation against another table in the database

Hi,

I want to validate my delete in the gridview to ensure the primary key in the record they are deleting isn't a foreign key in another table. I do have constraints on the table, but would like to have my own error message appear.

So with c# i want to call a subprogram through the ondeleting event, that will check another database table for the primary key in the gridview. How do i write that code?

I'm very new to the c# and asp.net world!

Hello my friend,

You can do this at the database level using a stored procedure to check. In my example, I want to delete a country based on its numeric id, but not if there are destinations associated with that country, and I want the page to tell me if the delete was done or not allowed. Create the following stored procedure in the database by running this SQL: -

CREATE PROCEDURE usp_DeleteCountry
(
@.CountryID AS INT
)

AS

IF EXISTS(SELECT 1 FROM tblDestination WHERE CountryID = @.CountryID)
BEGIN
SELECT 'NO'
END
ELSE BEGIN
DELETE FROM tblCountry WHERE CountryID = @.CountryID

SELECT 'DONE'
END

Now use the following within your web page: -

int intCountryID = 5; // delete country 5 for example

SqlConnection conn = new SqlConnection("YourConnectionString");
conn.Open();

SqlCommand cmd = new SqlCommand("usp_DeleteCountry", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.CountryID", SqlDbType.Int).Value = intCountryID;

string strResult = cmd.ExecuteScalar().ToString();

conn.Close();

if (strResult.Equals("NO"))
{
Response.Write("You cannot delete it. There are related records");
}

Kind regards

Scotty