CODEWITHSUNDEEP
c#sql-server
GoldSoft
GoldSoft

Reputation: 33

Insert a smalldatetime into SQL Server

I am trying to insert date to a smalldatetime column in SQL Server

I try something like this:

DateTime  transfer_date;
transfer_date = DateTime.Now;

SQL = "insert into MyTbl (DateT) values (transfer_date)";

SqlCommand Cmd_SQL = new SqlCommand(SQL, Conn_SQL);
Cmd_SQL.CommandText = SQL;
Cmd_SQL.ExecuteNonQuery();

but I got this error:

The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value. The statement has been terminated.

Upvotes: 0

Views: 3599

Answers (2)

marc_s
marc_s

Reputation: 754538

You need to define a parametrized query and then set the parameter value - something like this:

// define SQL statement to use, with a parameter
string sqlStmt = "insert into dbo.MyTbl (DateT) values (@transferDate)";

// define connection and command objects
using (SqlConnection conn = new SqlConnection(your-connection-string-here))
using (SqlCommand cmd = new SqlCommand(sqlStmt, conn))
{
    // add parameter and set value
    cmd.Parameters.Add("@transferDate", SqlDbType.SmallDateTime).Value = DateTime.Now;

    // open connection, execute SQL query, close connection
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}

Upvotes: 5

Jon Skeet
Jon Skeet

Reputation: 1500745

You're currently not doing anything with your transfer_date variable at all. Your SQL statement contains the text transfer_date, but it doesn't automatically fetch the value from the database. You want something like:

// @transfer_date is now a *parameter*.
string sql = "insert into MyTbl (DateT) values (@transfer_date)";

// Avoid using a shared connection - it'll cause problems. Let the connection
// pooling do its job. But use using statements to ensure that both the connection
// and the statement are disposed.
using (var connection = new SqlConnection(...))
{
    connection.Open();
    using (var command = new SqlCommand(sql, connection))
    {
        // No need to set the CommandText value now - it's already set up above.
        // But we need to set the value of the parameter.
        command.Parameters.Add("@transfer_date", SqlDbType.SmallDateTime).Value
             = DateTime.Now;
        command.ExecuteNonQuery();
    }
}

Upvotes: 0

Related Questions