dataset persisting

Why isn't my dataset not being updated with click of the submit button.  The datagrid only shows the last entry, the datagrid does not persist.

   Dim cmdRAP3 As OleDb.OleDbCommand
    Dim strSQL As String
    Public strvar As String
    'Public Shared array As New ArrayList
    Dim sID As String

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not IsPostBack Then
            'cleararray() 'clears the array before loading page

        End If
        'dgprogress.DataSource = array
        lblpcnnumber.Text = Session("PCN").ToString() 'assign the label a pcn from the final.aspx page using session variable
        strvar = lblpcnnumber.Text & " " & Now.Month & Now.Day & Now.Year & ".txt"

       
    End Sub
    Private Sub cleararray()
        'array.Clear()
    End Sub
    Sub deleteclicked(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
        'when a user clicks the delete next to an entry it is deleted from the arraylist and the database

        Dim iid As String = dgprogress.Items(e.Item.ItemIndex).Cells(1).Text()      'Grab the id from the hidden column
        'Dim remove As Integer = Convert.ToInt32(dgprogress.Items(e.Item.ItemIndex))
        'lblerrormessage.Text = remove

        strSQL = "Delete from BILLING_CAL where id = '" & iid & "'" 'removed from the database
        'array.RemoveAt(e.Item.ItemIndex.ToString) 'removed from the arraylist

        dgprogress.DataBind() 'rebind the datagrid to display the new table with the item deleted

        ExecuteDBCommand(strSQL) ' executes the delete statement

    End Sub
    Public Sub CreateDataTableInDataSet(ByVal id As String, ByVal pcn As String, ByVal cal As String, ByVal skill As String, ByVal units As String)

        ' Create the DataTable.
        Dim dt As New DataTable
        Dim ds As New DataSet
        Dim dr As DataRow

        ' Add columns to the table -- one Integer, one String, and one DateTime.
        dt.Columns.Add("ID", System.Type.GetType("System.String"))
        dt.Columns.Add("PCN", System.Type.GetType("System.String"))
        dt.Columns.Add("Date", System.Type.GetType("System.String"))
        dt.Columns.Add("Skill", System.Type.GetType("System.String"))
        dt.Columns.Add("Units", System.Type.GetType("System.String"))

        ' Add two rows of data.
        dr = dt.NewRow()
        dr("ID") = id
        dr("PCN") = pcn
        dr("Date") = cal
        dr("Skill") = skill
        dr("Units") = units
        dt.Rows.Add(dr)

        ' Add the DataTable to a new DataSet.
        If Not IsNothing(Session("dataset_ds")) Then
            ds = Session("dataset_ds")
            ds.Tables.Add(dt)
            Session("dataset_ds") = ds
            lblerror.Text = "I am in true section."
        Else
            ds.Tables.Add(dt)
            Session("dataset_ds") = ds
            lblmessage.Text = "I am in the false section."
        End If

        dgprogress.DataSource = Session("dataset_ds")
        dgprogress.DataBind()

    End Sub
    Private Sub btnsubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsubmit.Click

        sID = lblpcnnumber.Text & Now.Now.ToString 'used to attach to a hidden column in dgprogress

        strSQL = "INSERT INTO BILLING_CAL(id, pcn, [date], skill, unit) VALUES ('" & sID & "', '" & lblpcnnumber.Text & _
        "', '" & cal.SelectedDate & "', '" & ddlskills.SelectedValue & _
        "', '" & txtunits.Text & "')"  'as the user click submit the data is being entered into the database

        CreateDataTableInDataSet(sID, lblpcnnumber.Text, cal.SelectedDate, ddlskills.SelectedValue, txtunits.Text)

        'array.Add(New billcal(sID, lblpcnnumber.Text, cal.SelectedDate, ddlskills.SelectedValue, txtunits.Text))
        'dgprogress.DataBind() 'bind the new data to the datagrid after the user clicks submit

        ExecuteDBCommand(strSQL) 'execute the insert state

        print()
    End Sub
    Private Sub ExecuteDBCommand(ByVal strVal As String)
        'This section of code can execute insert, delete, update statements

        '  lblerrormessage.Text() = cmdRAP3.Connection.State()
        Try
            cmdRAP3 = New OleDb.OleDbCommand(strVal, conRAP3)
            With cmdRAP3
                .Connection = conRAP3
                .CommandType = CommandType.Text
                .CommandText = strVal
                .Connection.Open()
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            lblerrormessage.Text = ex.Message
            With cmdRAP3.Connection
                If .State = ConnectionState.Open Then
                    .Close()
                End If
            End With
        End Try
    End Sub
    Private Sub print()
        'This section of code writes a text file to the server @ the location below for the
        'user to download and print a list of entered items

        Dim oFile As System.IO.File
        Dim oWrite As System.IO.StreamWriter


        'oWrite = oFile.AppendText("C:\Inetpub\wwwroot\WebApplication1\BillingCal\" & Session.SessionID.ToString & ".txt")
        oWrite = oFile.AppendText("C:\Inetpub\wwwroot\WebApplication1\BillingCal\" & lblpcnnumber.Text & " " & Now.Month & Now.Day & Now.Year & ".txt")


        oWrite.WriteLine(lblpcnnumber.Text)
        oWrite.WriteLine(cal.SelectedDate)
        oWrite.WriteLine(ddlskills.SelectedValue)
        oWrite.WriteLine(txtunits.Text)
        oWrite.WriteLine()
        oWrite.Close()
    End Sub
End Class
WolfManBN25Asked:
Who is Participating?
 
DropZoneConnect With a Mentor Commented:
WolfMan, you are right, I'm sorry.  But I see that he you are creating a new DataTable every time with a single record.  This is, in my opinion, the reason why the DataGrid only displays the last record every time.

If the DataSet is defined in the SessionState, you should instead get the DataTable from it and add a row to that one instead.  Try this:

Public Sub CreateDataTableInDataSet(ByVal id As String, ByVal pcn As String, ByVal cal As String, ByVal skill As String, ByVal units As String)

        ' Create the DataTable.
        Dim dt As New DataTable
        Dim ds As New DataSet
        Dim dr As DataRow

        ' Add the DataTable to a new DataSet.
        If Not IsNothing(Session("dataset_ds")) Then
            ds = Session("dataset_ds")
            dt = ds.Tables(0) ' get the DataTable
            Session("dataset_ds") = ds
            lblerror.Text = "I am in true section."
        Else
            ' Add columns to the table -- one Integer, one String, and one DateTime.
            dt.Columns.Add("ID", System.Type.GetType("System.String"))
            dt.Columns.Add("PCN", System.Type.GetType("System.String"))
            dt.Columns.Add("Date", System.Type.GetType("System.String"))
            dt.Columns.Add("Skill", System.Type.GetType("System.String"))
            dt.Columns.Add("Units", System.Type.GetType("System.String"))
            ds.Tables.Add(dt)

            Session("dataset_ds") = ds
            lblmessage.Text = "I am in the false section."
        End If

        ' Add two rows of data.
        dr = dt.NewRow()
        dr("ID") = id
        dr("PCN") = pcn
        dr("Date") = cal
        dr("Skill") = skill
        dr("Units") = units
        dt.Rows.Add(dr)

        dgprogress.DataSource = Session("dataset_ds")
        dgprogress.DataBind()
End Sub
0
 
Bob LearnedCommented:
I don't see how the DataGrid is bound to the data source.

Bob
0
 
DropZoneCommented:
In btnsubmit_Click() method you are calling CreateDataTableInDataSet() method which databinds the grid, but you are not rebiding the grid after the ExecuteDBCommand() call.  This means that the DataGrid will still contain the old data.

You should call dgprogress.DataBind() after ExcecuteDBCommand().  Also, I don't see why you want to databind the grid in CreateDataTableInDataSet().  My guess is that this is unnecessary.

    -dZ.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
WolfManBN25Author Commented:
But the databind and executeDBcommand are completely seperate.  The databind is for the datagrid and the executedbcommand is for the sql statement nothing to do with each other.
0
 
DropZoneCommented:
QUOTE: "Why isn't my dataset not being updated with click of the submit button.  The datagrid only shows the last entry, the datagrid does not persist."

Isn't the ExcecuteDBCommand called when the submit button is clicked (btnsubmit_Click)?  I was under the impression that your problem is that when you click the submit button, the DataGrid is not showing the updated data.  As I see it, when you click the submit button, the database is updated (calling ExecuteDBCommand), but the DataGrid is not re-bound.

    -dZ
0
 
WolfManBN25Author Commented:
Maybe I am wrong, but when the user clicks the submit button the information in the page is inserted into the access database using the ExecuteDBCommand sub, that works fine.  Also when the user clicks submit the sub CreateDataTableInDataSet is called and that is where the databinding happens for the datagrid.
0
 
WolfManBN25Author Commented:
Awesome that worked.  Thanks dz
0
 
DropZoneCommented:
WolfMan, You may want to consider, as I suggested before, to re-bind the DataGrid after updating the database.  This is in case anything wrong happens during the execution of ExcecuteDBCommand().  In your case, if an error occurs and the database is not updated, the DataGrid is still bound to the new version of the DataSet, and the user may not be made aware of the problem.

    -dZ.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.