title: C# - SQL Server : Insert query with GETDATE() and Parameter
body: <p>UPDATE:</p>
  
  <p>I think the question is:</p>
  
  <p>Why the command replace value of <code>GETDATE()</code> by <code>DATE_CREATED</code> parameter value although <code>DATE_CREATED</code> does NOT EXIST in command query?</p>
  
  <p>I got the problem in <code>Select</code> statement as well...</p>
  
  <p>How does <code>GETDATE()</code> work?</p>
  
  <p>I'm having a problem with a SQL Server insert query.</p>
  
  <p>I put <code>GETDATE()</code> into insert query, but I also put the parameter named <code>DATE_CREATE</code> with value <code>0001/01/01</code> as well.</p>
  
  <p>And when I execute the insert query, it throw an exception</p>
  
  <blockquote>
    <p>SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and
    12/31/9999 11:59:59 PM.</p>
  </blockquote>
  
  <p>Please help me figure out what cause this error.</p>
  
  <p>P/s: Sorry about my bad English</p>
  
  <p>Table create code:</p>
  
  <pre><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
  </code></pre>
  
  <p>C# code:</p>
  
  <pre><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);
      }
  </code></pre>
  
  <p>As you can see, there is no parameter named <code>DATE_CREATED</code> in the insert query (it's <code>GETDATE()</code> value), but the command still add the value of <code>user.DATE_CREATED</code>.</p>
  
  <p>P/s: I fixed it by removing addParameter <code>DATE_CREATED</code> from the command, and it worked.</p>
  
  <p>I hope you can help me learn why it causes the issue... :(</p>