CODEWITHSUNDEEP
c#winformsdatetimepicker
John Deck
John Deck

Reputation: 899

Check for duplicated year value from DateTimePicker

I'm trying to check for duplicate records where one of its fields is date … TxtNum is textbox and DtDate is DateTimePicker and a stored procedure called CHECK_DUPLICATE

In my situation I don’t want to check the exact date .. but the year only .. for example .. the record is duplicated if two entry is txtNum = 1000 and the year value of both fields is 2017 

public DataTable CHECK_DUPLICATE(string txtNum, string dtDateYear)
{
    DataTable dt = new DataTable();
    SqlParameter[] param = new SqlParameter[2];

    param[0] = new SqlParameter("@txtNum", SqlDbType.NVarChar, 50);
    param[0].Value = txtNum;

    param[1] = new SqlParameter("@dtDateYear ", SqlDbType.NVarChar, 5);
    param[1].Value = dtDateYear;

    dt = DAL.SelectData("CHECK_PROCURATION_DUPLICATE", param);
    DAL.close();
    return dt;
} 

private void btnAddProcuration_Click(object sender, EventArgs e)
{
    string year = DtDate.Value.ToString("yyyy");

    DataTable dt = new DataTable();
    dt = CHECK_DUPLICATE (txtNum.Text, year);
    if (dt.Rows.Count > 1)
        MessageBox.Show("Duplicated records", MessageBoxButtons.OK, MessageBoxIcon.Error);
        return;
    }
}

SQL :

CREATE PROCEDURE [dbo].[CHECK_DUPLICATE]

    @Num nvarchar (50),
    @Year nvarchar (5),

AS
    BEGIN
        SELECT Num, DATEPART(YEAR, MyDate) AS 'Year'
        FROM tblMyList   
        WHERE     Num = @Num AND MyDate = @Year 
    END

But it didn't work … it saves the records even if it is a duplicate.

Upvotes: 0

Views: 39

Answers (1)

Anthropic
Anthropic

Reputation: 701

Should MyDate = @Year be DATEPART(YEAR, MyDate) = @Year like:

CREATE PROCEDURE [dbo].[CHECK_DUPLICATE]

@Num nvarchar (50),
@Year nvarchar (5),

AS
BEGIN
    SELECT Num, DATEPART(YEAR, MyDate) AS 'Year'
    FROM   tblMyList   
    WHERE  Num = @Num AND DATEPART(YEAR, MyDate) = @Year 
END

Upvotes: 1

Related Questions