CODEWITHSUNDEEP
c#sql-server
tuvboy
tuvboy

Reputation: 81

Insert record, auto add datetime getdate()

I have a simple sql insert C# statement, that works! However when I add a column for datetime getdate(), no nulls, insert fails. I have tried and looked at various articles, and i just cant get it.

Here is my code!

string constr = ConfigurationManager.ConnectionStrings[
                "My_DatabaseConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    using (SqlCommand cmd = new SqlCommand(
                            "INSERT INTO TestTable1 VALUES(@FirstName, @Email)"))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@FirstName", user.FirstName);
        cmd.Parameters.AddWithValue("@Email", user.Email);                    
        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

This works, so i add a new column Date using SQL Server Manager, with datetime and getdate() allow nulls not checked.

So this adds date and time to any existing records, but when i try from Form submit, its not working.

Error is: Column name or number of supplied values does not match table definition.

Upvotes: 0

Views: 1288

Answers (2)

Nomang
Nomang

Reputation: 55

get Time and date with DateTime.Now.

System.DateTime.Now.ToShortDateString();

add it as parameter in the query

Your code should be like this.

    string constr = ConfigurationManager.ConnectionStrings["My_DatabaseConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO TestTable1 VALUES (@FirstName,@Email,@DateTime)"))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@FirstName", user.FirstName);
            cmd.Parameters.AddWithValue("@Email", user.Email);                    
               cmd.Parameters.AddWithValue("@DateTime",System.DateTime.Now);                    
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

Upvotes: -1

Fabio
Fabio

Reputation: 32445

If list of columns not provided in the INSERT INTO statement, SQL Server expected values for all columns

From MSDN

If the values in the Value list are not in the same order as the columns in the table or do not have a value for each column in the table, column_list must be used to explicitly specify the column that stores each incoming value.

But your query provide only two columns

Add values for all columns in the table(except IDENTITY column if used) in the VALUES section

INSERT INTO TestTable1 VALUES (@FirstName, @Email, GETDATE())

or add list of inserted columns and default values will be used for other columns

INSERT INTO TestTable1 (FirstName, Email) VALUES (@FirstName, @Email)

Upvotes: 5

Related Questions