I am almost done with the Data Grid.

Ok so now I have a databind sub here is the code, nothing special (an for those who don't like datasets :P )


Sub BindGrid(ByVal strSortField As String) ' strSortField is a string passed to the Sqlcommand to sort the results

        Dim conStaticIP As SqlConnection

        Dim StaticDS As New DataSet()

        conStaticIP = New SqlConnection("Server=localhost;UID=WYD;Database=ITConfig")

        Dim cmdSelect As New SqlDataAdapter("Select [ID],[IpAddress],[IPOwner] from StaticIpAdd Order By " & strSortField, conStaticIP)

        conStaticIP.Open()

        cmdSelect.Fill(StaticDS, "StaticIpAdd")


        Dim sw1 As Integer = 1

        Select Case sw1

            Case 1

                Dim dr As DataRow = StaticDS.Tables(0).NewRow()

                'put something in the first column to look pretty

                dr("IPAddress") = "   Add IP Address"

                StaticDS.Tables(0).Rows.InsertAt(dr, 0)

          End Select



        StaticIPGrid.DataSource = StaticDS.Tables("StaticIpAdd")

        StaticIPGrid.DataBind()

        conStaticIP.Close()

    End Sub

Ok so what that basically does is Gets the results from the DB puts them into a Dataset, then it adds a empty row to the top of the DS with one value.



Then we get to the Update command a litttttle bit more complex.



Update Command where I am not sure, ok so what I am doing (and please correct me here!) I am first returning the values from the db (which I think might be unnesassary requiring of the db) and inserting the values into a DS.



I then need to take into consideration the empty row at the top and check for data in that row, because if the user "added a new record" and not just simply updated a current value I need to show this new record in the db.



Now where do I go from here? I have code but its everywhere,



Basically steps:

Dim cols As String() = {"IPAddress", "IPOwner"}



        Dim numCols As Integer = e.Item.Cells.Count

        Dim currentRow As Integer = e.Item.DataSetIndex

        ' reload Values into the dataset

        Dim conStaticIP As SqlConnection

        Dim DS As New DataSet()

        conStaticIP = New SqlConnection("Server=localhost;UID=WYD;Database=ITConfig")

        Dim cmdSelect As New SqlDataAdapter("Select [ID],[IpAddress],[IPOwner] from StaticIpAdd Order By " & strSortField, conStaticIP)

        conStaticIP.Open()

        cmdSelect.Fill(DS, "StaticIpAdd")

        conStaticIP.Close()

        Dim row As DataRow

        If currentRow = 0 Then

            row = DS.Tables(0).NewRow()

        Else

            row = DS.Tables(0).Rows(e.Item.DataSetIndex - 1) 'need to sub 1 because we have an inserted row

        End If



        ' get the values and update the datarow

        Dim I, J As Integer

        J = -1

        Trace.Warn("aspx.page", "numcols = " & CStr(numCols))

        For I = 2 To numCols - 1 'skip first and 2nd columns (Edit/Delete command column)

            J += 1

            Dim currentTextBox As TextBox

            currentTextBox = CType(e.Item.Cells(I).Controls(0), TextBox)

            row(cols(J)) = currentTextBox.Text

            Trace.Warn(I.ToString(), CStr(cols(J)) & " = " & currentTextBox.Text)

        Next



        If currentRow = 0 Then

            DS.Tables(0).Rows.InsertAt(row, 0)

        End If

Ok now where do I go reload the whole DS into the db (which I think is totally wrong...)



Please rip this apart I need the most efficient and best if possible answer.



The Datagrid column setup is:



All columns are Template columns

And are in this order:



Delete

Edit(update, cancel)

IPAddress

IpOwner



The very first row in the DG is a empty row (RE Databind sub above)