[RESOLVED] Insert all items from gridview

[Motorui]

Hello!

I´m trying to create a add all button to execute a query and insert all rows from a gridview.

Can't make it work, it only inserts one row.
Help please.

Imports System.Data.SqlClient
 
Public Class page1
    Inherits System.Web.UI.Page
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
    End Sub
 
    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
 
        For Each row As GridViewRow In GridView1.Rows
 
            Dim rb As RadioButtonList = DirectCast(row.FindControl("RblRespostas"), RadioButtonList)
            Dim resposta As String
            resposta = rb.SelectedValue
 
            Dim cs = ConfigurationManager.ConnectionStrings("DbqualityCs").ConnectionString
            Dim conn As New SqlConnection(cs)
            Dim cmd As New SqlCommand()
            cmd.Connection = conn
            cmd.CommandText = "INSERT INTO respostas (id_pergunta, id_operador, id_uh, id_chklst," & _
            " resposta, id_auditor, data, criador) VALUES " & _
            "(@id_pergunta, @id_operador, @id_uh, @id_chklst, @resposta, @id_auditor, @data, @criador)"
 
            cmd.Parameters.Add("@id_pergunta", SqlDbType.Int).Value = "1"
            cmd.Parameters.Add("@id_operador", SqlDbType.Int).Value = "1"
            cmd.Parameters.Add("@id_uh", SqlDbType.Int).Value = "1"
            cmd.Parameters.Add("@id_chklst", SqlDbType.Int).Value = "1"
            cmd.Parameters.Add("@resposta", SqlDbType.Int).Value = resposta
            cmd.Parameters.Add("@id_auditor", SqlDbType.Int).Value = "1"
            cmd.Parameters.Add("@data", SqlDbType.DateTime).Value = "1-1-2011"
            cmd.Parameters.Add("@criador", SqlDbType.NVarChar, 50).Value = "rasantos"
 
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            cmd.Connection.Close()
        Next
 
    End Sub
End Class

<%@ Page Title="" Language="vb" AutoEventWireup="false" MasterPageFile="~/Site.Master" CodeBehind="page1.aspx.vb" Inherits="pwquality.page1" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">

     <link type="text/css" href="css/ui-lightness/jquery-ui-1.8.14.custom.css" rel="stylesheet"/>
     <script src="scripts/jquery-1.5.1.min.js" type="text/javascript"></script>
     <script src="scripts/jquery-ui-1.8.14.custom.min.js" type="text/javascript"></script>
     <script type="text/javascript">
        $(function () {
            $("#TxtData").datepicker({ dateFormat: 'dd-mm-yy', gotoCurrent: true, showAnim: 'explode' });
        });
	
	 </script>

</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <asp:SqlDataSource ID="SqlDsUh" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DbqualityCs %>" 
        SelectCommand="SELECT * FROM [uhandling]"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDsOp" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DbqualityCs %>" 
        SelectCommand="SELECT * FROM [operadores]"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDsAud" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DbqualityCs %>" 
        SelectCommand="SELECT * FROM [auditores]"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DbqualityCs %>" 
        SelectCommand="SELECT id, pergunta, question, id_chklst FROM perguntas
         WHERE (id_chklst = @id_chklst)" >

        <SelectParameters>
            <asp:QueryStringParameter Name="id_chklst" QueryStringField="ID" />
        </SelectParameters>
    </asp:SqlDataSource>

    <asp:table runat ="server" CellSpacing ="1" GridLines ="Both" >
    <asp:TableRow>
    <asp:TableHeaderCell>Unidade de Handling</asp:TableHeaderCell>
    <asp:TableHeaderCell>Operador</asp:TableHeaderCell>
    <asp:TableHeaderCell>Auditor</asp:TableHeaderCell>
    <asp:TableHeaderCell>Data</asp:TableHeaderCell>
    </asp:TableRow>
    <asp:TableRow>
    <asp:TableCell>
        <asp:DropDownList ID="DdlUh" runat="server" DataSourceID="SqlDsUh"
    appenddatabounditems="true" DataTextField="nome" DataValueField ="id">
        </asp:DropDownList>
    </asp:TableCell>
    <asp:TableCell>
        <asp:DropDownList ID="DdlOp" runat ="server" DataSourceID="SqlDsOp"
         appenddatabounditems="true" DataTextField ="nome" DataValueField="id">
         </asp:DropDownList>
    </asp:TableCell>
    <asp:TableCell >
        <asp:dropdownlist ID="DdlAud" runat="server"  DataSourceID ="SqlDsAud"
        appenddatabounditems="true"  DataTextField ="nome" DataValueField ="id">
        </asp:dropdownlist>
    </asp:TableCell>
    <asp:TableCell>
<input type="text" id="TxtData"/>
    </asp:TableCell>
    </asp:TableRow>
        </asp:table>
        <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="id" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="id_chklst" HeaderText="id_chklst" 
                SortExpression="id_chklst" Visible ="false" />
            <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" 
                ReadOnly="True" SortExpression="id" Visible ="false" />
            <asp:BoundField DataField="pergunta" HeaderText="pergunta" 
                SortExpression="pergunta" />
            <asp:BoundField DataField="question" HeaderText="question" 
                SortExpression="question" />
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:RadioButtonList ID="RblRespostas" runat="server">
                        <asp:ListItem Value="1">Sim</asp:ListItem>
                        <asp:ListItem Value="2">Não</asp:ListItem>
                        <asp:ListItem Value="3">N/A</asp:ListItem>
                    </asp:RadioButtonList>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <asp:Button ID="Button1" runat="server" Text="Salvar" />
</asp:Content>

[danasegarane]

Did you debug the code, Did the insert run for all the rows ?

[SeanGrebey]

Isn't Parameter.Add no longer recommended?

I usually use Parameter.AddWithValue("@MyParam","My Value")

Then you don't have to worry about type so much.

--------------------------------------------------------------

Now that aside, why are you inserting the same value in all datafields but one. I would assume your issue is you have 5 ID fields and you are inserting 1 into all of them. I'm assuming one is a unique field and you are throwing an exception after the first insert. Put a try...catch block around your cmd.ExecuteNonQuery() and put a break point on the catch and you're probably getting some kind of duplicate ID error from SQL.

[Motorui]

Isn't Parameter.Add no longer recommended?

I usually use Parameter.AddWithValue("@MyParam","My Value")

Then you don't have to worry about type so much.

--------------------------------------------------------------

Now that aside, why are you inserting the same value in all datafields but one. I would assume your issue is you have 5 ID fields and you are inserting 1 into all of them. I'm assuming one is a unique field and you are throwing an exception after the first insert. Put a try...catch block around your cmd.ExecuteNonQuery() and put a break point on the catch and you're probably getting some kind of duplicate ID error from SQL.

It is working now with:

cmd.Parameter.AddWithValue("@MyParam","My Value")

about the same value im still working on it!
I have a jquery datetimepicker that i need to pass the selected date to a string
and some other fields from the gridview that are hidden

hope i wont get problems with them.

Thanks again