UPDATE:
I think the question is:
Why the command replace value of GETDATE() by DATE_CREATED parameter value although DATE_CREATED does NOT EXIST in command query?
I got the problem in Select statement as well...
How does GETDATE() work?
I'm having a problem with a SQL Server insert query.
I put GETDATE() into insert query, but I also put the parameter named DATE_CREATE with value 0001/01/01 as well.
And when I execute the insert query, it throw an exception
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Please help me figure out what cause this error.
P/s: Sorry about my bad English
Table create code:
CREATE TABLE [dbo].[USER](
[USER_ID] [int] NOT NULL,
[USER_NAME] [nvarchar](100) NOT NULL,
[DATE_CREATED] [datetime] NOT NULL,
CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED ([USER_ID] ASC)
) ON [PRIMARY]
GO
C# code:
private void Working()
{
//Get a SQL Connection
IDbConnection con = GetConnection();
USER user = new USER();
user.USER_ID = 1;
user.USER_NAME = "User";
user.DATE_CREATED = new DateTime(1, 1, 1);
USERDao dao = new USERDao(con);
con.Open();
try{
Insert(con,user);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
private int Insert(IDbConnection con,USER obj)
{
IDbCommand command;
command = con.CreateCommand();
string query = @"INSERT INTO USER (USER_ID, USER_NAME, DATE_CREATED)
VALUES (@USER_ID, @USER_NAME, GETDATE())";
command.CommandText = query +";";
addDbDataParameter(command, "USER_ID", obj.USER_ID, DbType.Int32);
addDbDataParameter(command, "USER_NAME", obj.USER_NAME, DbType.String);
addDbDataParameter(command, "DATE_CREATED", obj.DATE_CREATED, DbType.DateTime);
int res = command.ExecuteNonQuery();
if (res == 0) { throw new Exception("Zero rows were affected."); }
return res;
}
static public void addDbDataParameter(IDbCommand command, string name, object value, DbType dbType)
{
IDbDataParameter parameter = command.CreateParameter();
parameter.DbType = dbType;
parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = "@" + name;
//parameter.SourceColumn = name;
if (value == null) { parameter.Value = DBNull.Value; }
else { parameter.Value = value; }
command.Parameters.Add(parameter);
}
As you can see, there is no parameter named DATE_CREATED in the insert query (it's GETDATE() value), but the command still add the value of user.DATE_CREATED.
P/s: I fixed it by removing addParameter DATE_CREATED from the command, and it worked.
I hope you can help me learn why it causes the issue... :(
