update .csv file from gridview

[henn]

Hi. I am trying to read a .csv-file into a gridview, insert record, update record and delete records. So far I have managed to read the csv file into the gridview but inserting, updating and deleting records is not working for me, and I hope somebody can help. My aspx-file is like this:

    <asp:Panel ID="editPanel" runat="server" CssClass="margin-bottom-40" Visible="false">

        <div id="dvGrid">

            <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                <ContentTemplate>
                   
                    <asp:GridView ID="GridView1" 
                        runat="server"
                        CellPadding="6"
                        AutoGenerateColumns="false" 
                        DataKeyNames="animalID"
                        AlternatingRowStyle-BackColor="#f7f7f7"
                        HeaderStyle-BackColor="#dadada"
                        AllowSorting="true"
                        RowStyle-HorizontalAlign="right"
                        ShowFooter="true"  
                        OnPageIndexChanging="OnPaging" 
                        OnRowEditing="OnRowEditing"
                        OnRowUpdating="OnRowUpdating" 
                        OnRowCancelingEdit="OnRowCancelingEdit"
                        OnRowDeleting="OnRowDeleting"
                        EmptyDataText="No records has been added."
                        PageSize="10"
                        EnableSortingAndPagingCallbacks="true">
                        
                    <Columns>

                        <asp:CommandField ButtonType="Link" ShowEditButton="True" />

                        <asp:TemplateField HeaderText="Cow No." SortExpression="animalID">
                            <ItemTemplate>
                                <asp:Label ID="lblanimalID" runat="server" Text='<%# Eval("animalID")%>'></asp:Label>
                            </ItemTemplate> 
                            <FooterTemplate>
                                <asp:TextBox ID="txtanimalID" MaxLength="12" runat="server" CssClass="input_wide"></asp:TextBox>
                            </FooterTemplate> 
                        </asp:TemplateField> 

                        <asp:TemplateField HeaderText="Gram / day 1" SortExpression="24hoursallowancesup1">
                            <ItemTemplate>
                                <asp:Label ID="lbl24hoursallowancesup1" runat="server" Text='<%# Eval("24hoursallowancesup1")%>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="txt24hoursallowancesup1" runat="server" Text='<%# Eval("24hoursallowancesup1")%>' CssClass="input_narrow"></asp:TextBox>
                            </EditItemTemplate>  
                            <FooterTemplate>
                                <asp:TextBox ID="txt24hoursallowancesup1" runat="server" CssClass="input_narrow"></asp:TextBox>
                            </FooterTemplate> 
                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Gram / day 2" SortExpression="24hoursallowancesup2">
                            <ItemTemplate>
                                <asp:Label ID="lbl24hoursallowancesup2" runat="server" Text='<%# Eval("24hoursallowancesup2")%>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="txt24hoursallowancesup2" runat="server" Text='<%# Eval("24hoursallowancesup2")%>' CssClass="input_narrow"></asp:TextBox>
                            </EditItemTemplate>  
                            <FooterTemplate>
                                <asp:TextBox ID="txt24hoursallowancesup2" runat="server" CssClass="input_narrow"></asp:TextBox>
                            </FooterTemplate> 
                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Rest 1" SortExpression="accumulatedallowancesup1">
                            <ItemTemplate>
                                <asp:Label ID="lblaccumulatedallowancesup1" runat="server" Text='<%# Eval("accumulatedallowancesup1")%>'></asp:Label>
                            </ItemTemplate> 
                        </asp:TemplateField> 

                        <asp:TemplateField HeaderText="Rest 2" SortExpression="accumulatedallowancesup2">
                            <ItemTemplate>
                                <asp:Label ID="lblaccumulatedallowancesup2" runat="server" Text='<%# Eval("accumulatedallowancesup2")%>'></asp:Label>
                            </ItemTemplate> 
                        </asp:TemplateField> 

                        <asp:TemplateField HeaderText="Visits 1" SortExpression="successfullvisits1">
                            <ItemTemplate>
                                <asp:Label ID="lblsuccessfullvisits1" runat="server" Text='<%# Eval("successfullvisits1")%>'></asp:Label>
                            </ItemTemplate> 
                        </asp:TemplateField> 

                        <asp:TemplateField HeaderText="Visits 2" SortExpression="successfullvisits2">
                            <ItemTemplate>
                                <asp:Label ID="lblsuccessfullvisits2" runat="server" Text='<%# Eval("successfullvisits2")%>'></asp:Label>
                            </ItemTemplate> 
                        </asp:TemplateField> 

                        <asp:TemplateField HeaderText="Delete">
                            <ItemTemplate>
                                <asp:LinkButton ID="OnRowDeleting" runat="server" CommandArgument='<%# Eval("animalID")%>' OnClientClick="return confirm('Do you want to delete?')" Text="&#xf00d;" CssClass="fa fa-2x color-red nobtn"></asp:LinkButton>
                            </ItemTemplate>
                            <FooterTemplate>
                                <asp:LinkButton ID="OnRowAdding" runat="server" Text="&#xf0c7;" CssClass="fa fa-2x color-green nobtn align_right"></asp:LinkButton>
                            </FooterTemplate> 

                        </asp:TemplateField> 
                        
                    </Columns> 

                </asp:GridView> 

            </ContentTemplate> 

            <Triggers>
                <asp:AsyncPostBackTrigger ControlID = "GridView1" /> 
            </Triggers> 

        </asp:UpdatePanel> 
    </div>
         
    </asp:Panel>

