0

Have some issue with importing Excel Documnet in dataGrid with access Database (ad.Fill(dt)). Can someone know how to fix it? Is the main problem due to an already loaded database or having two connections? Try to edit quetes, builds, but nothing no get result. Google this problem, but nothing similiar. Sorry for my stupidity.

public partial class dashboard : Form
{
    public static string ConnectString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = Books.mdb";
    public OleDbConnection myConnection;
    
    
    public dashboard()
    {
        InitializeComponent();
        myConnection = new OleDbConnection(ConnectString);          
        myConnection.Open();
    }

    public void SendDataToGrid(params object[] data)
    {
        dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].SetValues(data);
        //Определяем индекс текущей строки, записываем в неё пришедшие значения 
    }
    
    private void dashboard_Load(object sender, EventArgs e)
    {
        Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("en-US");
 
        this.bookListTableAdapter.Fill(this.booksDataSet.BookList);

    }

    private void button1_Click(object sender, EventArgs e)
    {
        AddingForm addingform = new AddingForm(this);
        addingform.ShowDialog();
        
    }
   
    private void button2_Click(object sender, EventArgs e)
    {
        try
        {
            DialogResult dr = MessageBox.Show("Delete?", "Deleting", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
            if (dr == DialogResult.Yes)
            {
                booksDataSet.BookList.Rows.Find(dataGridView1.CurrentRow.Cells[0].Value).Delete();
                bookListTableAdapter.Update(booksDataSet.BookList);
                booksDataSet.BookList.AcceptChanges();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

    private void button3_Click(object sender, EventArgs e)
    {
        EditForm editform = new EditForm(this);
        editform.ShowDialog();
    }

    private void button4_Click(object sender, EventArgs e)
    {
        dashboard_Load(sender, e);
    }
  

    private void dataGridView1_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
    {
        int index = e.RowIndex;
        string indexStr = (index + 1).ToString();
        object header = this.dataGridView1.Rows[index].HeaderCell.Value;
        if (header == null || !header.Equals(indexStr))
            this.dataGridView1.Rows[index].HeaderCell.Value = indexStr;
    }

    Bitmap bitmap;
    private void button5_Click(object sender, EventArgs e)
    {
        int height = dataGridView1.Height;
        dataGridView1.Height = dataGridView1.RowCount * dataGridView1.RowTemplate.Height * 2;
        bitmap = new Bitmap(dataGridView1.Width, dataGridView1.Height);
        dataGridView1.DrawToBitmap(bitmap, new Rectangle(0, 0, dataGridView1.Width, dataGridView1.Height));
        printPreviewDialog1.PrintPreviewControl.Zoom = 1;
        printPreviewDialog1.ShowDialog();
        dataGridView1.Height = height;
    }

    private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
    {
        e.Graphics.DrawImage(bitmap, 0, 0);
    }

    private void button6_Click(object sender, EventArgs e)
    {
        Process.Start(@"1.pdf");

    }

    protected override void OnPaintBackground(PaintEventArgs e)
    {
        using (LinearGradientBrush brush = new LinearGradientBrush(this.ClientRectangle,
                                                                  Color.White,
                                                                   Color.FromArgb(239, 247, 237),
                                                                   100000F))
        {
            e.Graphics.FillRectangle(brush, this.ClientRectangle);
        }
    }

    private void button7_Click(object sender, EventArgs e)
    {
        About about = new About(this);
        about.ShowDialog();
    }

    private void button8_Click(object sender, EventArgs e)
    {
        Excel.Application exAAP = new Excel.Application();
        exAAP.Workbooks.Add();
        Excel.Worksheet wsh = (Excel.Worksheet)exAAP.ActiveSheet;
        int i, j;
        for (i = 0; i <= dataGridView1.RowCount - 2; i++)
        {
            for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
            {
                wsh.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString();

            }
        }

        exAAP.Visible = true;
    }
   
    private void button9_Click(object sender, EventArgs e)
    {
        OpenFileDialog opn = new OpenFileDialog();
        if (opn.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            this.textBox1.Text = opn.FileName;
        }
    }

    public void button10_Click(object sender, EventArgs e)
    {
        string path = "provider=microsoft.jet.OLEDB.4.0;Data source =" + textBox1.Text + ";Extended properties=\"Excel.8HDR=yes;\"; ";
        OleDbConnection  ExcelConnection = new OleDbConnection(path);
        OleDbDataAdapter ad = new OleDbDataAdapter("select * from [" + textBox2.Text + "$]", ExcelConnection);
        DataTable dt = new DataTable();
        ad.Fill(dt); //Error trigger!
        dataGridView1.DataSource = dt;
    }
}
  • I edited the code, but now I get the error 'Invalid name' 123 $ '. Make sure it does not contain invalid characters or punctuation marks, and is not too long. '. linked again with this line ad.Fill(dt) –  Jun 04 '21 at 09:36
  • The connection string for Excel is mangled. Take definitions, descriptions of the connection string parts and sample usage from [here](https://stackoverflow.com/a/54352568/7444103). Also, use proper case and don't add spaces if not required (as is in, e.g., `Data Source=` )-- Are you really using ancient `mdb` and `xls` file formats? – Jimi Jun 04 '21 at 10:45
  • @Jimi , yep, it's necessary - not at my will :By the way, i believe that I solved the problem (it seems), except for the fact that now DataGrid always becomes empty, when i load excel file to table. Maybe i must rewrite code using your sample. –  Jun 04 '21 at 15:09
  • It can happen, if you have empty rows at the beginning (which may also compromise data Type auto-detection). You can then specify the range of the cells. See the example here: [How to populate a DataGridView based on Excel search results and remove blank headers from the DataGridView?](https://stackoverflow.com/a/55240079/7444103) -- Fix the Excel connection string before anything else, as described in the previous link and also fix the objects declarations (Connection, DataAdapter - possibly, don't use it here-, Command): those objects need to be disposed of, to also close the connection. – Jimi Jun 04 '21 at 15:15
  • @Jimi, thank you very much - application work! All that remains is to combain implementation of adding, editing and deleting with basic (adding and etc), but i want to think it will be easy. Thank you again. –  Jun 04 '21 at 18:10

0 Answers0