Hello all - back again!!

I am trying to create a datagrid with a column for editing the data and a column which contains checkboxes. These check boxes are then used to indicate which rows to delete from the datagrid. See code below...

<asp:datagrid runat="server" id="dgEmps" Height="190px" ... and so on>
 <COLUMNS>
  <ASP:TEMPLATECOLUMN HeaderStyle-HorizontalAlign="Center" FooterStyle-verticalAlign="middle" FooterStyle- HorizontalAlign="Center" HeaderText="Delete">
     <ITEMTEMPLATE>
       <ASP:CHECKBOX id="chkdel" Runat="server" />
     </ITEMTEMPLATE>
  </ASP:TEMPLATECOLUMN>
  <ASP:EDITCOMMANDCOLUMN ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Edit" />
  <ASP:BOUNDCOLUMN HeaderText="Employee ID" ReadOnly="true" ItemStyle-HorizontalAlign="Center" DataField="EMP_EMPLOYEE_ID" />
  <ASP:BOUNDCOLUMN HeaderText="Employee First Name" ItemStyle-HorizontalAlign="Center" DataField="EMP_FIRST_NM" />
  <ASP:BOUNDCOLUMN HeaderText="Employee Second Name" ItemStyle-HorizontalAlign="Center" DataField="EMP_LAST_NM" />
 </COLUMNS>
</asp:datagrid>

On the code behind page, when I am trying to identify which records I want to delete, I can not get the datakey value out of the datagrid for the row I want to delete.

Here is the code I am trying to use:

Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        'this function deletes employees from the database

        Dim chkSelected As System.Web.UI.WebControls.CheckBox
        Dim chkDel As DataGridItem
        Dim del_empID As String

        For Each chkDel In dgEmps.Items

            chkSelected = chkDel.FindControl("chkdel")

            If chkSelected.Checked Then

                'trying to get the primary key of the row to delete

                del_empID =

                Dim strSQL As String = "DELETE EMPLOYEE.* FROM(EMPLOYEE)WHERE EMPLOYEE.EMP_EMPLOYEE_ID=(" & del_empID & ");"

                'setting the connection and query details
                Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "User ID=Admin;" & "Data Source=C:\Inetpub\wwwroot\CompModel\Database\Dev\C ompModel.mdb"

                Dim dbConn As New OleDbConnection(strConn)
                Dim delCmd As OleDbCommand = New OleDbCommand(strSQL, dbConn)
                delCmd.CommandType = CommandType.Text

                dbConn.Open()
                delCmd.ExecuteNonQuery() 'Execute the UPDATE query
                dbConn.Close()

                'save the update to the database
                dgEmps.EditItemIndex = -1
                DbEditResults()

            End If

        Next

    End Sub

on the line: 'del_empID =' is where the problem lies. I have tried various different appraoches for this, but nothing seems to want to work. The idea behind this screen is to allow a user to make multiple selections by checking checkboxes and then initiate the delete by clicking a delete button.

Any body got any ideas? As always, all help greatfully recieved and appreciated,

Many thanks

M.

