0

In my windows form application I have some datagridview that fills using calculations that the program do. now I want to design a report based on datagridviews data by searching I figured it out that to do so I have to put my data into a database table and then get them from database to put them into a dataset in my C# program so I can access that data to design a report.

but this way has it's own problem such as my datagridviews have different type of data like string or int and problem is how to put them into a table

for example I convert a datagrid to a datatable like this:

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

    foreach (DataGridViewColumn column in dgv.Columns)
    {
        if (column.Visible)
        {
            dt.Columns.Add();
            if (column.Name != "")
            {
                dt.Columns[column.Index].ColumnName = column.Name;
            }
        }
    }
    object[] CellValue = new object[dgv.Columns.Count];

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

    return dt;
}

and this is my datatable:

enter image description here

now I want to insert it into a database like this and I get syntax error:

Syntax error in INSERT INTO statement.

using (OleDbCommand Cmd = new OleDbCommand())
{
   Cmd.Connection = Connection;

   Cmd.CommandText = "Delete * From rAnimalPerformance";
   Cmd.ExecuteNonQuery();

   foreach (DataRow Dr in cAnimalPerformancedt.Rows)
   {
       Cmd.CommandText = @"Insert Into rAnimalPerformance (Val, Desc) 
                                       Values ("
                                               + "'" + Dr["Val"] + "', '"     
                                               + Dr["Desc"] + "')";

       Cmd.ExecuteNonQuery();
   }
}

this the only way? for situation like this what is the best way to design a report

SaraniO
  • 587
  • 6
  • 26
  • What specific syntax error? or is it just plain syntax error? Also you should always add a parameter as good practice when dealing with queries. – P. Pat Mar 07 '17 at 09:00
  • First thing todo is to use SqlParameters. And no this is not the only way. You can pass dataset straight to your report. – Renatas M. Mar 07 '17 at 09:01
  • @P.Pat Syntax error in INSERT INTO statement. – SaraniO Mar 07 '17 at 09:06
  • @Reniuz I'm using access database, in a foreach loop how should I use parameters? – SaraniO Mar 07 '17 at 09:06
  • [Like with other sql parameters](http://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database) – Renatas M. Mar 07 '17 at 09:14
  • @SaraniO What I can suggest is that you rewrite your whole query in inserting. 1 You have a delete query before insert which is confusing. 2. You declared two `Cmd.ExecuteNonQuery` and no indication in your code above if connection was ever opened(`Cmd.Open()`)3. @Reniuz link is what you should follow. – P. Pat Mar 07 '17 at 09:17
  • 1
    And [here](http://stackoverflow.com/a/40367119/754438) is starting point to how to pass data and create report dynamically. – Renatas M. Mar 07 '17 at 09:20

0 Answers0