Member Avatar for Devasya

Hi all

I just want to know, how to insert the data from a datagrid into access database?

please help me....
:'(

  • 4 Contributors
  • 7 Replies
  • 707 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by bbauraj

Dani AI

Generated

A quick, practical summary and a safe pattern for inserting DataGridView rows into an Access table.

In ’s snippet the blockers are mostly logical: loop bounds are mixed up (row vs column), the temporary SQL/value string isn’t reset per row, and an undefined variable (l) breaks the flow (as noted). Building VALUES by concatenating quotes is fragile (and unsafe). The reliable approach is to prepare one parameterized INSERT, reuse it for each DataGridViewRow, skip the grid’s new row, and execute inside a transaction.

Example pattern (uses parameterized OleDb, avoids string concatenation):

string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\c.mdb";
using (var cn = new OleDbConnection(connStr))
{
    cn.Open();
    using (var tx = cn.BeginTransaction())
    using (var cmd = cn.CreateCommand())
    {
        cmd.Transaction = tx;
        cmd.CommandText = "INSERT INTO customer ([name],[address]) VALUES (?, ?)";
        cmd.Parameters.Add(new OleDbParameter("name", OleDbType.VarChar, 255));
        cmd.Parameters.Add(new OleDbParameter("address", OleDbType.VarChar, 255));

        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            if (row.IsNewRow) continue;
            cmd.Parameters[0].Value = row.Cells["nameColumn"]?.Value ?? DBNull.Value;
            cmd.Parameters[1].Value = row.Cells["addressColumn"]?.Value ?? DBNull.Value;
            cmd.ExecuteNonQuery();
        }

        tx.Commit();
    }
}

Troubleshooting checklist: confirm correct row/column indices (or use column names), skip IsNewRow, ensure parameter count/order matches the SQL (OleDb uses ? placeholders and positional binding), wrap multiple inserts in a transaction, handle nulls with DBNull.Value, and catch/rollback on exceptions. Wrap connections/commands in using so resources are disposed. ’s links were on the right topic, but the core fixes above address the specific logic errors in the original thread and avoid the single-row-only symptom.

Recommended Answers

All 7 Replies

Member Avatar for Renukavani

im not geting clearly. do u want to transfer database data to datagrid or what ur expect...

Hi all

I just want to know, how to insert the data from a datagrid into access database?

please help me....
:'(

Member Avatar for Devasya

I fill the datagrid with a data and I don't know how to insert that data into a access database. I want to transfer the data from datagrid to the access database

Member Avatar for Renukavani

check these r links

Member Avatar for Devasya

Dear all,

this is my code for transfer data from datagrid view to access database.
but why i can't transfer data more than one row?

can somebody help me?

please...:'(
------------------------------------------------------------------------------------------------
connection class is contains:

static string strCon = "provider=Microsoft.Jet.OLEDB.4.0;data source=D:\\c.mdb";
    public static OleDbConnection oleCon = new OleDbConnection(strCon);
    public static OleDbCommand oleCommand = oleCon.CreateCommand();

------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace test
{
    public partial class Form5 : Form
    {
        public Form5()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            connection.oleCon.Open();
            string[,] a= new string[100,100];
            int countRow = dataGridView1.RowCount;
            int countCol = dataGridView1.ColumnCount;
              
            Console.WriteLine("row = " + countRow);
            Console.WriteLine("col = " + countCol);

            int j = 0;
            int k = 0;

            string data = "'";
            for (k = 0; k < (countRow-1); k++)
            {
                
                for (j = 0; j < (countCol); j++)
                {
                    
                    Console.WriteLine("j=" + j);
                    Console.WriteLine("k=" + k);
                    a[j,k] = string.Format("" + dataGridView1[j, k].Value);

                    if( (j == (countCol - 1))&&(k == l))
                    {
                        data = data + a[j, k] + "'";
                    }else
                         if (j < (countRow - 1)) 
                    {
                        data = data + a[j, k] + "','";
                    }
                    Console.WriteLine("a[" + j + "," + k + "]=" + a[j, k]);
                    Console.WriteLine("a = " + data);
      
                }
                connection.oleCommand.CommandText =
                "INSERT INTO customer(name,address) " +
                "VALUES (" + data + ")";
                connection.oleCommand.ExecuteNonQuery();
            }
        }
    }
}
Member Avatar for Devasya

Just forget it this problem is solved,thanks all

Member Avatar for krishnisilva

hey ,
in line 43:
if( (j == (countCol - 1))&&(k == l))
what is l
it is not defined??

and also i need to know what is this code for?? i am trying to figure it out but it is a little bit difficult.

thankx

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.