Further questions regarding looping through a dataset table!!

[mike55]

Hi

 

My SQL code does the following select

 MyCommand.CommandType = CommandType.Text
                   MyCommand.CommandText = "SELECT DISTINCT myMembers.Member_ID, myMembers.Surname, myMembers.Forename, " & _
                                                                   "myGroups.Group_Name, myGroups.Group_ID, myGroups.Group_Description " & _
                                                                   "FROM myMembers " & _
                                                                   " CROSS JOIN myGroupMembership " & _
                                                                   "INNER JOIN myGroupMembership myGroupMembership_1 " & _
                                                                   "ON myGroupMembership.Member_ID = myMembers.Member_ID " & _
                                                                   "INNER JOIN myGroups " & _
                                                                   "ON myGroups.Group_ID = myGroupMembership.Group_ID " & _
                                                                   "ORDER BY myMembers.Member_ID"
                   daDataAdapter.SelectCommand = MyCommand
                   daDataAdapter.Fill(dsDataset)
                   daDataAdapter.Dispose()

               ' 3. Retrieve the data and place it in a datagrid.
                   GridEX1.DataSource = dsDataset.Tables(0)
                   GridEX1.DataBind()
                   dsDataset.Tables(0).TableName = "DataMembers"

 

As you can see I am then renaming the dataset table to "DataMembers"

 

Now further one in my code, I want to loop through the dataset table "DataMembers" one row at a time. The code that I am using is:

  For Each dr As DataRow In dsDataset.Tables("DataMembers").Rows
            
  Next

 

The problem that I am getting is: Object reference not set to an instance of an object.

This errors is thrown for the first line of the for loop.

 

Any suggestions??

 

Mike55

[Afraits]

I'm not sure why you are renaming the table but there is an overload method for filling the dataset which allows you to name the table as you fill it.

 

daDataAdapter.Fill(dsDataset,"DataMembers")

 

It may be due to the table being renamed that causes the problem but I'm not sure on that

[mike55]

Thanks for the suggestion regarding the overload method, will use that instead of what I am currently doing. I have stepped through my code and have put a watch on my dataset. From what I can figure out, it would seem that my Dataset is loosing its data. Don't know how, as I don't think I told the dataset to empty itself. Will keep looking.

 

Mike55

[SonicBoomAu]

Where have you declared "dsDataSet"?

If it is in the loading of the datagrid and the lopping is another procedure it won't be able to see the value.

 

Try declaring "dsDataSet" either in a module or at the top of your forms code.

 

Hope this helps.

[mike55]

Found out what was going wrong. The dataset was being wipped clean by a post-back. Solved the problem by inserting the data into a Session variable and retrieving it when I needed the data. When I was finished with the data, I simple updated the Session variable.

 

Mike55