StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUPDATE query DateTime overflow
text
Body
copied!<p>I use SQL Server 2012 and VS 2012. I have a table with following data:</p> <pre><code>TagID SessionID ScanningTime int nvarchar datetime --------------------------------------- 4820427 Test1 2013-08-16 14:20:17.447 </code></pre> <p>I have created an UPDATE query to save any changes that been made (any records added or existing records edited) in DGV back to SQL DB. </p> <p>So for example I will add a new record and edit the existing one to look like this:</p> <pre><code>TagID SessionID ScanningTime int nvarchar datetime --------------------------------------- 4820000 Test1 2013-08-16 14:20:17.447 3242341 Test1 2013-08-16 14:20:17.447 </code></pre> <p>And click the button to update the database:</p> <pre><code>private void UpdateBtn_Click(object sender, EventArgs e) { DataTable queryResult = new DataTable(); foreach (DataGridViewRow row in dataGridView1.Rows) { int tagID = Convert.ToInt32(row.Cells["TagID"].Value); string sessionID = Convert.ToString(row.Cells["SessionID"].Value); DateTime ScanningTime = Convert.ToDateTime(row.Cells["ScanningTime"].Value); string ConnStr = "Data Source=DUZY;Initial Catalog=AutoRegSQL;Integrated Security=True"; SqlConnection MyConn = new SqlConnection(ConnStr); MyConn.Open(); //SQL query that returns todays sessions for the given roomID string query = @"UPDATE Attendance" + " SET TagID= @tagNo, SessionID= @sessionNo, ScanningTime= @scantime "; SqlCommand command = new SqlCommand(query, MyConn); command.Parameters.Add("tagNo", SqlDbType.Int).Value = tagID; command.Parameters.Add("sessionNo", SqlDbType.VarChar).Value = sessionID; command.Parameters.Add("scantime", SqlDbType.DateTime).Value = ScanningTime; SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(queryResult); MyConn.Close(); } } </code></pre> <p>When I click the button to save the changes i receive an error:</p> <pre><code>An unhandled exception of type 'System.Data.SqlTypes.SqlTypeException' occurred in System.Data.dll Additional information: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. </code></pre> <p><strong>EDIT</strong>: </p> <p>Looking back to my SSMS, the content of Attendance table has changed after trying to update the table. The original record was replaced by the record that was added manualy (despite the error).</p> <p>Attendance table after UPDATE query execution:</p> <pre><code> TagID SessionID ScanningTime int nvarchar datetime --------------------------------------- 5453453 Test1 2013-08-16 14:20:00.000 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload