Export from DataGridView to access _ c#

[Member_14194390]

Hi I have the following code and I am trying to export information from datagridview to an Access document based but when i have error that was : System.Data.OleDb.OleDbException: 'Number of query values and destination fields are not the same.' please help me to complited this.becouse i cant fix this error. thank you this is my project code:

private DataTable GetDataTableFromDGV(DataGridView dgv)
{
var dt = new DataTable();

        foreach (DataGridViewColumn column in dgv.Columns)
        {
            if (column.Visible)
            {
                dt.Columns.Add();
            }

        }

        object\[\] cellValues = new object\[dgv.Columns.Count\];

        foreach (DataGridViewRow row in dgv.Rows)
        {
            for (int i = 0; i < row.Cells.Count; i++)
            {
                cellValues\[i\] = row.Cells\[i\].Value;
            }
            dt.Rows.Add(cellValues);
        }
        return dt;
    }

private void Exportbutton_Click(object sender, EventArgs e)

    {
        openFileDialog1.ShowDialog();

if (KindcomboBox.SelectedIndex == 0)
{
OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + openFileDialog1.FileName + ";Persist Security Info=True");

            cn.Open();

            DataTable dt = new DataTable();

            dt = GetDataTableFromDGV(TtmsdataGridView);

            OleDbCommand com = new OleDbCommand();

            com.CommandType = CommandType.Text;

            com.Connection = cn;

            int dtc = dt.Rows.Count;

            MessageBox.Show($"{dtc}");

            for (int i = 0; i < (dt.Rows.Count); i++)
            {
                com.CommandText = "INSERT INTO Kharid\_Detail(" + dt.Columns\[i\].ColumnName.Trim() + ")VALUES(" + (i++) + ",'" +
                    dt.Rows\[i\].ItemArray.GetValue(i) + "')";

                com.ExecuteNonQuery();
            }

[josda1000]

First off, as the discussion previous to this one states, you should never write a query like this without using SqlCommands and SqlParameters. PLEASE use that for production code. If this is not for production, then please take a look at your loop. You are looping on the number of Rows in the table, yet you are using the variable i to index on the table's Columns. I'm guessing you're trying to make the insert statement include all of the columns in the data table, and make a newly inserted row for each row in the data table. These would have to be two different for loops, concatenating to the same command string.

Josh Davis
This is what plays in my head when I finish projects.

[OriginalGriff]

A couple of things: 1) As mentioned, you've made a lot of mistakes here. Read this: Re: Question about login form with access database - C# Discussion Boards[^] and apply it to your code for the same reasons! 2) Read the error message:

Number of query values and destination fields are not the same.

Then look at your code:

com.CommandText = "INSERT INTO Kharid_Detail(" + dt.Columns[i].ColumnName.Trim() + ")VALUES(" + (i++) + ",'" +
dt.Rows[i].ItemArray.GetValue(i) + "')";

Lets make it a bit simpler to read:

com.CommandText = "INSERT INTO Kharid_Detail(" + x + ")VALUES(" + y + ",'" + z + "')";

You list one column, and provide two values ... 3) You have a DataTable. why not just use a DataAdapter to write them all in one go? Updating Data Sources with DataAdapters | Microsoft Docs[^]

Sent from my Amstrad PC 1640 Never throw anything away, Griff Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!

[Member_14194390]

Thanks for your help i edit my loop code and it work but i have on other error that was when i copy the result of store procedure from sql and past it into access dont have any problem that worked but when i trying with c# i see error that say

"

System.Data.OleDb.OleDbException: 'You must enter a value in the 'Kharid_Detail.KalaKhadamatName' field.'

"

when i print my result in c# every thing is true. this is my new code:

for (int j = 0; j < (TtmsdataGridView.ColumnCount); j++)

                for (int i = 0; i < (TtmsdataGridView.Rows.Count); i++)
                {
                    com.CommandText = "INSERT INTO Kharid\_Detail(" + TtmsdataGridView.Columns\[j\].Name.Trim() + ")VALUES(" + TtmsdataGridView.Rows\[i\].Cells\[j\].Value + ")";

                    com.ExecuteNonQuery();
                }

about dataadapter i'm beginer in programing and don't understand how to change my code for use it. thank you

[OriginalGriff]

WHat part of "never concatenate strings" is giving you problems? Or did you just not read what we told you?

Sent from my Amstrad PC 1640 Never throw anything away, Griff Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!