DataAdapter.Fill() returns 0 rows

[Bucky]

Wow, it's been a long time since I've visited this forum. I'm having some trouble filling an empty DataSet from an Access database. Here's the code (I intentionally changed the path in the connection string):

 

	private DataSet GetData() {
		OleDbConnection connection = new OleDbConnection(@"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source='Data path here';Provider='Microsoft.Jet.OLEDB.4.0';");
		OleDbDataAdapter adapter = new OleDbDataAdapter(GetSql(), connection);
		DataSet data = new DataSet();

		connection.Open();
		adapter.Fill(data);
		connection.Close();
		return data;
	}

 

GetSql() returns a valid SQL string (I tested it out in Access), and a DataTable is created in the DataSet with the correct columns, but no rows are added to it. adapter.Fill() returns 0, and data.Tables[0].Rows.Count is also 0. There should be 1000+ records in there.

 

What could be wrong?

 

Thanks.

[SonicBoomAu]

Had a similiar problem myself yesterday. My problem was that I didn't set the data member.

 

Have a look at this thread.

http://www.xtremedotnettalk.com/showthread.php?t=91461

 

 

I could be wrong, but It's worth a shot. :D

[Bucky]

Well since I'm not working with a DataGrid here, there isn't any DataMember property to set.

 

Here's the SQL statement I'm working with, for reference:

SELECT * FROM [Combined Addresses] WHERE [Town] LIKE '*TownName*'

[SonicBoomAu]

Bucky, Not a C# person.

But in you first post you close the connection. Are you then retreiving the data?

 

connection.Close();

return data;

[HJB417]

#1) You can make your sample code more efficient

 

private DataSet GetData()
{
DataSet ds = new DataSet();
try
{
	string connStr = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase Locking Mode=1;Data Source='Data path here';Provider='Microsoft.Jet.OLEDB.4.0';";
	using(OleDbDataAdapter da = new OleDbDataAdapter(GetSql(), connStr))
	{
		da.Fill(ds);
		return ds;
	}
}
catch
{
	ds.Dispose();
	throw;
}
}

 

 

#2) I've never seen a oledb connection string like that in my life. Not to say that it's wrong, but the oledb provider isn't throwing any exceptions, and it works in access. Here's an example of the connection string I use

 

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\testdb.mdb"

 

or go to http://www.connectionstring.com for other examples.

 

So I'm guessing your connection string is suspect.

[Bucky]

Thanks for your replies. Those extra parameters in the connection string were left over from when I copied the string from an OleDataAdapter that had been automatically generated.

 

Anyway, the issue, now solved, was with the SQL statement. I've just learned that % is the SQL wildcard character, not *. Whoops.