Exception with timestamp in the database

Question

I want to enter the time and date separately in the database, which have the fields timestamp and data, respectively. And I have the following code to add the fields:

sqlInsertCabecalho.Parameters.AddWithValue("@colaborador", Session["New"].ToString());
sqlInsertCabecalho.Parameters.AddWithValue("@data", DateTime.Now.ToString("yyyy-MM-dd"));

However, I always get the following exception in the middle of insertion:

Cannot Insert an Explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or Insert a DEFAULT into the timestamp column.

I’m using MS SQL Server 2012 in a local network database. What I’m doing wrong?

Answer 1

The guy timestamp of SQL Server is equivalent to rowversion, he shouldn’t keep one timestamp your.

The solution is given in the error message. Do not save anything in that column. Why save DateTime.Now explicitly if this is exactly what you save if you let SQL Server do it on its own? Then the solution is to just take the recording column and transfer it to SQL Server by placing GetDate() as default.

If you want to use your own timestamp

A timestamp is a sequential number. Then you have to convert the date to a number meeting its criteria starting at 01/01/1970. You cannot convert to text. If you want to save a timestamp, use a field bigint even.

To convert the date to the numeric compatible with timestamp can do this:

DateTime.Now.Ticks - 621355968000000000) / 10000000

Or you can create an extension method if you’re going to make a lot of use:

public static class DateTimeExt { 
    public static readonly DateTime TimeStampStart = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
    public static long ToTimestamp(this DateTime value) => (long)(value - TimeStampStart).TotalSeconds;
}

Behold working in the ideone. And in the .NET Fiddle. Also put on the Github for future reference.

Answer 2

Kawaii, if Esat using MSSQL and wants to store a date and time in different fields, I advise you to change the field type to date and time respectively.

In any case, for new banks, it is advisable to avoid the use of types datetime and timestamp, use the not so new types date, datetime2, datetimeoffset, time

sqlInsertCabecalho.Parameters.Add("@data", SqlDbType.Date, 8).Value = DateTime.Today;
sqlInsertCabecalho.Parameters.Add("@hora", SqlDbType.Time, 5).Value = DateTime.Now.TimeOfDay;

if you cannot modify the database structure, you will need to adapt to this poorly done modeling, in its place I would do the date and time insert in the field data and the timestamp/rowversion provided by the system in the field hora.

in this case the name of the field will not be self-explanatory, including the same will be untracterized, but if you do otherwise, you will be misrepresenting the data type, and between keeping the semantics of the column name and the data type, I prefer the type.

SqlCommand sqlInsertCabecalho = 
new SqlCommand("Insert into cabecalho (nRequesicao,nomeEmpresa,colaborador,data,nota) VALUES(@nRequesicao,@nomeEmpresa,@colaborador,@data,2nota)", sqlConn);

sqlInsertCabecalho.Parameters.Add("@nRequesicao", SqlDbType.Int, 4).Value = nRequesicao; // estou assumindo que está utilizando um campo int
sqlInsertCabecalho.Parameters.Add("@nomeEmpresa", SqlDbType.VarChar, 50).Value = DropDownListEmpresa.Text; // estou assumindo que está utilizando um campo varchar(50)
sqlInsertCabecalho.Parameters.Add("@colaborador", SqlDbType.VarChar, 50).Value = Session["New"].ToString(); // estou assumindo que está utilizando um campo varchar(50)
sqlInsertCabecalho.Parameters.Add("@data", SqlDbType.DateTime, 8).Value = DateTime.Now;  // estou assumindo que está utilizando um campo datetime
sqlInsertCabecalho.Parameters.Add("@nota", SqlDbType.VarChar, 10).Value = Session["New"].ToString());  // estou assumindo que está utilizando um campo varchar(10)

but the measure is to look for the administrator of the database and suggest modifying the types, thus following the recommendations of Microsoft itself.:

https://msdn.microsoft.com/en-us/library/ms187819.aspx