My code-behind is:

Imports System.Data.OleDb
Imports System.IO
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic.FileIO

Public Class pitstop1
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Page.IsPostBack = False Then
            If Session("MemberAccessLevel") >= 4 Then

                'We get the herd number

                Try
                    Dim Secondaryconnection As String = ConfigurationManager.ConnectionStrings("SecondaryConnection").ToString()
                    Dim sql As String = "SELECT members.herd_number, herd_name FROM members WHERE members.MemberIndex = " & Session("MemberIndex") & ""
                    Dim cn As New OleDbConnection(Secondaryconnection),
                cmd As New OleDbCommand(sql, cn)
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
                    While reader.Read()
                        herd_number.Text = reader("herd_number")
                        herd_name.Text = reader("herd_name")
                        If herd_number.Text > "0" Then
                            menu.Visible = True
                        Else
                            Response.Redirect("/account/login.aspx?status=not_pitstop&rUrl=/")
                        End If
                    End While

                Catch ex As Exception
                    Response.Write(ex.Message)
                End Try

            Else
                Response.Redirect("/account/login.aspx?status=not_logged_in&rUrl=/pitstop_manager.aspx")
            End If
        End If

    End Sub

    Protected Sub editBtn_Click(sender As Object, e As EventArgs) Handles editBtn.Click

        menu.Visible = False
        editPanel.Visible = True

        'If File.Exists("pitstop_herddata/59523B.csv") Then
        If (System.IO.File.Exists(MapPath("pitstop_herddata/59523B.csv"))) Then

            BindData()
            table2()

        Else
            MsgBox("The file is NOT found.")
        End If

    End Sub

    Private Sub BindData()

        Dim csvPath As String = Server.MapPath("~/pitstop_herddata/") + Path.GetFileName("59523B.csv")
        Dim dta As New DataTable()

        dta.Columns.AddRange(New DataColumn(6) {New DataColumn("animalID", GetType(String)), New DataColumn("24hoursallowancesup1", GetType(String)), New DataColumn("24hoursallowancesup2", GetType(String)), New DataColumn("accumulatedallowancesup1", GetType(String)), New DataColumn("accumulatedallowancesup2", GetType(String)), New DataColumn("successfullvisits1", GetType(String)), New DataColumn("successfullvisits2", GetType(String))})

        Dim csvData As String = File.ReadAllText(csvPath)
        For Each row As String In csvData.Split(ControlChars.Lf).Skip(1)
            If Not String.IsNullOrEmpty(row) Then
                dta.Rows.Add()
                Dim i As Integer = 0
                For Each cell As String In row.Split(";"c)
                    dta.Rows(dta.Rows.Count - 1)(i) = cell
                    i += 1
                Next
            End If
        Next

        GridView1.DataSource = dta
        GridView1.DataBind()

    End Sub
    Protected Sub OnRowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)

        GridView1.EditIndex = e.NewEditIndex
        BindData()

    End Sub

    Protected Sub OnRowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)

        GridView1.EditIndex = -1
        BindData()

    End Sub

    Protected Sub OnRowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)

        'create a new stream(file) writer based on supplied path
        Dim fw As New IO.StreamWriter(Server.MapPath("~/pitstop_herddata/59523B.csv"))
        'write a section for columns collection
        'fw.WriteLine("[column]")
        'For Each column, write the column name within a line
        For x As Integer = 0 To GridView1.Columns.Count - 2
            If x > 0 And x < 8 Then
                fw.Write(GridView1.Columns.Item(x).HeaderText + ";")
            End If
        Next
        'write a section for rows collection
        fw.WriteLine()
        'row controller
        For y As Integer = 0 To GridView1.Rows.Count - 1
            If y > 0 Then
                'for each cell, write the cell value followed by a comma
                For x As Integer = 0 To GridView1.Columns.Count - 1
                    If x > 0 And x < 8 Then
                        fw.Write(GridView1.Rows.Item(y).Cells(x).Text + ";")
                    End If
                Next
            End If
            'ends the line
            fw.WriteLine(y)

        Next
        'close the file writer
        fw.Close()
    End Sub

   Protected Sub OnRowDeleting(ByVal sender As Object, ByVal e As EventArgs)

    End Sub


    Protected Sub OnRowAdding(ByVal sender As Object, ByVal e As EventArgs)

    End Sub

