Read Datatable from Dataset

[EddiRae]

Hello,
I have created multiple tables in a dataset when I load a page. Some of the information for those tables are on a form. After making changes to the data on the form, I want to go back to those datatables to write to the SQL table.

Here is my code that I currently have. It is getting zero rows. I am not sure how to correct.

Public dsAssessment As DataSet
Public cnn As OleDbConnection = New OleDbConnection
Public ds As New dsAssessment


Dim tbl As New DataTable

tbl = New dsAssessment.dtMemberDataDataTable
iCount = tbl.Rows.Count

If I remove the "New" on the tbl assignment, it gives me the error "dtMemberDataDataTable is not a member of 'System.Data.Dataset'"

Any help would be greatly appreciated.

Eddi Rae

[tinstaafl]

Have you tried accessing the table through the tables collection in the dataset without the New modifier?

[EddiRae]

As you can see the comment under the code, I am getting an error.

[tinstaafl]

The tables collection is a separate property. The code you show doesn't try to access it. A table isn't a direct member of the dataset, it is a member of the Tables collection. Something like this might work:

tbl = dsAssessment.Tables("dtMemberDataDataTable")

[EddiRae]

I am getting the following error:

System.NullReferenceException was unhandled by user code
  Message=Object reference not set to an instance of an object.
  Source=App_Web_thxmkxg0
  StackTrace:
       at Assessment.UpdateAssessment() in V:\Tapestry\Applications\CM_Assessments\Adult_GA.aspx.vb:line 724
       at Assessment.cmdPend_Click(Object sender, EventArgs e) in V:\Tapestry\Applications\CM_Assessments\Adult_GA.aspx.vb:line 670
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException: 

[tinstaafl]

I would suggest that the table wasn't added to the dataset properly.

On a side note mentioning that you're using a webform is quite important as there are differences between webforms and winforms.

[EddiRae]

Here is my code that I used to create the datatable.

Partial Class Assessment
    Public cnn As OleDbConnection = New OleDbConnection
    Public ds As New dsAssessment

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load        
        If Not IsPostBack Then
            cnn.ConnectionString = ConfigurationManager.ConnectionStrings("MyCM_Acnn").ConnectionString
                strSQL = "select "
                strSQL += " a.interview_id"
                strSQL += " , a.interview_seq"
                strSQL += " , b.tab_seq "
                strSQL += " , b.question_seq "
                strSQL += " , b.question_id "
                strSQL += " , c.question_type "
                strSQL += " , isnull(b.response , '') as response"
                strSQL += " , isnull(b.text_response , '') as text_response"
                strSQL += " , isnull(b.criteria_interview_seq , '') as criteria_interview_seq"
                strSQL += " , b.criteria_met "
                strSQL += " , isnull(b.criteria_score , 0) as criteria_score"
                strSQL += " from dbo.cm_assessment_member_interview a"
                strSQL += " left join cm_assessment_member_interview_resp b on a.member_id = b.member_id "
                strSQL += " left join cm_assessment_question c on b.question_id = c.question_id "
                strSQL += " and a.interview_seq  = b.interview_seq "
                strSQL += " where a.member_id = '" & Session("strMemberID") & "'"
                strSQL += " and a.interview_id = " & Session("iInterviewID").ToString
                strSQL += " and a.interview_seq = " & Session("iInterviewSeq").ToString
                strSQL += " and b.interview_seq = " & Session("iInterviewSeq").ToString
                strSQL += " order by b.tab_seq, b.question_seq  "

                Try
                    Dim cmd As New OleDbDataAdapter(strSQL, cnn)
                    cmd.Fill(ds, "dtMemberData")
                    cnn.Close()

                    If ds.Tables("dtMemberData").Rows.Count > 0 Then
                        iCount = ds.Tables("dtMemberData").Rows.Count
                        lblInterviewSeq.Text = ds.Tables("dtMemberData").Rows(0).Item("interview_seq")
                        For i As Integer = 0 To iCount - 1
                            sResponse = ds.Tables("dtMemberData").Rows(i).Item("response")
                            sText_Response = ds.Tables("dtMemberData").Rows(i).Item("text_response")
                            iQuestionID = ds.Tables("dtMemberData").Rows(i).Item("question_id")
                            iType = ds.Tables("dtMemberData").Rows(i).Item("question_type")                            
                            ddlID = Nothing
                            tbID = Nothing

                            Select Case iType
                                Case 1
                                    ddlID = "resp_YN_" & iQuestionID.ToString
                                    tbID = "resp_txt_" & iQuestionID.ToString
                                    cDDL = New DropDownList
                                    cDDL = pnlMain.FindControl(ddlID)
                                    cText = New TextBox
                                    cText = pnlMain.FindControl(tbID)

                                    If Len(Trim(sResponse)) Then
                                        cDDL.SelectedValue = sResponse
                                    Else
                                        cDDL.SelectedValue = 0
                                    End If
                                    If Len(Trim(sText_Response)) Then
                                        cText.Text = sText_Response
                                    Else
                                        cText.Text = ""
                                    End If
                                Case 2
                                    ddlID = "resp_YN_" & iQuestionID.ToString
                                    cDDL = New DropDownList
                                    cDDL = pnlMain.FindControl(ddlID)
                                    If Len(Trim(sResponse)) Then
                                        cDDL.SelectedValue = sResponse
                                    Else
                                        cDDL.SelectedValue = 0
                                    End If
                                Case 5
                                    ddlID = "resp_ddl_" & iQuestionID.ToString
                                    cDDL = New DropDownList
                                    cDDL = pnlMain.FindControl(ddlID)
                                    If Len(Trim(sResponse)) Then
                                        cDDL.SelectedValue = sResponse
                                    Else
                                        cDDL.SelectedValue = 0
                                    End If
                                Case 3, 4, 6, 8, 9
                                    tbID = "resp_txt_" & iQuestionID.ToString
                                    cText = New TextBox
                                    cText = pnlMain.FindControl(tbID)
                                    If Len(Trim(sText_Response)) Then
                                        cText.Text = sText_Response
                                    Else
                                        cText.Text = ""
                                    End If
                            End Select
                        Next i
                    Else
                        lblMsg.Text = "There aren't any records for this Assessment"
                        pnlMsg.Visible = True
                        Exit Sub
                    End If

                Catch ex As Exception
                    If IsNothing(ex.InnerException) Then
                        lblMsg.Text = "Error in Page_Load:  Fill dtMemberData  - Message:  " & ex.Message
                    Else
                        lblMsg.Text = "Error in Page_Load:  Fill dtMemberData - InnerException:  " & ex.InnerException.ToString
                    End If
                    pnlMsg.Visible = True
                    Exit Sub
                End Try
    End Sub             

End Class

I use the datatable to load the form. Could it be that I have the dataset being defined in the class and not in the sub?

[tinstaafl]

Comparing this code to your original code it looks like you're not calling the table by the right name. It's being added as "dtMemberData", but your original code says "dtMemberDataDataTable". If you're only using 1 table you can reference it by index number instead of name, ds.Tables(0). Also I'm pretty sure you have to add the empty table to the dataset before you can fill it.

[EddiRae]

Sorry for the confustion.

I placed the wrong table name into the first code. It actually is dtMemberData. I have several tables in this dataset that I create in the OnLoad.