I'm using the ODBC Microsoft Text Driver to load a csv file into the dataset to be displayed in the datagridview, the DGV has no columns in at present. I can load the file successfully but I encounter 2 problems:
- The 2 columns load fine but a third 'noname' column is also loaded - why does this happ开发者_StackOverflow社区en?
- When I do an update using a DataAdapter, I get this exception:
ERROR [HYS22] [Microsoft][ODBC Text Driver] The INSERT INTO statement contains the following unknown field name: 'NoName'. Make sure you have typed the name correctly, and try the operation again.
Does anybody know why this occurs?
//RefreshData Method()
OdbcConnection conn = new OdbcConnection(@"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\;");
OdbcCommand comm = new OdbcCommand("Select * FROM test.csv", conn);
OdbcDataAdapter adapter = new OdbcDataAdapter(comm);
DataSet ds = new DataSet();
adapter.Fill(ds);
// ds.Tables[0].Columns.Remove("noname");
dataGridView1.DataSource = ds.Tables[0];
}
//Button Click Method
OdbcConnection conn = new OdbcConnection(@"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\;");
string qry = "Select * FROM test.csv";
OdbcDataAdapter da = new OdbcDataAdapter();
da.SelectCommand = new OdbcCommand(qry, conn);
OdbcCommandBuilder cb = new OdbcCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
//ds.Tables[0].Columns.Remove("noname");
DataTable dt = ds.Tables[0];
// Add a row
DataRow newRow = dt.NewRow();
newRow[0] = this.textBox1.Text;
newRow[1] = int.Parse(this.textBox2.Text); ;
dt.Rows.Add(newRow);
da.Update(ds.Tables[0]);
conn.Close();
this.Refresh();
CSV Data:
empName,salary,
charles,4324343, andrew,31343970, freddy,998788966, loop,8878743,
There is a comma at the end of each row of data. I suspect that the ODBC text driver reads this as an extra column with no data in it and no name. Hence the NoName column.
This probably also explains your problem importing the data to the database, since you have an extra column that cannot be mapped back to the database.
There may be an option in the ODBC Text Driver to ignore empty columns, but I am not sure. If there isn't then you can remove it by including the commented out line:
//ds.Tables[0].Columns.Remove("noname");
This should remove the column from the DataTable and allow you to import the data to the database.
精彩评论