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

Question

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

And you've verified that query does actually return data, yes? — Dave, Jul 27 '09 at 16:36
yep I run it in toad and it fetches back what I need. good point though, you made me go check! — Gareth, Jul 27 '09 at 16:38
Just to make sure everything's kosher, it may be a good idea to run cmd.ExecuteNonQuery and check the return value to ensure that rows are being read. — Adam Robinson, Jul 27 '09 at 16:51

score 1 · Answer 1 · answered Jul 27 '09 at 16:59

1

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

answered Jul 27 '09 at 16:59

Greg

16,540
9
51
97

H H · Answer 2 · 2009-07-28T07:43:25.797

1

You should remove the call to conn.Open() and let .Fill() open the connection. Right now you are leaving the connection open. I am not sure if it is the main problem, but you may get better error reports.

Furthermore, you can eliminate the OracleCommand object since you are not really using it. The adapter will create a new Command object.

edited Jul 28 '09 at 07:43

answered Jul 27 '09 at 17:18

H H

263,252
30
330
514

good point, in my fury of debugging I ended up copying code from everywhere. Tidied up now :) Thanks – Gareth Jul 28 '09 at 08:01

score 1 · Answer 3 · answered Jul 27 '09 at 17:29

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.

score 1 · Answer 4 · answered Jul 28 '09 at 07:45

1

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

answered Jul 28 '09 at 07:45

cjk

45,739
9
81
112

Gareth · Accepted Answer · 2009-07-28T08:18:09.300

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.

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

5 Answers5