End Class

The above code can read and save the column headers into the csv file but not the table data. I have searched for code snippets but none of them are working for me. The .csv file I am using contains the following data:

animalID 24hoursallowancesup1 24hoursallowancesup2 accumulatedallowancesup1 accumulatedallowancesup2 successfullvisits1 successfullvisits2
5952300318 120 100 80 120 10 13
5952301321 100 80 280 60 3 1
5952301387 140 60 90 40 8 4

I hope somebody can help.

/henn

[KGComputers]

Welcome to the forums.

Kindly wrap your code snippets with code tags for readability. Since this is an asp.net issue, I'll inform the mods to transfer this to the appropriate
subforum.

-kgc

[KGComputers]

And as for the issue,

I have a CRUD(Create/Update/Delete) sample project in the asp.net codebank using the GridView control and Bootstrap. Maybe some of the functionalities
there might help you.

ASP-NET-GridView-CRUD-with-Bootstrap

- kgc

[henn]

Thanks for your answer, but the situation is that I need to use a comma separated file - a .csv-file - and not a conventional database table. My problem is neither dealing with the styling. I hope somebody could check my code and suggest how I can make it function.

And as for the issue,

I have a CRUD(Create/Update/Delete) sample project in the asp.net codebank using the GridView control and Bootstrap. Maybe some of the functionalities
there might help you.

ASP-NET-GridView-CRUD-with-Bootstrap

- kgc

[KGComputers]

We do have a windows app C# project before to remove rows and insert new rows in a csv file. No updating of csv values of course. See if I can pull that up from our files. That was like years ago..

Then maybe incorporate that functionality in an asp.net project.

- kgc

[Pradeep1210]

How big is the CSV File? How many records?

[henn]

I feel I am very close to make it function with my code, and the best for me would be if somebody could tell me why I can alone update the headers and not the record values. Sorry, but I do not see how a C# code could help me. There are numerous code examples on the web in VB.NET, also recent, and it seems I managed to make it close to function.

[henn]

As mentioned, the .csv file I am using contains the following data:

