CODEWITHSUNDEEP
c#sql-serverwinforms
Codrin Afrasinei
Codrin Afrasinei

Reputation: 17

Using DateTimePicker in WinForms

I'm trying to select some records from a MSSQL database based on a date through a C# app (WinForms) and I'm using the following code:

string select = @"
select 
    g.nume as Gestiune, 
    p.cod as NumarNIR, 
    p.doc_cod as DocumentDeBaza, 
    p.Validat as Validat, 
    p.Facturat as Contat 
from 
    primar p 
        inner join gestiuni g 
        on p.part2=g.gest_id 
           and data= '" + dtpNIR.Value.ToString() + "' 
           and cod='" + txtNIR.Text +"'";

The error I get when I try to select the records:

the conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I have been looking for answers but all I could find was about using the insert statement using datetimepicker. What am I missing? Thanks

Upvotes: 0

Views: 128

Answers (1)

Fabio
Fabio

Reputation: 32445

Use Sql parameters.
Using parameters eliminate all kind of formatting problems in passing values to the query.

string select = @"
    select 
        g.nume as Gestiune, 
        p.cod as NumarNIR, 
        p.doc_cod as DocumentDeBaza, 
        p.Validat as Validat, 
        p.Facturat as Contat 
    from 
        primar p 
            inner join gestiuni g 
            on p.part2=g.gest_id 
                and data=@DateNIR 
                and cod=@TextNIR";

SqlParameter[] parameters = 
{
    new SqlParameter("@DateNIR", dtpNIR.Value),
    new SqlParameter("@TextNIR", txtNIR.Text)
}

using SqlConnection conn = new SqlConnection(yourConnectionString)
{
    using SqlCommand command = new SqlCommand(select, conn)
    {
        command.Parameters.AddRange(parameters);
        conn.Open()
        //Execute command
    }
}

Using parameters will defend against Sql Injection attacks
Improve performance of queries if parameter's properties defined correctly according to database schema (re-use of compiled query plans)

Upvotes: 2

Related Questions