CODEWITHSUNDEEP
c#ado.netdatatabledataset
ledgeJumper
ledgeJumper

Reputation: 3630

Why does my Datatable not have any results coming in?

I am sort of new to working with datasets, adapters, datatables, etc... So I believe this would qualify as a noob question.

I create Dataset in visual studio using the visual designer (the file with the .xsd extention). I there I defined a DataTable that I named "Courses". I populated this table via the designer using a sql server query, so I know I have my data connection correct.

In my code I am testing out how to instatiate that DataTable and then map the columns in it to a local class I call Course.cs. Here is the method:

MyContext ctx = new MyContext();

SqlConnection conn = 
    new SqlConnection("Data Source=****;Initial Catalog=****;User ID=****;Password=****");
SqlCommand command = new SqlCommand(@"select * from stuff", conn);

SqlDataAdapter da = new SqlDataAdapter();
MyDataSet ds = new MyDataSet();
ds.Locale = CultureInfo.InvariantCulture;
da.Fill(ds);
DataTable courses = ds.Courses;

IEnumerable<DataRow> query =
   from course in courses.AsEnumerable()
   select course;
foreach (var course in query)
{
    Course localCourse = new Course();
    localCourse.CourseCode = course.Field<string>("CourseCode");
    ctx.Courses.Add(localCourse);
}
ctx.SaveChanges();

I am not getting any errors, it will run the code fine, but since the 'query' variable does not have anything in it, it is not making the new objects I want and mapping the values.

What am I missing here?

EDIT

Thanks for the quick responses! When I add the line of code:

da.Fill(ds);

it give me this exception:

{"The SelectCommand property has not been initialized before calling 'Fill'."}

Which confuses me because I am not wanting to create a select command, I though that was already in my datatable?

Edit Two

I have done what was suggested, and it makes sense what I need to do, but I am still getting no results (No errors anymore though..). If I run the same query on the same connection string I can get results, any ideas?

Edit Three!

Here is the raw and ugly query that I am using to populate the datatable. Like I said, the results come in fine if I do it in SSMS.

select 
s.AdClassSchedId,
rtrim(s.code) + 
CASE WHEN (isnull(s.section,'') <> '') 
THEN '-S' + s.section 
ELSE '' END as CourseCode,
s.descrip +
CASE WHEN (isnull(s.section,'') <> '') 
THEN ' - Section ' + s.section 
ELSE '' END as CourseName,
s.AdTeacherId, 
s.StartDate,
s.EndDate,
s.DateLstMod as ClassSchedLastModified,
t.AdTermId
from adclasssched s 
inner join adclassschedterm t on s.adclassschedid = t.adclassschedid
where s.active = 1

Upvotes: 1

Views: 2243

Answers (2)

Pranay Rana
Pranay Rana

Reputation: 176886

Way1

If you are using sqlcommand to fill dataset object than you need to open and close connection like this 

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand selectCMD = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
selectCMD.CommandTimeout = 30;

SqlDataAdapter custDA = new SqlDataAdapter();
custDA.SelectCommand = selectCMD;

nwindConn.Open();

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

nwindConn.Close();

Populating a DataSet from a DataAdapter

Way2

Update for you 

// Assumes that connection is a valid SqlConnection object.
string queryString = 
  "SELECT CustomerID, CompanyName FROM dbo.Customers";
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

DataSet customers = new DataSet();
adapter.Fill(customers, "Customers");

Populating a DataSet from a DataAdapter

Before update

Fill your dataset object which not here in you code 

MyDataSet ds = new MyDataSet();
ds.Locale = CultureInfo.InvariantCulture;
//fill datase object ds
//DataAdapter.Fill(ds)
DataTable courses = ds.Courses;

for this error 

{"The SelectCommand property has not been initialized before calling 'Fill'."}

do as below , as error said set select command 

SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand command = new SqlCommand("SELECT * FROM table", connection);
adapter.SelectCommand = command;

Full source : SqlDataAdapter.SelectCommand Property

Upvotes: 2

John Kraft
John Kraft

Reputation: 6840

I don't see you calling fill on the adapter anywhere?

There should be a line of code somewhere in there that looks like...

SqlDataAdapter da = new SqlDataAdapter("select * from courses", mySqlConnection);

da.Fill(ds);

Additionally, you need to set the select command of the data adapter to the sql statement you intend to run from the database.

Upvotes: 4

Related Questions