How to implement "Right Outer Join" query in access database using C#?

Question

I want to write RightOuterJoin query to retrieve data from access database.. How can i implement this query in c#? I tried Like this

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=Sample1.accdb; Jet OLEDB:Engine Type=5";

string sql = "SELECT t1.mobileno,t1.RetailerNo,t1.custcode,t2.RET NO FROM [C:\\Sample1.accdb].[Table1] as t1 RIGHT OUTER JOIN [C:\\Sample1.accdb].[Table2] as t2 ON t1.RetailerNo = t2.RET NO";


database = new OleDbConnection(connectionString);
database.Open();
OleDbCommand cmd1 = new OleDbCommand(sql, database);
OleDbDataAdapter da = new OleDbDataAdapter(cmd1);

//database.Open();
//conn.Open();
cmd1.ExecuteNonQuery();
System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);

Answer 1

I think you may have a problem with your connection string. It uses the Jet provider, but the database type is the newer ACCDB format. Jet does not work with ACCDB.

For ACCDB, you need the ACE provider. If you don't have Office 2007 or 2010 installed, you can download and install the Access Database Engine Redistributable.

Here is the provider section from a working connection string:

Provider=Microsoft.ACE.OLEDB.12.0

See Connection strings for Access 2007 for more details.

Once you have a working connection to Sample1.accdb, revise your SQL statement to this:

SELECT t1.mobileno,t1.RetailerNo,t1.custcode,t2.[RET NO]
FROM 
    Table1 as t1
    RIGHT JOIN Table2 as t2
    ON t1.RetailerNo = t2.[RET NO]

Notes:

1. Since you're already connected to Sample1.accdb, you don't need to prefix the table names with the path to the db file.
2. For a field name which includes a space (RET NO), enclose the field name in square brackets.