DataAdapter.Fill(Dataset)

Question

i try to get some Data from a Access Database via OleDB in a DataSet. But the DataSet is empty after the Fill() method. The same statement works and return 1 row when i trigger them manually in D*.

OleDbConnection connection = 
   new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Inventar.accdb");
DataSet1 DS = new DataSet1();
connection.Open();

OleDbDataAdapter DBAdapter = new OleDbDataAdapter(
    @"SELECT tbl_Computer.*,  tbl_Besitzer.*
      FROM tbl_Computer 
      INNER JOIN tbl_Besitzer ON tbl_Computer.FK_Benutzer = tbl_Besitzer.ID 
      WHERE (((tbl_Besitzer.Vorname)='ma'));", 
    connection);

DBAdapter.Fill(DS);

Thanks in advance.

New working code:

DataSet ds = new DataSet();
OleDbDataAdapter DBAdapter = new OleDbDataAdapter();

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Inventar.accdb");
string query = @"
    SELECT tbl_Computer.*,  tbl_Besitzer.*
    FROM tbl_Computer 
    INNER JOIN tbl_Besitzer ON tbl_Computer.FK_Benutzer = tbl_Besitzer.ID 
    WHERE (((tbl_Besitzer.Vorname)='ma'));";

connection.Open();

using (OleDbCommand command = new OleDbCommand(query, connection))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
    adapter.Fill(ds);
}

Dictionary<string, string> DictValues = new Dictionary<string, string>();

for (int i = 0; i <= ds.Tables[0].Rows[0].ItemArray.Length - 1; i++)
{
    MessageBox.Show(ds.Tables[0].Rows[0].ItemArray[i] + " -- " + ds.Tables[0].Rows[0].Table.Columns[i]);
    DictValues.Add(ds.Tables[0].Rows[0].Table.Columns[i].ToString(), ds.Tables[0].Rows[0].ItemArray[i].ToString());
}

Now The Right code is posted above, with an Dictonary to access the Data more comfortable. hope anybody find help in this post. Thank you all for get it work !

Do you have any tables in dataset? I think you may need to add some tables with corresponding structure — Blablablaster, Jun 30 '11 at 08:58
@Blabla: It's not required. Schema is populated automatically — abatishchev, Jun 30 '11 at 09:03

score 21 · Accepted Answer · answered Jun 30 '11 at 09:02

21

DataSet ds = new DataSet();

using (OleDbConnection connection = new OleDbConnection(connectionString))
using (OleDbCommand command = new OleDbCommand(query, connection))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
    adapter.Fill(ds);
}

return ds;

answered Jun 30 '11 at 09:02

abatishchev

98,240
88
296
433

@abatishchev: That is just a recommendation. Isn't it? Does it solve the problem ?? – Akram Shahda Jun 30 '11 at 09:05
@Akram: If the query is correct, this code will do the work 100% – abatishchev Jun 30 '11 at 09:06
@abatishchev: Still not specifying the problem, nor providing a solution for it. I think it is not an answer? – Akram Shahda Jun 30 '11 at 09:11
and how can i get the values ? ds.Tables[0].Rows[0].ItemArray[0] don´t work – Marcus Jun 30 '11 at 09:14
@User: Do you need only the value of first column in first row? – abatishchev Jun 30 '11 at 09:18
i need the format same as in the db. i put some code in my question you can have a look it´s alpha :-) hope you can give me a hint – Marcus Jun 30 '11 at 09:46
2

@User: I see you call `ds.Tables[0].Rows[0]` every time. Don't do that; cache instead: `var arr = ds.Tables[0].Rows[0].ItemArray` and next use just `arr` – abatishchev Jun 30 '11 at 10:12
then use arr[0]["Columnname"] for access ? – Marcus Jun 30 '11 at 10:22
`DataRow` provides strongly typed access via [`.Field()`](https://learn.microsoft.com/en-us/dotnet/api/system.data.datarowextensions.field?view=net-5.0): `int value = arr[0].Field("Columnname");`. – D M Aug 25 '21 at 13:20

Hasan Fahim · Answer 2 · 2011-06-30T09:18:00.760

2

You need to do this:

OleDbConnection connection = new OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Inventar.accdb");
DataSet DS = new DataSet();
connection.Open();

string query = 
    @"SELECT tbl_Computer.*,  tbl_Besitzer.*
    FROM tbl_Computer 
    INNER JOIN tbl_Besitzer ON tbl_Computer.FK_Benutzer = tbl_Besitzer.ID 
    WHERE (((tbl_Besitzer.Vorname)='ma'))";
OleDbDataAdapter DBAdapter = new OleDbDataAdapter();
DBAdapter.SelectCommand = new OleDbCommand(query, connection); 
DBAdapter.Fill(DS);

By the way, what is this DataSet1? This should be "DataSet".

edited Jun 30 '11 at 09:18

answered Jun 30 '11 at 08:58

Hasan Fahim

3,875
1
30
51

Sorry I cant see differences, [OleDbDataAdapter](http://msdn.microsoft.com/en-us/library/by7t044k.aspx) constructor takes first parameter as select command text and it is used by SelectCommand. looks like you wrote same thing in different way. Can you explain? – Renatas M. Jun 30 '11 at 09:05
i´m with Reniuz, i think the new code brings no changes on logic – Marcus Jun 30 '11 at 09:06
@User: Please don't propose such edits. Just edit your own post (question) – abatishchev Jun 30 '11 at 09:07
1

@ Akram Shahda and @Reniuz. The problem is just a typo I think. Rather than writing DataSet, the user had written DataSet1. – Hasan Fahim Jun 30 '11 at 09:20
how to get access more comfortable as ds.Tables[0].Rows[0].ItemArray ? in this way the object[] has no Columnnames. – Marcus Jun 30 '11 at 09:32
@user820831. Do you want to access data from the dataset? – Hasan Fahim Jun 30 '11 at 09:44
To access data, do this ds.Tables[0].Rows[0][0]. This would return the data present in the first row / first column of the first table. – Hasan Fahim Jun 30 '11 at 09:47

score 0 · Answer 3 · edited Nov 12 '14 at 15:29

0

leDbConnection connection = 
new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Inventar.accdb");
DataSet1 DS = new DataSet1();
connection.Open();

OleDbDataAdapter DBAdapter = new OleDbDataAdapter(
@"SELECT tbl_Computer.*,  tbl_Besitzer.*
  FROM tbl_Computer 
  INNER JOIN tbl_Besitzer ON tbl_Computer.FK_Benutzer = tbl_Besitzer.ID 
  WHERE (((tbl_Besitzer.Vorname)='ma'));", 
connection);

edited Nov 12 '14 at 15:29

Jan Doggen

8,799
13
70
144

answered Nov 12 '14 at 15:26

sexy

1

1

Your answer should contain an explanation of your code and a description how it solves the problem. – AbcAeffchen Nov 12 '14 at 15:44

score 0 · Answer 4 · answered Jul 17 '18 at 13:40

it works for me, just change: Provider=Microsoft.Jet.OLEDB.4.0 (VS2013)

OleDbConnection connection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\\GENERAL\\OFMPTP_PD_SG.MDB");
DataSet DS = new DataSet();
connection.Open();

string query =
@"SELECT * from MONTHLYPROD";
OleDbDataAdapter DBAdapter = new OleDbDataAdapter();
DBAdapter.SelectCommand = new OleDbCommand(query, connection);
DBAdapter.Fill(DS);

Luis Montoya

DataAdapter.Fill(Dataset)

4 Answers4

Linked