If the EmpID is the primary key for the table (which it probably is, or else the Delete functionality won't work correctly), you can set the DataKeyField property of the Datagrid to the ID of your table:

<asp:datagrid
  runat="server"
  id="dgEmps"
  Height="190px"
  DataKeyField="EmpID"
>

This makes the DataGrid aware of the primary keys for each item. In the code behind, this allows you to retrieve an EmpID like this:

int empID = Convert.ToInt32(dgEmps.DataKeys[chkDel.ItemIndex].ToString());


IMO, this is much cleaner than parsing the empID from something like:

empID = Convert.ToInt32(chkDel.Item.Cells[2].Text);

where Cells[2] is the third column in your datagrid.

HtH,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Good morning Imar,

thanks for your advice, but the code is giving errors (at least it is moving in the right direction!!)

When I enter either of the lines you suggested, I am getting these errors:

1: int empID = Convert.ToInt32(dgEmps.DataKeys[chkDel.ItemIndex].ToString());
VS is flagging this line with an error description 'identifier expected' in relation to the phrase 'chkDel.ItemIndex'

2: empID = Convert.ToInt32(chkDel.Item.Cells[2].Text);
VS is again flagging error messages up. this time it is 'item is not a member of system.web.ui.webcontrols.datagrid' in relation to the phrase 'chkDel.Item' and at the the phrase '[2]' I am getting the error 'identifier expected'

Any ideas?! Thanks for all of your advice and help,

M.

Can you post the entire code for the Delete method? Maybe you're missing something or maybe I am missing something.. ;)

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Right, never mind. You're using VB.NET, not C#....

Try this:

Dim empID as Integer = Convert.ToInt32(dgEmps.DataKeys(chkDel.ItemIndex). ToString())

or

Dim empID as Integer = Convert.ToInt32(chkDel.Item.Cells(2).Text)

If that doesn't help, please post the code for the Delete method.

Cheers,

Imar




---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Ahhh.... Ladies and Gentlemen, we got him! haha!

The code is now working (sort of, more on that in a moment)! I used the line: empID = Convert.ToInt32(dgEmps.DataKeys(chkDel.ItemIndex). ToString())

The line 'dim empID as integer = Convert.ToInt32(chkDel.Item.Cells(2).Text)' is still giving the error 'item is not a member of system.web.ui.webcontrols.datagrid'. Not that it matters, cos the line is working, but do you have ideas why VS doesnt like this line?! I am curious.

I didnt notice that your previous posts where in C# when I looked at them. You would have thought that the different variable declariations, syntax and the fact that the lines ended with ; would have given it away to me!! Even tho I am new to .net, i really should have noticed that x100

Can I pick your brains for a moment - I am now getting the following errors when I try to delete records and I cant see where they are coming from:

'Value was either too large or too small for an Int32.' I changed the 'toint32' to 'toint16', but this didnt solve the problem, so I altered it again to 'toint64' which seemed to work and it allowed me to delete a record - success! Anyway, when I try to delete more than one record - either by deleting 3 at once or 3 single records in one go, I get this error 'Arithmetic operation resulted in an overflow' I have played around with the code a little, but cant seem to get it to go away. Any ideas?!

Cheers

M

here is the full listing for the sub:

    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        'this function deletes employees from the database

        Dim chkSelected As System.Web.UI.WebControls.CheckBox
        Dim chkDel As DataGridItem

        For Each chkDel In dgEmps.Items
            Dim del_empID As Integer
            Dim strSQL As String = ""

            chkSelected = chkDel.FindControl("chkdel")

            If chkSelected.Checked Then

                'get the primary key of the row to delete
                del_empID = Convert.ToInt64(dgEmps.DataKeys(chkDel.ItemIndex). ToString())

                strSQL = "DELETE EMPLOYEE.* FROM EMPLOYEE WHERE EMPLOYEE.EMP_EMPLOYEE_ID=" & del_empID & ";"
                'setting the connection and query details
                Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "User ID=Admin;" & "Data Source=C:\Inetpub\wwwroot\CompModel\Database\Dev\C ompModel.mdb"

                Dim dbConn As New OleDbConnection(strConn)
                Dim delCmd As OleDbCommand = New OleDbCommand(strSQL, dbConn)
                delCmd.CommandType = CommandType.Text

                dbConn.Open()
                delCmd.ExecuteNonQuery() 'Execute the UPDATE query
                dbConn.Close()

            End If

        Next

        'save the update to the database and show them on the screen
        dgEmps.EditItemIndex = -1
        DbEditResults()

    End Sub

I think the reason why this doesn't work:

Dim empID As Integer = Convert.ToInt32(chkDel.Item.Cells(2).Text)

is my fault. I usually use this code inside a method like UpdateCommand or ItemCreated. With these methods, a variable called e is passed in. e refers to a DataGridItemEventArgs which exposes an Item property which is of type DataGridItem. Inside such a method, e.Item would give you the DataGridItem object. However, your method uses a For Each DataGridItem, so chkDel already is an DataGridItem, so you can't use its Item property. This should work:

Dim empID As Integer = Convert.ToInt32(chkDel.Cells(2).Text)

As for the other problem: what is the DataType for EmpID in the database? It must be pretty large if it doesn't fit in an Int32....


Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

the datatype in the database is set to double. Is that what is likely to be causing the problem?

Cheers

Morris

Well, a Long Integer will do in most cases. Unless you work for a very very large company ;)

AFAIK, a Long Integer in Access will fit into an Int32 in .NET

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

cheers, ill revise the database and see if that solves the problem!

Thanks again for all of your help, it is really appreciated!

Many thanks

Morris