Update All Values In Gridview Column

[Motorui]

hi all!!

what i need to do is to fill a gridview With a query and just let one column
to edit and when i click a button update all values in that column to the database...
ex:

table Answers:
ID(Int)
Question(Nvarchar(50)
Answer(Bit)
fill the gridview

Select Question , Answer FROM Answers

Ok now i need to edit all rows in the column Answer and if possible with enum

answer = 0 Enum = Yes
answer = 1 Enum = No
answer = DbNull Enum = Not applicable or N/A

anny ideas in how to do this??
instead row to row?

Thanks in advance

[mendhak]

http://msdn2.microsoft.com/en-us/library/ms972948.aspx

[Motorui]

thanks mendhak! saw the examples, but it still edit row by row!
i need to change all values in the gridview column and then click update button
to update all at once!

[mendhak]

So what you're looking for is Edit mode. You start off by creating an item template. The ItemTemplate obviously corresponds to a column, you already know that. You can however, create an EditItemTemplate. Just like the ItemTemplate, but it's a template meant exclusively for editing.

You will then have a button external to the grid. User clicks it, set a class-level boolean to true (isInEditMode = true), and you should then handle the OnRowDataBound event. In there, I believe you can set the .Rowstate property of each row to Rowstate.Edit (something similar, intellisense will tell you).

[Motorui]

something like this?::

<ItemTemplate>
                    <asp:DropDownList ID="DropDownList1" runat="server">
                        <asp:ListItem Value="TRUE">YES</asp:ListItem>
                        <asp:ListItem Value="FALSE">NO</asp:ListItem>
                        <asp:ListItem Value="NULL">N/A</asp:ListItem>
                    </asp:DropDownList>

[Motorui]

ok got it working!!!
but not in the update button
tis is what i got so far:

       Dim conn As New SqlConnection
        Dim dgItem As GridViewRow

        For Each dgItem In GvLCRespostas.Rows
            Dim strQuery As String = "UPDATE LCRespostas SET Resposta = @Res"
            conn.ConnectionString = "Data Source=PORTLIS-RAS\SQLEXPRESS;Initial " & _
                        "Catalog=GesQual;Persist Security Info=True;User ID=Cleverops;Password=admin"
            conn.Open()

            Dim Command As New SqlCommand(strQuery, conn)
dim str as string = "TRUE"
            Command.Parameters.AddWithValue("@Res", str)
            Command.ExecuteNonQuery()
            strQuery = String.Empty
            Command.Dispose()
        Next

        conn.Close()

how to retrieve the value from my dropdownlist??

[mendhak]

But the link provided gives a full example... using a datasource, creating the update methods and using the <EditItemTemplate>.

Was it not useful?

[Motorui]

yes mendhak i read the link, it helps but it only allows to edit one row at a time!
what i need is to edit and update all rows at the same time!
thanks again for your time!

[Motorui]

tried this but still not working!!

Protected Sub BtnUpdate_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles BtnUpdate.Click

Dim con As New SqlConnection
Dim dgItem As GridViewRow
Dim row As GridViewRow = GvLCRespostas.Rows(2)
Dim comd As New SqlCommand

con.ConnectionString = "Data Source=PORTLIS-RAS\SQLEXPRESS;Initial " & _
"Catalog=GesQual;Persist Security Info=True;User ID=Cleverops;Password=admin"
con.Open()

For Each dgItem In GvLCRespostas.Rows

comd.Connection = con

comd.CommandText = "UPDATE LCRespostas SET Resposta = @Res"

Dim list As DropDownList = CType(row.FindControl("DropDownList1"), DropDownList)

If Not list Is Nothing Then

Dim str As String = list.SelectedValue.ToString
comd.Parameters.AddWithValue("@Res", str)
comd.ExecuteScalar()

End If

Next
con.Close()
con.Dispose()
'Response.Redirect("stockInput.aspx")

End Sub

gives the error :

The variable name '@Res' has already been declared. Variable names must be unique within a query batch or stored procedure.

[mendhak]

UPDATE LCRespostas SET Resposta = @Res

You need a WHERE clause in there as well.

[Motorui]

ok Now it is giving an error that i don't know how to solve:

        Dim StrOperador As Integer = DdlOperador.SelectedValue
        Dim StrUh As Integer = DdlUh.SelectedValue
        Dim StrData As Date = TxtData.Text
        Dim StrVoo As String = TxtVoo.Text

        Dim con As New SqlConnection
        Dim dgItem As GridViewRow
        Dim row As GridViewRow = GvLCRespostas.Rows(0)
        Dim comd As New SqlCommand

        con.ConnectionString = "Data Source=PORTLIS-RAS\SQLEXPRESS;Initial " & _
      "Catalog=GesQual;Persist Security Info=True;User ID=Cleverops;Password=admin"
        con.Open()

        For Each dgItem In GvLCRespostas.Rows

            comd.Connection = con

            comd.CommandText = "UPDATE ResLc SET IdRes = @Res " & _
            "FROM ResLc CROSS JOIN LControl WHERE " & _
            "(LControl.IdOp = @Op) AND (LControl.IdUH = @Uh) " & _
            "AND (LControl.Data = @Data) AND (LControl.Voo = @Voo)"

            Dim list As DropDownList = CType(row.FindControl("DropDownList1"), DropDownList)

            If Not list Is Nothing Then

                Dim str As String = list.SelectedValue.ToString
                comd.Parameters.AddWithValue("@Res", str)
                comd.Parameters.AddWithValue("@Op", StrOperador)
                comd.Parameters.AddWithValue("@UH", StrUh)
                comd.Parameters.AddWithValue("@Data", StrData)
                comd.Parameters.AddWithValue("@Voo", StrVoo)


                comd.ExecuteScalar()

            End If

        Next
        con.Close()
        con.Dispose()

The Error:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index



Line 86: Dim con As New SqlConnection
Line 87: Dim dgItem As GridViewRow
Line 88: Dim row As GridViewRow = GvLCRespostas.Rows(0)
Line 89: Dim comd As New SqlCommand
Line 90:

[Motorui]

hi again...
searched trough the net and find examples but in c#
as i am a noob in vb in C# i am even more:

converted the code but I'm not been able to get it to work:

    Private Sub Update()

        Dim sb As New StringBuilder()

        ' build the query
        For Each row As GridViewRow In GridView1.Rows

            sb.Append("UPDATE [ResLc] SET [IdPer] = '")
            sb.Append(TryCast(row.FindControl("DropDownList1"), DropDownList).Text)
            sb.Append("'")
            sb.Append(" WHERE [Id] = '")
            sb.Append(TryCast(row.FindControl("Pergunta"), TextBox).Text)
            sb.Append("'")

        Next

        Dim connectionString As String = (ConfigurationManager.ConnectionStrings _
            ("GesQualConnectionString").ConnectionString)

        Dim myConnection As New SqlConnection(connectionString)

        Dim myCommand As New SqlCommand(sb.ToString(), myConnection)

        myConnection.Open()

        myCommand.ExecuteNonQuery()

        myConnection.Close()

    End Sub

gives the error:

Object reference not set to an instance of an object.

in this line:

sb.Append(TryCast(row.FindControl("Pergunta"), TextBox).Text)

any ideas?

[mendhak]

When you do:

(row.FindControl("Pergunta")

Not every row will have "pergunta" in it. For example, the header and footer. For each row, check its RowType property to see if it is equivalent to DataGridRowType.DataRow

For Each row As GridViewRow In GridView1.Rows
 
            If row.RowType = DataGridRowType.DataRow Then
            sb.Append("UPDATE [ResLc] SET [IdPer] = '")
            sb.Append(TryCast(row.FindControl("DropDownList1"), DropDownList).Text)
            sb.Append("'")
            sb.Append(" WHERE [Id] = '")
            sb.Append(TryCast(row.FindControl("Pergunta"), TextBox).Text)
            sb.Append("'")
            End If
        Next

[Motorui]

nop it isnt working also!

[Motorui]

</asp:GridView>
    <asp:SqlDataSource ID="SqlDsResLc" runat="server"
        ConnectionString="<%$ ConnectionStrings:GesQualConnectionString %>" 
        SelectCommand="SELECT PerLC.Pergunta, Res.Res 
FROM ResLc 
LEFT OUTER JOIN Res ON ResLc.Id = Res.Id 
LEFT OUTER JOIN PerLC ON ResLc.IdPer = PerLC.ID CROSS JOIN LControl 
WHERE (LControl.IdOp = @Op) AND (LControl.IdUH = @Uh) AND (LControl.Data = @Data) AND (LControl.Voo = @Voo)"
        UpdateCommand="UPDATE ResLc SET IdRes = @Param1 WHERE (Id = @Id)" InsertCommand="INSERT INTO ResLc(IdRes, IdLc, IdPer) SELECT @IdRes AS Expr2, PerLC.ID, LControl.Id AS Expr1 FROM PerLC CROSS JOIN LControl WHERE (LControl.IdOp = @Op) AND (LControl.IdUH = @Uh) AND (LControl.Data = @Data) AND (LControl.Voo = @Voo)">
               <UpdateParameters>
                   <asp:Parameter Name="Param1" />
            <asp:Parameter Name="Id" Type="Int32" />
                    </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="IdRes" />
            <asp:Parameter Name="Op" />
            <asp:Parameter Name="Uh" />
            <asp:Parameter Name="Data" />
            <asp:Parameter Name="Voo" />
        </InsertParameters>
        <SelectParameters>
            <asp:ControlParameter ControlID="DdlOperador" Name="Op" PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="DdlUh" Name="Uh" PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="TxtData" Name="Data" PropertyName="Text" />
            <asp:ControlParameter ControlID="TxtVoo" Name="Voo" PropertyName="Text" />
        </SelectParameters>
    </asp:SqlDataSource>

even in the edit template it doesn't update the values!!!


BUMP

[mendhak]

You haven't posted your gridview. Show us.

[Motorui]

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDsResLc">
        <Columns>
            <asp:CommandField ShowEditButton="True" />
            <asp:BoundField DataField="Pergunta" HeaderText="Pergunta" SortExpression="Pergunta" />
            <asp:TemplateField HeaderText="Res" SortExpression="Res">
                <EditItemTemplate>
                    <asp:DropDownList ID="DropDownList1" runat="server">
                        <asp:ListItem Value="1">YES</asp:ListItem>
                        <asp:ListItem Value="2">NO</asp:ListItem>
                        <asp:ListItem Value="3">N/A</asp:ListItem>
                        <asp:ListItem></asp:ListItem>
                    </asp:DropDownList>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("Res") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>