Strongly typed dataset wont fill, table mappings problem? c#.net 2.0

Question

Hello all,

You've been excellent with my other questions previously - so here I am again, in need of some help!

I've got a query which joins three tables and a strongly typed dataset which has the columns defined for everything which comes back from the query. When I go to fill the dataadapter, nothing gets filled. I've copied the code from another method, so I presume it's okay - the only difference is that this query has joins. Any help appreciated, code follows:

Query:

select gsh.locid, locations.description, GSH.workorder, GSH.comstatus, GSH.teststatus, GSH.fireresult, GSH.lightresult, GSH.watercold, GSH.waterhot, GSH.responsedate, GSH.comments, GSH.testername from gsh_vhs_locations locs left outer join locations on locs.maximoloc = locations.location left outer join gsh_vhs_comms GSH on locs.LOCID = GSH.locid where gsh.insertdate > sysdate-7 order by locid, locations.description, GSH.workorder, GSH.comstatus, GSH.teststatus, GSH.fireresult, GSH.lightresult, GSH.watercold, GSH.waterhot, GSH.responsedate, GSH.comments, GSH.testername

Code:

ResponseSheet Tests = new ResponseSheet();
        DataSet ReturData = new DataSet();
        OracleDataAdapter da;
        try
        {
            using (OracleConnection conn = new OracleConnection(ConnString))
            {
                conn.Open();

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
 cmd.CommandText = @"select gsh.locid, locations.description, GSH.workorder, GSH.comstatus,   GSH.teststatus, GSH.fireresult, GSH.lightresult, GSH.watercold, GSH.waterhot, GSH.responsedate,  GSH.comments, GSH.testername 
from gsh_vhs_locations locs
left outer join locations on locs.maximoloc = locations.location
left outer join gsh_vhs_comms GSH on locs.LOCID = GSH.locid 
where gsh.insertdate > sysdate-7
order by locid, locations.description, GSH.workorder, GSH.comstatus, GSH.teststatus, GSH.fireresult,   GSH.lightresult, GSH.watercold, GSH.waterhot, GSH.responsedate, GSH.comments, GSH.testername ";

                da = new OracleDataAdapter(cmd.CommandText, conn);

da.MissingMappingAction = MissingMappingAction.Error;
da.TableMappings.Add("Table", "ResponseSheet");
                da.Fill(ReturData, "ResponseSheet");

            }

        }
        catch (Exception ex)
        {
            Console.WriteLine(TimeStamp() + ex.Message.ToString() + "Get Capture Report (TraceCode: 00019)");
        }


        return ReturData;
    }

As you can see, I've turned the error reporting for table mappings on, but I get no errors at run time, just an empty dataset (da = null)

Anything you can help with guys, just poke random google phrases at me if needs be - thanks :)

Gareth

Answer 1

Try handling the OnFillError event. I'm not sure it'll help, but it's worth a shot.

Answer 2

Try it without the joins and see if you get the results you expect- select locid from gsh_vhs_comms where insertdate > sysdate-7 if the fill method works with that query, try adding one join and see if it breaks it. If it does, the joins may not be supported. If not, there's something else going on, and focusing on the joins is misleading you.

Answer 3

Do the column names in your strongly typed dataset match the column names returned in the query?

Answer 4

Okay guys

I found the issue.

I'm very sorry, but for some reason my TNSnames wasn't set up for the new dev database (literally changed it that afternoon and had missed one letter off)

so it was hitting the catch and I wasn't spotting it.

I feel very stupid, but thank you all for your help anyway.

Henk has tidied up my code considerably and Beth made me take note that there was actually a problem with the query (the joins arent right, but it still returns data).

Thanks again all,

Gareth

EDIT: Further to this, the table mappings were out (a nights sleep seems to be the key to solving this one!) so for future reference anyone:

da.MissingMappingAction = MissingMappingAction.Passthrough; da.MissingSchemaAction = MissingSchemaAction.Add;

adds all the relevant table mappings to the data adapter and it fills properly now.