DataAdapter.Fill() returns 0 rows

[Bucky]

Wow, its been a long time since Ive visited this forum. Im having some trouble filling an empty DataSet from an Access database. Heres 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 didnt set the data member.

Have a look at this thread.
http://www.computerhelp.forum/showthread.php?t=91461


I could be wrong, but Its worth a shot.

 

[Bucky]

Well since Im not working with a DataGrid here, there isnt any DataMember property to set.

Heres the SQL statement Im 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) Ive never seen a oledb connection string like that in my life. Not to say that its wrong, but the oledb provider isnt throwing any exceptions, and it works in access. Heres 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 Im 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. Ive just learned that % is the SQL wildcard character, not *. Whoops.