none
C# Exporting to Access Data Base missing rows RRS feed

  • Question

  • When I export my datagrid to Access it not exporting all the rows. I'm using Spire.DataExport and found this code.

    I don't see where it's limiting the rows. Any ideas

    Thanks


    private void btn_Export_Click(object sender, EventArgs e)
            {
                
                System.Data.OleDb.OleDbConnection oleDbConnection1
                     = new System.Data.OleDb.OleDbConnection();
                oleDbConnection1.ConnectionString
                    = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Roster.accdb";
    
                System.Data.OleDb.OleDbCommand oleDbCommand1
                    = new System.Data.OleDb.OleDbCommand();
                oleDbCommand1.CommandText = "select * from [Test Trial Roster] ";  ////* means all database column
                oleDbCommand1.Connection = oleDbConnection1;
    
                Spire.DataExport.Access.AccessExport accessExport1
                    = new Spire.DataExport.Access.AccessExport();
    
                accessExport1.DatabaseName = "Roster Export File.accdb";
                accessExport1.DataFormats.CultureName = "zh-CN";
                accessExport1.DataFormats.Currency = "c";
                accessExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
                accessExport1.DataFormats.Float = "g";
                accessExport1.DataFormats.Integer = "g";
                accessExport1.DataFormats.Time = "H:mm";
                accessExport1.SQLCommand = oleDbCommand1;
                accessExport1.TableName = "ExportData";
    
                oleDbConnection1.Open();
                accessExport1.SaveToFile();
            }


    Booney440

    Wednesday, September 25, 2019 5:45 PM

Answers

  • understood, what would be an alternative way to accomplish what I need? I looked for 2 days and that's the only thing I could find.

    Booney440

    Something like this (taken from here) which shows an idea which may be tedious if there are a good deal of data columns.

    public static void UpDataDB(string DB, DataTable DT)
    {
        string ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DB +
                            ";User Id=admin;Password=;";
        string SQL = "SELECT * FROM MeterReadings WHERE ReadingID=0";
        string INSERT = "INSERT INTO MeterReadings(Meter1, Meter2, Meter3, Meter4) " +
                        "VALUES (@Meter1, @Meter2, @Meter3, @Meter4)";
    
        OleDbConnection OleConn = new OleDbConnection(ConnString);
        OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);
        OleAdp.InsertCommand = new OleDbCommand(INSERT);
        OleAdp.InsertCommand.Parameters.Add("@Meter1", OleDbType.Integer, 8, "Meter1");
        OleAdp.InsertCommand.Parameters.Add("@Meter2", OleDbType.Integer, 8, "Meter2");
        OleAdp.InsertCommand.Parameters.Add("@Meter3", OleDbType.Integer, 8, "Meter3");
        OleAdp.InsertCommand.Parameters.Add("@Meter4", OleDbType.Integer, 8, "Meter4");
        OleAdp.InsertCommand.Connection = OleConn;
        OleAdp.InsertCommand.Connection.Open();
        OleAdp.Update(DT);
        OleAdp.InsertCommand.Connection.Close();
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Booney440 Friday, September 27, 2019 4:32 PM
    Thursday, September 26, 2019 3:43 PM
    Moderator

All replies

  • Hello,

    We don't support third party libraries, use their support

    https://www.e-iceblue.com/Introduce/data-export-for-net-intro/Support.html#.XYvDByhKhhE


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, September 25, 2019 7:42 PM
    Moderator
  • understood, what would be an alternative way to accomplish what I need? I looked for 2 days and that's the only thing I could find.

    Booney440

    Wednesday, September 25, 2019 9:51 PM
  • Hi Booney440,

    Thank you for posting here.

    You could try the following code to convert datagriview to access database successfully.

       private void button1_Click(object sender, EventArgs e)
            {
                Spire.DataExport.Access.AccessExport accessExport = new
        Spire.DataExport.Access.AccessExport();
                accessExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
                accessExport.DataTable = this.dataGridView1.DataSource as DataTable;
                accessExport.DatabaseName = @"D:\test.mdb";
                accessExport.TableName = "ExportFromDatatable";
                accessExport.SaveToFile();
                MessageBox.Show("success");
            }
    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 26, 2019 6:33 AM
    Moderator
  • Getting error (Data source (DataTable) property has not been initialized). would like to use save file dialog also.

    Booney440

    Thursday, September 26, 2019 3:03 PM
  • understood, what would be an alternative way to accomplish what I need? I looked for 2 days and that's the only thing I could find.

    Booney440

    Something like this (taken from here) which shows an idea which may be tedious if there are a good deal of data columns.

    public static void UpDataDB(string DB, DataTable DT)
    {
        string ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DB +
                            ";User Id=admin;Password=;";
        string SQL = "SELECT * FROM MeterReadings WHERE ReadingID=0";
        string INSERT = "INSERT INTO MeterReadings(Meter1, Meter2, Meter3, Meter4) " +
                        "VALUES (@Meter1, @Meter2, @Meter3, @Meter4)";
    
        OleDbConnection OleConn = new OleDbConnection(ConnString);
        OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);
        OleAdp.InsertCommand = new OleDbCommand(INSERT);
        OleAdp.InsertCommand.Parameters.Add("@Meter1", OleDbType.Integer, 8, "Meter1");
        OleAdp.InsertCommand.Parameters.Add("@Meter2", OleDbType.Integer, 8, "Meter2");
        OleAdp.InsertCommand.Parameters.Add("@Meter3", OleDbType.Integer, 8, "Meter3");
        OleAdp.InsertCommand.Parameters.Add("@Meter4", OleDbType.Integer, 8, "Meter4");
        OleAdp.InsertCommand.Connection = OleConn;
        OleAdp.InsertCommand.Connection.Open();
        OleAdp.Update(DT);
        OleAdp.InsertCommand.Connection.Close();
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Booney440 Friday, September 27, 2019 4:32 PM
    Thursday, September 26, 2019 3:43 PM
    Moderator