Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VS2008 C# ms-sql-server
Hi,
i had coded a utility to copy tables with data from one source mdb to a target mdb.
Now my project needs to copy data from a mdb file to a SQL Express database into a mdf file. My previous code was as follows:
 
 DataTable schemaTable;
            OleDbConnection conn = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
            conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                   @"Data source=" + Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "Source.mdb";
            conn.Open();
            cmd.Connection = conn;
 
            string templetDataTable = AppDomain.CurrentDomain.BaseDirectory + @"\\Target.mdb";
            string clientDataTable = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "Source.mdb";
            string templetBackupDataTable = AppDomain.CurrentDomain.BaseDirectory + @"\\SourceBackup.mdb";
            if (File.Exists(templetDataTable))
            {   //**********MDB Data Migration**************//
                schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                             new Object[] { null, null, null, "TABLE" });
              
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {   
                        query = "INSERT INTO " + schemaTable.Rows[i].ItemArray[2].ToString() + " IN '" + templetDataTable + "' SELECT * FROM " + schemaTable.Rows[i].ItemArray[2].ToString() + "";
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = query;
                        try
                        {
                            cmdresults = cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            txtErrorDetails.Visible = true;
                            txtErrorDetails.Text = ex.ToString();
                            errorLogger = new StreamWriter(@"ErrorLog" + DateTime.Now.ToString("ddMMyyyyMMhhss") + ".Log");
                            errorLogger.WriteLine(txtErrorDetails.Text + " \n Error While Updating Table.."
                                                   + schemaTable.Rows[i].ItemArray[2].ToString() + "{" + ex.ToString() + "}");
                            errorLogger.Flush();
                            continue;
                        }
                    
                }
                conn.Close();
 

But this code is not going to work as sql database need to establish connection.
Can anyone please suggest as how can i modify the existing code to do the same?
 

Regards
honeyashu
Posted 4-Jun-12 21:24pm
Comments
digimanus at 5-Jun-12 9:06am
   
why not via SSMS Sql Server management Studio ?
digimanus at 5-Jun-12 9:08am
   
why not create a connection to sql server (sqlConnection class) that calls a stored Procedure that reads and insert the MDB Acces file via OpenRowSet command in SQL?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 334
1 Kornfeld Eliyahu Peter 325
2 Maciej Los 239
3 OriginalGriff 188
4 King Fisher 185
0 OriginalGriff 6,303
1 DamithSL 4,764
2 Maciej Los 4,306
3 Kornfeld Eliyahu Peter 3,914
4 Sergey Alexandrovich Kryukov 3,538


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 5 Jun 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100