Checking values in a database table to see if a new record would conflict with an existing record

Question

I have a Detailsview with multiple fields, always set to be in insert mode. Of those fields I wanted to check before the user inserted a new record to see if that user has already entered a record for that date.

I really needed to figure a way to check multiple requirements, like if the date requested is a holiday, or if there are already two different requests made from different users for the same date (because there cannot be more than two people requesting the same date.)

I could not get the custom validator to work because the only time I have postback is when insert is pressed.

I was wondering if there was any way to check different validations when insert is pressed but only follow through with the insert if it all passed?

I would need to check more than one column in the table. The userid and the date.

I hope that made sense.

asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="ObjectDataSource1" DataKeyNames="bwrequestid" Height="29px" Width="928px" AutoGenerateRows="False" CellPadding="4" ForeColor="#333333" GridLines="None" Style="margin-right: 0px; text-align: left; margin-top: 0px;" ondatabound="DetailsView1_DataBound" oniteminserting="EntValid_ItemInserting"

        protected void EntValid_ItemInserting(object sender, DetailsViewInsertEventArgs e)
    {
        for (int i = 0; i < e.Values.Count; i++)
        {
            if (e.Values[i] != GetData())
            {
                e.Cancel = true;

                return;
            }
        }
    }

-------the get data looks like this

private DataSet GetData() { ConnectionStringSettingsCollection cssc = ConfigurationManager.ConnectionStrings;

        var sql = "SELECT LEAVETYPE, LEAVECODE FROM TEST.LVTYPE ORDER BY LEAVECODE";

        using (iDB2Connection conn = new iDB2Connection(GetConnectionString()))
        {
            conn.Open();

            using (iDB2Command cmd = new iDB2Command(sql, conn))
            {
                cmd.DeriveParameters();

                using (iDB2DataAdapter da = new iDB2DataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds;
                }
            }
        }
    }

        protected void EntValid_ItemInserting(object sender, DetailsViewInsertEventArgs e)
    {
        for (int i = 0; i < e.Values.Count; i++)
        {
            if (e.Values[i] != GetData())
            {
                e.Cancel = true;

                return;
            }
        }
    }

-------the get data looks like this

private DataSet GetData() { ConnectionStringSettingsCollection cssc = ConfigurationManager.ConnectionStrings;

        var sql = "SELECT LEAVETYPE, LEAVECODE FROM TEST.LVTYPE ORDER BY LEAVECODE";

        using (iDB2Connection conn = new iDB2Connection(GetConnectionString()))
        {
            conn.Open();

            using (iDB2Command cmd = new iDB2Command(sql, conn))
            {
                cmd.DeriveParameters();

                using (iDB2DataAdapter da = new iDB2DataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds;
                }
            }
        }

}

Try out some code and let us know where you are struck. If everything is data driven, you need to get the list of holidays and other conditions from the database prior to the insert. Validate each of them and then make your insert — Shankar Raju
Sounds like you are trying to do everything from the front end. You should create a function in the codebehind that is fired on submit. Pass the infomation into the function and then do any/all validation you need from inside the function. The return type could be bool. So in your submit event you could call something like if(myValidatefunction(theData)) { submitTheFormHere } else { showMessageAndStartOver } — aserwin
I guess more of the problem I am having is the validator is not validating anything because the user has to hit insert to execute the code. I edited my code to show some of the validation I tried. — user1596472

saml saml · Accepted Answer · 2012-09-27T14:34:13

I am assuming you are using only server side validation is this correct? If so (which btw I would suggest both server and clientside validation but that is beside the point) what you need to is first of all handle the ItemInserting event of the Details view.

At that point you will have full access to the record. See MSDN for more details.

Inside the ItemInserting event handler, do your validation, and if it fails, use e.Cancel = true;

ex:

void CustomerDetail_ItemInserting(object sender, 
    DetailsViewInsertEventArgs e)
{
    for (int i = 0; i < e.Values.Count; i++)
    {
        if (e.Values[i] != *your validation here*)
        {
            e.Cancel = true;

            //set your validation summary message here
            ...

            return;
        }
    }
}

Just don't forget to hook up this handler:

<asp:DetailsView ID="CustomerDetail" 
    DataSourceID="Details" AutoGenerateRows="false"
    *other properties*
    OnItemInserting="CustomerDetail_ItemInserting" >

Checking values in a database table to see if a new record would conflict with an existing record

}

1 Answers