Access '07 database query problems in C #
I am doing a .NET module as part of my research. I just started with a teacher who kind of couldn't give me a solid foundation with .NET, so sorry noobishness.
I am making a fairly simple and generic database driven application. I am using C # and I am accessing a Microsoft Access 2007 database.
I have put the database stuff in my class with methods just spitting out OleDbDataAdapters that I use to commit. I supply any methods that will prepare a request for a DataSet object from the main program where I store data (multiple tables in db).
I made a very general private method that I use to execute SQL SELECT queries and some public methods wrap this method to get products, orders.etc (this is a generic retail database).
The general method uses a separate Connect method for the actual connection, and it looks like this:
private static OleDbConnection Connect()
{
OleDbConnection conn = new OleDbConnection(
@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\db.accdb");
return conn;
}
The general method looks like this:
private static OleDbDataAdapter GenericSelectQuery(
DataSet ds, string namedTable, String selectString)
{
OleDbCommand oleCommand = new OleDbCommand();
OleDbConnection conn = Connect();
oleCommand.CommandText = selectString;
oleCommand.Connection = conn;
oleCommand.CommandType = CommandType.Text;
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = oleCommand;
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(ds, namedTable);
return adapter;
}
The wrapper methods simply traverse the DataSet they received from the main program, the namedtable line is the name of the table in the dataset, and you pass the query you want to make.
It doesn't matter what query I give it (even something simple like SELECT * FROM TableName). I am still getting an OleDbException stating that there was an en error in the FROM clause. I only resorted to building queries with Access, but still not used. Obviously there is something wrong with my code, which doesn't really surprise me.
Here are some wrapper methods that I am using.
public static OleDbDataAdapter GetOrderLines(DataSet ds)
{
OleDbDataAdapter adapter = GenericSelectQuery(
ds, "orderlines", "SELECT OrderLine.* FROM OrderLine;");
return adapter;
}
They all look the same: it's just SQL that changes.
source to share
Have you tried something simpler to see if you have a connection to the table you are looking for. Something like
DataSet ds = new DataSet();
using (OleDbConnection myConnection = new OleDbConnection
(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\db.accdb"))
{
myConnection.Open();
OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT OrderLine.* FROM OrderLine;, myConnection);
myAdapter.TableMappings.Add("Table", "TestTable");
myAdapter.Fill(ds);
}
Then from there check if the file is in ds with
ds.Tables[0].Rows.Count()
This will actually show you if you hit the DB and get the results. From there, you can make it look more elegant.
source to share