animalID 24hoursallowancesup1 24hoursallowancesup2 accumulatedallowancesup1 accumulatedallowancesup2 successfullvisits1 successfullvisits2
5952300318 120 100 80 120 10 13
5952301321 100 80 280 60 3 1
5952301387 140 60 90 40 8 4

Unfortunately, this forum doesn't allow me to upload the file, which is only a test file and in reality there would be up to several hundred records.

[KGComputers]

I feel I am very close to make it function with my code

How about posting your updated codes. Maybe others could take a look at it.

- kgc

[henn]

The codes are above - tell me are you joking???? Could you delete my account and the thread. This seems to turn into being a waste of my time.

[Pradeep1210]

While reading/writing data from flat file is a bit cumbersome as compared to any database, it is not as hard as it might seem like.

Modify your code like this.

1. In your aspx file add a column to the grid to hold a unique record number. We will use this Record Number later to identify the line in the file that we want to update. Ensure that the column is not visible. (I prefer keeping such columns as first or last column in the grid).

<asp:TemplateField HeaderText="RecordID" visible="false">
    <ItemTemplate>
        <asp:Label ID="lblRecordID" runat="server" Text='<%# Eval("RecordID")%>'></asp:Label>
    </ItemTemplate>
</asp:TemplateField>

2. Change your BindData method and add a column named "RecordID" in the datatable you are binding to the grid. So ultimately your BindData method would look like this:

Private Sub BindData()
    Dim csvPath As String = Server.MapPath("~/pitstop_herddata/") + Path.GetFileName("59523B.csv")
    Dim dta As New DataTable()
 
    dta.Columns.AddRange(New DataColumn() { _
                                            New DataColumn("RecordID", GetType(Integer)), _
                                            New DataColumn("animalID", GetType(String)), _
                                            New DataColumn("24hoursallowancesup1", GetType(String)), _
                                            New DataColumn("24hoursallowancesup2", GetType(String)), _
                                            New DataColumn("accumulatedallowancesup1", GetType(String)), _
                                            New DataColumn("accumulatedallowancesup2", GetType(String)), _
                                            New DataColumn("successfullvisits1", GetType(String)), _
                                            New DataColumn("successfullvisits2", GetType(String))})
 
    Dim csvData() As String = File.ReadAllLines(csvPath)
    For rowNum As Integer = 1 To csvData.Length - 1
        If Not String.IsNullOrWhiteSpace(csvData(rowNum)) Then
            Dim rowData() As String = Split(csvData(rowNum), ";")
            dta.Rows.Add(rowNum, rowData(0), rowData(1), rowData(2), rowData(3), rowData(4), rowData(5), rowData(6))
        End If
    Next
 
    GridView1.DataSource = dta
    GridView1.DataBind()
End Sub

3. Finally your OnRowUpdating method goes like this. (Read inline for details)

Protected Sub OnRowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    '-- read the data from file
    Dim csvPath As String = Server.MapPath("~/pitstop_herddata/") + Path.GetFileName("59523B.csv")
    Dim csvData() As String = File.ReadAllLines(csvPath)
 
    '-- get the record number we are updating and prefill the exiting values in an array
    Dim recordId As Integer = DirectCast(GridView1.Rows(e.RowIndex).FindControl("lblRecordID"), Label).Text
    Dim newRowData() As String = Split(csvData(recordId), ";")
 
    '-- fill updated values from Grid
    'newRowData(0) = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtanimalID"), TextBox).Text
    newRowData(1) = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txt24hoursallowancesup1"), TextBox).Text
    newRowData(2) = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txt24hoursallowancesup2"), TextBox).Text
 
    '-- write back the updated data to file
    csvData(recordId) = Join(newRowData, ";")
    IO.File.WriteAllLines(csvPath, csvData)
 
    '-- refresh the grid
    GridView1.EditIndex = -1
    BindData()
End Sub

The above should work. However, the code is not optimized and I would leave that part to you.

[henn]

Thanks a lot Pradeep1210 - it is working now .