Template Column, EditCommandColumn issues

[Guest]

Hello,
I am trying to create a page that pulls class rosters from an SQLServer
database. The roster table definition is: emp_id(pk, fk), sec_id(pk, fk),
reg_date and reg_status. Status can be Pending, Cancelled or NoShow. I want
the page to display section information (working fine) as well as a datagrid
of all the employees and their status'. Status should be displayed in a
dropdownlist control and is the only control you can edit.

Help:
1) When the datagrid loads, all columns load fine except the dropdownlist
column, which just show the header and blank cells underneath. I obvioussly
need the ddl to show at all times, including before you click edit. Dropdown
code follows:
---
<aspataGrid ID="secRoster" runat="server" AutoGenerateColumns="false"
showHeader="true" CellPadding="5" OnCancelCommand="CancelEdit"
OnEditCommand="EditRecord" OnUpdateCommand="updaterecordedit">
<columns>
<asp:Boundcolumn datafield="emp_id" visible="false"/>
<asp:Boundcolumn datafield="sec_id" visible="false"/>
....more columns working as expected...
<asp:TemplateColumn HeaderText="Status">
<EditItemTemplate>
<aspropDownList ID="ddlregstatus" runat="server">
<asp:ListItem Value="Pending" Text="Pending"/>
<asp:ListItem Value="Cancelled" Text="Cancelled"/>
<asp:ListItem Value="Noshow" Text="No Show"/>
</aspropDownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update"
CancelText="Cancel" EditText="Edit" ItemStyle-Font-Size="smaller"
ItemStyle-Width="10%"/>
</columns>
---

2) When you click Edit the ddl shows with the default value selected. It
should obviously reflect whats in the DB. The following is the load grid
code. I am pretty sure I am missing somthing:
---
Private sub load_grid()
dim strsql as string = "SELECT * FROM roster_info WHERE sec_id = " &
ddlSec.selecteditem.value
Connect()
dim adapter as New SqlDataAdapter(strSQL, objconnection)
dim ds as New DataSet()
adapter.Fill(ds, "Roster")
Disconnect()
secroster.datasource = ds.tables("Roster")
secroster.databind()
end sub
---
3) Finally, at least for now, when I attempt to update a row, I get a
problem that is seemingly unrelated to the ddl. I get this error:
"System.FormatException: Input string was not in a correct format."
The edit column uses the following code:
---
Public Sub EditRecord(ByVal Sender as Object, ByVal E as
DataGridCommandEventArgs)
secroster.edititemIndex = E.Item.ItemIndex
Load_Grid()
end sub

Public Sub CancelEdit (ByVal Sender as Object, ByVal E as
DataGridCommandEventArgs)
secroster.edititemindex = -1
Load_Grid()
End sub

Public Sub UpdateRecordEdit (ByVal Sender as Object, ByVal E as
DataGridCommandEventArgs)
dim empid as int32 = convert.toint32(e.item.cells(0).text) <-----This is
the line causing the error.
dim secid as int32 = convert.toint32(e.item.cells(1).text)
Dim TempList As DropDownList
TempList = E.Item.FindControl("ddlregstatus")
dim regstatus as string = templist.selecteditem.value
secroster.edititemindex = -1
UpdateRoster(empid, secid, regstatus)
End sub

Private Sub UpdateRoster (ByVal empid as integer, byval secid as integer,
byval editstatus as string)
dim strsql as string = "SELECT * FROM roster_info WHERE sec_id = " & secid
Connect()
dim adapter as New SqlDataAdapter(strsql, objconnection)
dim ds as new dataset()
adapter.fill(ds, "Roster")
disconnect()

dim tbl as datatable = ds.tables("Roster")
tbl.primarykey = New DataColumn() _
{ _
tbl.Columns("EmpID"), _
tbl.Columns("SecID") _
}
dim row as datarow = tbl.rows.find(empid)
row.item("reg_status") = editstatus

dim cb as new sqlcommandbuilder(adapter)
Connect()
adapter.update(ds, "Roster")
disconnect()
End sub
---

I hope I gave enough information to properly convey my issue(s), w/o
overwhelming anyone. I really appreciate any help at all. Thanks for reading
my book...

--Ron

 

[Guest]

Ok, I figured out the solution to problems 1 & 2:

Add:

 

[Guest]

Well, I finally figured it out. I used parameters to go into the DB and it
worked.
The following replaced UpdateRoster and UpdateRecordEdit in my original
post. Thanks for all the quick responses!
---
Private Sub UpdateRoster (ByVal source As Object, ByVal e As
DataGridCommandEventArgs)
Dim DDL As DropDownList = CType(e.Item.Cells(5).Controls(1), DropDownList)
Dim NewStat As String = DDL.SelectedValue
dim empid as int32 = Int32.Parse(e.Item.Cells(0).Text)
dim secid as int32 = Int32.Parse(e.Item.Cells(1).Text)
dim strsql as string = "UPDATE rosters SET reg_status = @status " & _
"WHERE emp_id = @empid1 and sec_id = @secid1"
Dim Cmd As New SqlCommand(strsql, objconnection)
Cmd.Parameters.Add(New SqlParameter("@empid1", empid))
Cmd.Parameters.Add(New SqlParameter("@secid1", secid))
Cmd.Parameters.Add(New SqlParameter("@status", newstat))
Connect()
cmd.executenonquery()
disconnect()
secroster.EditItemIndex = -1
Load_Grid()
End sub
---