Update specific field of all selected rows

[Andrew]

I have a radgrid on my page and I have it set up so that I allow multiple selections via a checkbox.

I want to be able to get the selected rows checked and update their field(status) to a certain value.

I want to use a command button for this. the user will select the records and click the button which will update "status" to "confirmed".

Is this possible to be done through a stored procedure? If not, just through vb.net

Thanks

[Jayesh Goyani]

Hi,

// method 1
           foreach (GridDataItem item in grdProducts.MasterTableView.Items)
           {
               if (item.Selected)
               {
                   int ID = Convert.ToInt32(item.GetDataKeyValue("ID"));
                   //update your record in DB.
               }
           }
         grdProducts.Rebind();

//method 2
           string strIDs = string.Empty, strComma = string.Empty;
           foreach (GridDataItem item in grdProducts.MasterTableView.Items)
           {
               if (item.Selected)
               {
                   strIDs += strComma + Convert.ToInt32(item.GetDataKeyValue("ID")).ToString();
                   strComma = ",";
               }
           }
           // update your recoed in DB..with csv valus of IDs

           grdProducts.Rebind();

Thanks,
Jayesh Goyani

[Andrew]

Hi thanks for your reply. I have modified my code to

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim FileID As Integer
        For Each item As GridDataItem In RadGrid1.MasterTableView.Items
            If item.Selected Then
                FileID = Convert.ToInt32(item.GetDataKeyValue("ID"))
                Dim conFiles As SqlConnection
                Dim strConnection As String
                Dim cmd As New SqlCommand

                strConnection = ConfigurationManager.ConnectionStrings("FileawaySQLConnectionString").ConnectionString
                conFiles = New SqlConnection(strConnection)
                conFiles.Open()
                cmd.Connection = conFiles
                cmd.CommandText = "UPDATE dbo.Files SET FileStatus = 2 "
                cmd.ExecuteNonQuery()
                conFiles.Close()

            End If
        Next
    End Sub

However, it updates all the records in the table and not just the selected items. Any ideas?

Thanks again

[Jayesh Goyani]

HI,

foreach (GridDataItem item in grdProducts.SelectedItems)

Thanks,
Jayesh Goyani

[Andrew]

If I use:

foreach(GridDataItem item ingrdProducts.SelectedItems)

Then my codebehind is telling me that:

'Name foreach is not declared'
'GridDataItem is a type and cannot be used as an expression'
'Item - Comma, ')', or a valid expression continuation......

So i tried to change your code to:

For Each item As GridDataItem In RadGrid1.SelectedItems

Which was accepted, however still updating all records. I have tried a WHERE clause (FileID = ID) at the end of my UPDATE statement but still not working

[Andrew]

This is my code for the radgrid which may help

<telerik:RadGrid ID="RadGrid1" runat="server"
                DataSourceID="GetFilesAvailableForRequest" GridLines="None" AllowMultiRowSelection="true">
<HeaderContextMenu EnableImageSprites="True" CssClass="GridContextMenu GridContextMenu_Default"></HeaderContextMenu>

<MasterTableView AutoGenerateColumns="False" DataKeyNames="FileID"
                    DataSourceID="GetFilesAvailableForRequest">
<CommandItemSettings ExportToPdfText="Export to Pdf"></CommandItemSettings>

<RowIndicatorColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</RowIndicatorColumn>

<ExpandCollapseColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</ExpandCollapseColumn>
    <Columns>
    <telerik:GridClientSelectColumn UniqueName="ClientSelectColumn" />
        <telerik:GridBoundColumn DataField="BoxNumber" HeaderText="BoxNumber"
            SortExpression="BoxNumber" UniqueName="BoxNumber">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="FileID" DataType="System.Int32"
            HeaderText="FileID" ReadOnly="True" SortExpression="FileID" UniqueName="FileID" Visible="false">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="FileNumber" HeaderText="FileNumber"
            SortExpression="FileNumber" UniqueName="FileNumber">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="DateEntered" DataType="System.DateTime"
            HeaderText="DateEntered" SortExpression="DateEntered" UniqueName="DateEntered" DataFormatString="{0:dd/MM/yyyy}">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="FileStatus" HeaderText="FileStatus"
            SortExpression="FileStatus" UniqueName="FileStatus">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="BusinessID" DataType="System.Int32"
            HeaderText="BusinessID" SortExpression="BusinessID" UniqueName="BusinessID" Visible="false">
        </telerik:GridBoundColumn>
    </Columns>
</MasterTableView>
            <ClientSettings>
                    <Selecting AllowRowSelect="True"  />
                </ClientSettings>
            </telerik:RadGrid>

[Jayesh Goyani]

foreach(GridDataItem item in RadGrid1.SelectedItems)
{
    int FieldID= Convert.ToInt32(item.GetDataKeyValue("FieldID"));
// DB operation
// UPDATE Files SET FileStatus = 2 WHERE FieldID = '"+  FieldID +"'";
}

check with this solution and let me know if any concern

Thanks,
Jayesh Goyani

[Andrew]

Input string was not in a correct format

on this line

UPDATE Files SET FileStatus = 2 WHERE FieldID = '"+  FieldID +"'"

This only occured once I added the where clause. it works with out it

[Jayesh Goyani]

"UPDATE Files
SET FileStatus = 2
WHERE FieldID = '"+  FieldID.ToString() +"'";

Thanks,
Jayesh Goyani

[Andrew]

Ok you code works and updates the selected item, however if I choose multiple items it only updates the first selected item

[Jayesh Goyani]

Hi,

from code part there was not any issue then also you can check the every time FieldID's value are same or different.

in addition, check your DB query is also

let me know if any concern..

Thanks,
Jayesh Goyani

[Andrew]

this is my code that allows me to select rows and update only those rows in the table. however I am getting the error 'index was out of range'

Protected Sub btnRequestFiles_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRequestFiles.Click
        For Each item As GridDataItem In RadGrid1.SelectedItems
            Dim RowID As String = item.GetDataKeyValue("FileID")
            Dim conFiles As SqlConnection
            Dim strConnection As String
            Dim cmd As New SqlCommand
            Dim cmdinsert As SqlCommand
            Dim strInsert As String

            strConnection = ConfigurationManager.ConnectionStrings("FileawaySQLConnectionString").ConnectionString
            conFiles = New SqlConnection(strConnection)
            conFiles.Open()
            cmd.Connection = conFiles
            cmd.CommandText = "UPDATE dbo.Files SET FileStatus = 2 WHERE FileID = '" + RowID.ToString() + "'"
            cmd.ExecuteNonQuery()
            conFiles.Close()
            Me.lblRequestSuccessful.Text = "Files requested successfully"
            RadGrid1.Rebind()

            'conFiles = New SqlConnection(strConnection)
            ' strInsert = "INSERT INTO dbo.FileHistory (FileID, Action) VALUES (@RowID, @Action)"
            ' cmdinsert = New SqlCommand(strInsert, conFiles)
            ' cmdinsert.Parameters.AddWithValue("@RowID", RowID)
            ' cmdinsert.Parameters.AddWithValue("@Action", 2)
            ' conFiles.Open()
            'cmdinsert.ExecuteNonQuery()
            'conFiles.Close()


        Next
    End Sub

The error is happing on this line

Dim RowID As String = item.GetDataKeyValue("FileID")