Check for duplicated year value from DateTimePicker

Question

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.

What is "MyNum" in your where sentence?

Ziv Weissman
– Ziv Weissman

2017-10-14 10:37:57 +00:00
Commented Oct 14, 2017 at 10:37 — Ziv Weissman
– Ziv Weissman, Commented Oct 14, 2017 at 10:37
Sorry .. its Num not MyNum .. i edit the question

John Deck
– John Deck

2017-10-14 10:48:28 +00:00
Commented Oct 14, 2017 at 10:48 — John Deck
– John Deck, Commented Oct 14, 2017 at 10:48

Anthropic · Accepted Answer · 2017-10-14 10:40:50Z

1

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

answered Oct 14, 2017 at 10:40

Anthropic

7111 gold badge11 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check for duplicated year value from DateTimePicker

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related