insert to sql server

Question

I have to insert data from ms access to sql server. My logic is:

1. Get list of names in ms access.

2. Disable foreign key and triggers before insertion.

3. Read each table.

4. Change RowState of each row by SetAdded() method.

5. Finally insert it using command builder

Error

One field in table(Ms Access ) has datatype Date/Time. But it got only time value NO DATE part. During insertion it gives error. " SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. "

In dataset visualizer i saw value(s) 12/30/1899 1:01:01 AM.

How to solve it.

        try
        {
            oleCon = new OleDbConnection();
            oleCon.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Client Work\Client Docs\Don\ABCMotor.mdb;User Id=admin;Password=;";
            oleCon.Open();
            dt = new DataTable();
            dt = oleCon.GetSchema("tables");

            sqlCon = new SqlConnection(@"Server=ACER-PC\SQLEXPRESS2008R2;Database=ABC;Integrated Security=SSPI;");
            sqlCon.Open();
            SqlCommandBuilder sqlCmdB = new SqlCommandBuilder();
            sqlCmd = new SqlCommand();
            SqlTransaction sqlTran;
            sqlTran = sqlCon.BeginTransaction();
            sqlCmd.Connection = sqlCon;
            sqlCmd.Transaction = sqlTran;
            sqlCmd.CommandText = "sp_msforeachtable";
            sqlCmd.Parameters.AddWithValue("@Command1", "ALTER TABLE ? NOCHECK CONSTRAINT all");
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.ExecuteNonQuery();

            sqlCmd = new SqlCommand();
            sqlCmd.Connection = sqlCon;
            sqlCmd.Transaction = sqlTran;
            sqlCmd.CommandText = "sp_msforeachtable";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@Command1", "ALTER TABLE ? DISABLE TRIGGER  all");
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.ExecuteNonQuery();

            foreach (DataRow row in dt.Rows)
            {
                if (row["Table_Name"].ToString().StartsWith("MSys") == false)
                {
                    oleDa = new OleDbDataAdapter("Select * from [" + row["Table_Name"] + "]", oleCon);
                    ds = new DataSet();
                    oleDa.Fill(ds);

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        sqlDa = new SqlDataAdapter("Select * from [" + row["Table_Name"] + "]", sqlCon);
                        sqlDa.SelectCommand.Transaction = sqlTran;
                        foreach (DataRow item in ds.Tables[0].Rows)
                        {
                            item.SetAdded();
                        }
                        sqlCmdB.DataAdapter = sqlDa;

                        sqlCmdB.GetInsertCommand();

                        sqlDa.Update(ds);                                         
                    }
                }
            }

            sqlCmd = new SqlCommand();
            sqlCmd.Connection = sqlCon;
            sqlCmd.Transaction = sqlTran;
            sqlCmd.CommandText = "sp_msforeachtable";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@Command1", "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all");
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.ExecuteNonQuery();
            sqlCmd = new SqlCommand();
            sqlCmd.Connection = sqlCon;
            sqlCmd.Transaction = sqlTran;

            sqlCmd.CommandText = "sp_msforeachtable";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@Command1", "ALTER TABLE ? ENABLE TRIGGER  all");
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.ExecuteNonQuery();
            sqlTran.Commit();
        }
        catch (Exception ex)
        {

            MessageBox.Show(ex.Message);
        }

Answer 1

Try using FillSchema() to ensure your data adapter knows exactly what datatypes it is dealing with.

OleDbDataAdapter.FillSchema("", SchemaType.Source)

You will still need to call the Fill() method to get the data.