SQL Insert for Dynamic GridView

[JohnShell]

Hi,

No idea how to do this. Preceding programmer did VB.NET dynamic grid. Columns/rows generated based on user selection of check boxes. Code generates text boxes for grid for user entered values. I need to insert the user entered values into SQL Srvr 2K5 table - don't know how. I'm guessing a For Loop. Any guidance/suggestions appreciated. Please see some of the code below:

Private Sub BindGridView()
'Dim da As New DataLayer.SqlDataAccess
'Dim sql As String = "Select * from categories"
Dim dt As New DataTable '= da.GetDataTable(sql)
dt.Columns.Add(New DataColumn("Branch"))
dt.Columns.Add(New DataColumn("Team"))
dt.Columns.Add(New DataColumn("Reg Hrs Allocated"))
dt.Columns.Add(New DataColumn("OT Hrs Allocated"))
Dim PlaceHolder As DataRow = dt.NewRow
dt.Rows.Add(PlaceHolder)

Dim RS As SqlDataReader = ClsData.getRecordSet("Select Distinct ISNULL([Team], 'N/A') As Team, [Branch_Initials] As Branch From dbo.Employees Order By Branch_Initials", ClsData.ConnectionString)
If RS.HasRows Then
While RS.Read
System.Diagnostics.Debug.Print(RS("Branch") & ("Team"))
Dim rw As DataRow = dt.NewRow
rw.Item("Branch") = RS("Branch")
rw.Item("Team") = RS("Team")
dt.Rows.Add(rw)
End While
End If
grdAsgnTm.DataSource = dt
grdAsgnTm.DataBind()
For i As Integer = 1 To grdAsgnTm.Rows.Count - 1
grdAsgnTm.Rows.Item(i).Visible = False
grdAsgnTm.Rows.Item(i).Cells(2).Controls.Add(New TextBox)
grdAsgnTm.Rows.Item(i).Cells(3).Controls.Add(New TextBox)
DirectCast(grdAsgnTm.Rows.Item(i).Cells(2).Controls(0), TextBox).Text = 0
DirectCast(grdAsgnTm.Rows.Item(i).Cells(3).Controls(0), TextBox).Text = 0
Next

End Sub
Private Function Verify() As Boolean
Dim intReg As Integer = 0
Dim intOT As Integer = 0
Dim BlnPass As Boolean = True
Dim BranchRegTotals As New Dictionary(Of String, Integer)
Dim BranchOTTotals As New Dictionary(Of String, Integer)



Dim totalRegHrs As New Dictionary(Of String, Integer)
Dim totalOTHrs As New Dictionary(Of String, Integer)
For i As Integer = 2 To grdAsgnTm.Rows.Count - 1
If grdAsgnTm.Rows.Item(i).Visible = False Then
If Val(DirectCast(grdAsgnTm.Rows.Item(i).Cells(2).Controls(0), TextBox).Text) > 0 Or Val(DirectCast(grdAsgnTm.Rows.Item(i).Cells(3).Controls(0), TextBox).Text) > 0 Then
If totalRegHrs.ContainsKey(grdAsgnTm.Rows.Item(i).Cells(0).Text) Then
totalRegHrs(grdAsgnTm.Rows.Item(i).Cells(0).Text) += Val(DirectCast(grdAsgnTm.Rows.Item(i).Cells(2).Controls(0), TextBox).Text)
Else
totalRegHrs.Add(grdAsgnTm.Rows.Item(i).Cells(0).Text, Val(DirectCast(grdAsgnTm.Rows.Item(i).Cells(2).Controls(0), TextBox).Text))
End If
If totalOTHrs.ContainsKey(grdAsgnTm.Rows.Item(i).Cells(0).Text) Then
totalOTHrs(grdAsgnTm.Rows.Item(i).Cells(0).Text) += Val(DirectCast(grdAsgnTm.Rows.Item(i).Cells(3).Controls(0), TextBox).Text)
Else
totalOTHrs.Add(grdAsgnTm.Rows.Item(i).Cells(0).Text, Val(DirectCast(grdAsgnTm.Rows.Item(i).Cells(3).Controls(0), TextBox).Text))
End If
End If
End If
Next
For Each KVP As KeyValuePair(Of String, Integer) In totalRegHrs
If BranchRegTotals.ContainsKey(KVP.Key) Then
If Not BranchRegTotals(KVP.Key) >= KVP.Value Then
lblBranchRegTotals.Text = "* Exceeds Branch Reg Hrs total: Allocated= " & BranchRegTotals(KVP.Key) & " hrs" & ", " & "Input= " & KVP.Value & " hrs"
lblBranchRegTotals.Visible = True
BlnPass = False
Else
lblBranchRegTotals.Visible = False
End If
End If
Next
For Each KVP As KeyValuePair(Of String, Integer) In totalOTHrs
If BranchOTTotals.ContainsKey(KVP.Key) Then
If Not BranchOTTotals(KVP.Key) >= KVP.Value Then
lblBranchOTTotals.Text = "* Exceeds Branch OT Hrs total: Allocated= " & BranchOTTotals(KVP.Key) & " hrs" & ", " & "Input= " & KVP.Value & " hrs"
lblBranchOTTotals.Visible = True
BlnPass = False
Else
lblBranchOTTotals.Visible = False
End If
End If
Next
Return BlnPass
End Function


Dim grdTmData As String = ""
For i As Integer = 2 To grdAsgnTm.Rows.Count - 1
'If grdAsgnTm.Rows.Item(i).Visible = False Then
If Val(DirectCast(grdAsgnTm.Rows.Item(i).Cells(2).Controls(0), TextBox).Text) > 0 Or Val(DirectCast(grdAsgnTm.Rows.Item(i).Cells(3).Controls(0), TextBox).Text) > 0 Then
grdTmData += grdAsgnTm.Rows.Item(i).Cells(0).Text & ","
grdTmData += grdAsgnTm.Rows.Item(i).Cells(1).Text & ","
grdTmData += DirectCast(grdAsgnTm.Rows.Item(i).Cells(2).Controls(0), TextBox).Text & ","
grdTmData += DirectCast(grdAsgnTm.Rows.Item(i).Cells(3).Controls(0), TextBox).Text & "|"
End If
'End If
Next
Verify()
Session("grdAsgnTmData") = grdTmData
Response.Redirect("ProjectNewAsgnMmbr.aspx")
End Sub

Thanks,
John