Application crashes at ArgumentOutOfRangeException

[EDN Admin]

I am extracting data from an Oracle system into SQL Server using OLEDB.

The issue is that there is some invalid dates in our Source Oracle system, I am extracting row by row and then trying to insert it into the real MY table, if it fails the insert, then I need that row to be inserted into a MY_ERROR table where all the fields
are VARCHAR. The issue I have is with a DATE column, there are some invalid dates like â9/16/0199 12:00:00 AMâ which gets redirected as expected into the MY_ERROR table just fine.

Unfortunately, some of the dates in the Source system have DATES like â00/00/0000 00:00:00 00â and these errors donât get trapped and causes the entire application to crash. The requirement is for this row (and any other error row) to
get redirected to the MY_ERROR table as well.

Ive stripped the code down to a console application as below. The error actually occurs in the getObject = reader line, inside the WHILE(READER.READ) loop. Because of this crash, the row never gets redirected and crashes.


<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; string sourceConnectionString = <span style="color:#A31515; "Data Source=xxxxxxx;User ID=xxxx;Password=xxxxx;Provider=MSDAORA.1;";
<span style="color:Blue; string destConnectionString = <span style="color:#A31515; "Data Source=localhost;Initial Catalog=Test_MY;Integrated Security=True;";


<span style="color:Blue; string oracleExtract = <span style="color:#A31515; "SELECT myID, myDATE FROM OracleMy";

<span style="color:Blue; using (OleDbConnection sourceConn = <span style="color:Blue; new OleDbConnection(sourceConnectionString))
{

sourceConn.Open();

OleDbCommand cmdExtractFromOracleSql = <span style="color:Blue; new OleDbCommand(oracleExtract.ToString(), sourceConn);

<span style="color:Blue; using (OleDbDataReader reader = cmdExtractFromOracleSql.ExecuteReader())
{
<span style="color:Blue; object[] getObject = <span style="color:Blue; new <span style="color:Blue; object[2];


<span style="color:Blue; while (reader.Read())
{
<span style="color:Blue; for (<span style="color:Blue; int i = 0; i < 2; i++)
{
getObject = reader;
}

StringBuilder insertIntoReal = <span style="color:Blue; new StringBuilder();
StringBuilder insertIntoError = <span style="color:Blue; new StringBuilder();

insertIntoReal.Append(<span style="color:#A31515; "INSERT INTO MY (myID, myDATE) VALUES (@Param0, @Param1)");
insertIntoError.Append(<span style="color:#A31515; "INSERT INTO MY_ERROR (myID, myDATE) VALUES (@Param0, @Param1)");

<span style="color:Blue; using (SqlConnection destConnForInsert = <span style="color:Blue; new SqlConnection(destConnectionString))
{
destConnForInsert.Open();

<span style="color:Blue; try
{
SqlCommand insertReal = <span style="color:Blue; new SqlCommand(insertIntoReal.ToString(), destConnForInsert);

<span style="color:Blue; for (<span style="color:Blue; int i = 0; i < 2; i++)
{
insertReal.Parameters.AddWithValue(<span style="color:Blue; string.Format(<span style="color:#A31515; "@Param{0}", i.ToString()), getObject);
}

<span style="color:Blue; int ret = insertReal.ExecuteNonQuery();


}
<span style="color:Blue; catch (Exception sqlEx)
{
SqlCommand insertError = <span style="color:Blue; new SqlCommand(insertIntoError.ToString(), destConnForInsert);

<span style="color:Blue; for (<span style="color:Blue; int i = 0; i < 2; i++)
{
insertError.Parameters.AddWithValue(<span style="color:Blue; string.Format(<span style="color:#A31515; "@Param{0}", i.ToString()), getObject.ToString());
}

<span style="color:Blue; int ret = insertError.ExecuteNonQuery();

}

destConnForInsert.Close();

}
}

}
}
[/code]
<br/>
<br/>
<hr class="sig You are so wise...like a miniature budha covered in fur. -Anchorman

View the full article