Exception with timestamp in the database

2

I want to separately insert the time and date into the database, which have the timestamp and data fields, 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"));

But nevertheless, I always have the following exegetion in the middle of the insertion:

  

Can not 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 am using MS SQL Server 2012 in a database on the local network. What am I doing wrong?

    
asked by anonymous 22.04.2016 / 16:19

2 answers

2

The SQL Server timestamp is equivalent to rowversion , it should not save a timestamp .

The solution is given in the error message. Do not record anything in this column. Why write DateTime.Now explicitly if that's exactly what it writes if you let SQL Server do it on its own? So the only solution is to take the column out of the write and transfer it to SQL Server by setting GetDate() to 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 by meeting the criterion of it starting on 01/01/1970. Can not convert to text. If you want to write a timestamp , use a bigint field.

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

DateTime.Now.Ticks - 621355968000000000) / 10000000

Or you can create an extension method if you make use of it:

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) {
        return (long)(value - TimeStampStart).TotalSeconds;
    }
}

See working on dotNetFiddle .

    
22.04.2016 / 16:35
1

Kawaii, if esat using MSSQL and want 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 p>

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

If you can not modify the database structure, you will have to adapt to this poorly done model, in its place I would insert the date and time in the data field 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, even the same will be deprecated, but if you do otherwise, you will be typing the type of the data, and between maintaining the semantics of the column name and type of data, 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 best thing is to look for the database administrator and suggest modifying the types, so follow the recommendations of Microsoft itself:

link

    
22.04.2016 / 16:35