none
update gridview rows bound by sqldatasource to database does not update database after importing tab file RRS feed

  • Question

  • <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" /> <asp:TemplateField HeaderText="DospelyDieta" SortExpression="DospelyDieta"> <EditItemTemplate> <asp:DropDownList ID="DropDownList7" runat="server" SelectedValue='<%# Bind("DospelyDieta") %>'> <asp:ListItem></asp:ListItem> <asp:ListItem>Dospelý</asp:ListItem> <asp:ListItem>Dieťa</asp:ListItem> </asp:DropDownList> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label6" runat="server" Text='<%# Bind("DospelyDieta") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Export" InsertVisible="False"> <EditItemTemplate> <asp:CheckBox ID="CheckBox1" runat="server" /> </EditItemTemplate> <ItemTemplate> <asp:CheckBox ID="CheckBox1" runat="server" /> </ItemTemplate> </asp:TemplateField> <asp:BoundField DataField="Meno" HeaderText="Meno" SortExpression="Meno" /> <asp:BoundField DataField="Priezvysko" HeaderText="Priezvysko" SortExpression="Priezvysko" /> <asp:BoundField DataField="DátumNarodenia" HeaderText="DátumNarodenia" SortExpression="DátumNarodenia" /> // more fields continues

    </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [knihaHosti]" UpdateCommand="UPDATE [knihaHosti] SET [Meno] = @Meno, [Priezvysko] = @Priezvysko, [DátumNarodenia] = @DátumNarodenia, [StatnaPrislusnost] = @StatnaPrislusnost, [ŠtátNarodenia] = @ŠtátNarodenia, [MiestoNarodenia] = @MiestoNarodenia, [čísloDokladu] = @čísloDokladu, [MiestoPobytu] = @MiestoPobytu, [ŠtátPobytu] = @ŠtátPobytu, [ČísloVíza] = @ČísloVíza, [MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte] = @MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte, [TypVízaDokladu] = @TypVízaDokladu, [ZačiatokPobytu] = @ZačiatokPobytu, [KoniecPobytu] = @KoniecPobytu, [ÚčelCesty] = @ÚčelCesty, [ÚčelCestyVoľnýmTextom] = @ÚčelCestyVoľnýmTextom, [PočetPrenocovaní] = @PočetPrenocovaní, [DospelyDieta] = @DospelyDieta, [extIdentifikatorCudzinca] = @extIdentifikatorCudzinca WHERE [Id] = @original_Id AND (([Meno] = @original_Meno) OR ([Meno] IS NULL AND @original_Meno IS NULL)) AND (([Priezvysko] = @original_Priezvysko) OR ([Priezvysko] IS NULL AND @original_Priezvysko IS NULL)) AND (([DátumNarodenia] = @original_DátumNarodenia) OR ([DátumNarodenia] IS NULL AND @original_DátumNarodenia IS NULL)) AND (([StatnaPrislusnost] = @original_StatnaPrislusnost) OR ([StatnaPrislusnost] IS NULL AND @original_StatnaPrislusnost IS NULL)) AND (([ŠtátNarodenia] = @original_ŠtátNarodenia) OR ([ŠtátNarodenia] IS NULL AND @original_ŠtátNarodenia IS NULL)) AND (([MiestoNarodenia] = @original_MiestoNarodenia) OR ([MiestoNarodenia] IS NULL AND @original_MiestoNarodenia IS NULL)) AND (([čísloDokladu] = @original_čísloDokladu) OR ([čísloDokladu] IS NULL AND @original_čísloDokladu IS NULL)) AND (([MiestoPobytu] = @original_MiestoPobytu) OR ([MiestoPobytu] IS NULL AND @original_MiestoPobytu IS NULL)) AND (([ŠtátPobytu] = @original_ŠtátPobytu) OR ([ŠtátPobytu] IS NULL AND @original_ŠtátPobytu IS NULL)) AND (([ČísloVíza] = @original_ČísloVíza) OR ([ČísloVíza] IS NULL AND @original_ČísloVíza IS NULL)) AND (([MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte] = @original_MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte) OR ([MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte] IS NULL AND @original_MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte IS NULL)) AND (([TypVízaDokladu] = @original_TypVízaDokladu) OR ([TypVízaDokladu] IS NULL AND @original_TypVízaDokladu IS NULL)) AND (([ZačiatokPobytu] = @original_ZačiatokPobytu) OR ([ZačiatokPobytu] IS NULL AND @original_ZačiatokPobytu IS NULL)) AND (([KoniecPobytu] = @original_KoniecPobytu) OR ([KoniecPobytu] IS NULL AND @original_KoniecPobytu IS NULL)) AND (([ÚčelCesty] = @original_ÚčelCesty) OR ([ÚčelCesty] IS NULL AND @original_ÚčelCesty IS NULL)) AND (([ÚčelCestyVoľnýmTextom] = @original_ÚčelCestyVoľnýmTextom) OR ([ÚčelCestyVoľnýmTextom] IS NULL AND @original_ÚčelCestyVoľnýmTextom IS NULL)) AND (([PočetPrenocovaní] = @original_PočetPrenocovaní) OR ([PočetPrenocovaní] IS NULL AND @original_PočetPrenocovaní IS NULL)) AND (([DospelyDieta] = @original_DospelyDieta) OR ([DospelyDieta] IS NULL AND @original_DospelyDieta IS NULL)) AND (([extIdentifikatorCudzinca] = @original_extIdentifikatorCudzinca) OR ([extIdentifikatorCudzinca] IS NULL AND @original_extIdentifikatorCudzinca IS NULL))"> <UpdateParameters> <asp:Parameter Name="Meno" Type="String" /> <asp:Parameter Name="Priezvysko" Type="String" /> <asp:Parameter Name="DátumNarodenia" Type="String" /> <asp:Parameter Name="StatnaPrislusnost" Type="String" /> <asp:Parameter Name="ŠtátNarodenia" Type="String" /> <asp:Parameter Name="MiestoNarodenia" Type="String" /> <asp:Parameter Name="čísloDokladu" Type="String" /> <asp:Parameter Name="MiestoPobytu" Type="String" /> <asp:Parameter Name="ŠtátPobytu" Type="String" /> <asp:Parameter Name="ČísloVíza" Type="String" /> <asp:Parameter Name="MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte" Type="String" /> <asp:Parameter Name="TypVízaDokladu" Type="String" /> <asp:Parameter Name="ZačiatokPobytu" Type="String" /> <asp:Parameter Name="KoniecPobytu" Type="String" /> <asp:Parameter Name="ÚčelCesty" Type="String" /> <asp:Parameter Name="ÚčelCestyVoľnýmTextom" Type="String" /> <asp:Parameter Name="PočetPrenocovaní" Type="String" /> <asp:Parameter Name="DospelyDieta" Type="String" /> <asp:Parameter Name="extIdentifikatorCudzinca" Type="String" /> <asp:Parameter Name="original_Id" Type="Int32" /> <asp:Parameter Name="original_Meno" Type="String" /> <asp:Parameter Name="original_Priezvysko" Type="String" /> <asp:Parameter Name="original_DátumNarodenia" Type="String" /> <asp:Parameter Name="original_StatnaPrislusnost" Type="String" /> <asp:Parameter Name="original_ŠtátNarodenia" Type="String" /> <asp:Parameter Name="original_MiestoNarodenia" Type="String" /> <asp:Parameter Name="original_čísloDokladu" Type="String" /> <asp:Parameter Name="original_MiestoPobytu" Type="String" /> <asp:Parameter Name="original_ŠtátPobytu" Type="String" /> <asp:Parameter Name="original_ČísloVíza" Type="String" /> <asp:Parameter Name="original_MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte" Type="String" /> <asp:Parameter Name="original_TypVízaDokladu" Type="String" /> <asp:Parameter Name="original_ZačiatokPobytu" Type="String" /> <asp:Parameter Name="original_KoniecPobytu" Type="String" /> <asp:Parameter Name="original_ÚčelCesty" Type="String" /> <asp:Parameter Name="original_ÚčelCestyVoľnýmTextom" Type="String" /> <asp:Parameter Name="original_PočetPrenocovaní" Type="String" /> <asp:Parameter Name="original_DospelyDieta" Type="String" /> <asp:Parameter Name="original_extIdentifikatorCudzinca" Type="String" /> </UpdateParameters> </asp:SqlDataSource>

    tab file import:

     protected void BtnImportDat_Click(object sender, EventArgs e)
        {
            Label2.Text = "";
            try
            {
            //getting full file path of Uploaded file 
            FileUpload1.SaveAs(Server.MapPath("~/file_storage") + "//" + FileUpload1.FileName);
            FileUpload1.Dispose();
            string CSVFilePath = Path.GetFullPath(Server.MapPath("~/file_storage") + "//" + FileUpload1.FileName);
                 
            string line;
            
            myConnection.Open();
            using (StreamReader file = new StreamReader(CSVFilePath))
            {
                string headerLine = file.ReadLine();
                while ((line = file.ReadLine()) != null)
                {
                    string[] fields = line.Split('\t');
    
                    Int32 number;
                    bool id1 = int.TryParse(fields[0], out number);
    
                    SqlCommand cmd = new SqlCommand("INSERT INTO [knihaHosti] ([Id], [Meno], [Priezvysko], [DátumNarodenia], [StatnaPrislusnost], [ŠtátNarodenia], [MiestoNarodenia], [čísloDokladu], [MiestoPobytu], [ŠtátPobytu], [ČísloVíza], [MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte], [TypVízaDokladu], [ZačiatokPobytu], [KoniecPobytu], [ÚčelCesty], [ÚčelCestyVoľnýmTextom], [PočetPrenocovaní], [DospelyDieta], [extIdentifikatorCudzinca]) VALUES (@Id, @Meno, @Priezvysko, @DátumNarodenia, @StatnaPrislusnost, @ŠtátNarodenia, @MiestoNarodenia, @čísloDokladu, @MiestoPobytu, @ŠtátPobytu, @ČísloVíza, @MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte, @TypVízaDokladu, @ZačiatokPobytu, @KoniecPobytu, @ÚčelCesty, @ÚčelCestyVoľnýmTextom, @PočetPrenocovaní, @DospelyDieta, @extIdentifikatorCudzinca)", myConnection);
                    cmd.Parameters.AddWithValue("@Id", number);
                    cmd.Parameters.AddWithValue("@Meno", fields[1].ToString());
                    cmd.Parameters.AddWithValue("@Priezvysko", fields[2].ToString());
                    cmd.Parameters.AddWithValue("@DátumNarodenia", fields[3].ToString());
                    cmd.Parameters.AddWithValue("@StatnaPrislusnost", fields[4].ToString());
                    cmd.Parameters.AddWithValue("@ŠtátNarodenia", fields[5].ToString());
                    cmd.Parameters.AddWithValue("@MiestoNarodenia", fields[6].ToString());
                    cmd.Parameters.AddWithValue("@čísloDokladu", fields[7].ToString());
                    cmd.Parameters.AddWithValue("@MiestoPobytu", fields[8].ToString());
                    cmd.Parameters.AddWithValue("@ŠtátPobytu", fields[9].ToString());
                    cmd.Parameters.AddWithValue("@ČísloVíza", fields[10].ToString());
                    cmd.Parameters.AddWithValue("@MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte", fields[11].ToString());
                    cmd.Parameters.AddWithValue("@TypVízaDokladu", fields[12].ToString());
                    cmd.Parameters.AddWithValue("@ZačiatokPobytu", fields[13].ToString());
                    cmd.Parameters.AddWithValue("@KoniecPobytu", fields[14].ToString());
                    cmd.Parameters.AddWithValue("@ÚčelCesty", fields[15].ToString());
                    cmd.Parameters.AddWithValue("@ÚčelCestyVoľnýmTextom", fields[16].ToString());
                    cmd.Parameters.AddWithValue("@PočetPrenocovaní", fields[17].ToString());
                    cmd.Parameters.AddWithValue("@DospelyDieta", fields[18].ToString());
                    cmd.Parameters.AddWithValue("@extIdentifikatorCudzinca", fields[19].ToString());
                    cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        cmd.Dispose();
                }
                    file.Dispose();
                    file.Close();
            }
            myConnection.Dispose();
            myConnection.Close();
    
            //delete all files in ~/file_storage/
            string[] files = Directory.GetFiles(Server.MapPath("~/file_storage/"));
            foreach (string file in files)
            {
                File.Delete(file);
            }
            Server.Transfer("Default.aspx");
            }
            catch (Exception)
            {
                Label2.ForeColor = System.Drawing.Color.Red;
                Label2.Text = "Nie je vybratý súbor";
            }


    Saturday, November 9, 2019 9:30 PM

Answers

  • I solved it this way: I changed sql command in SglDtataSource as follows

    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM [knihaHosti] WHERE [Id] = @Id" InsertCommand="INSERT INTO [knihaHosti] ([Id], [Meno], [Priezvysko], [DátumNarodenia], [StatnaPrislusnost], [ŠtátNarodenia], [MiestoNarodenia], [čísloDokladu], [MiestoPobytu], [ŠtátPobytu], [ČísloVíza], [MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte], [TypVízaDokladu], [ZačiatokPobytu], [KoniecPobytu], [ÚčelCesty], [ÚčelCestyVoľnýmTextom], [PočetPrenocovaní], [DospelyDieta], [extIdentifikatorCudzinca]) VALUES (@Id, @Meno, @Priezvysko, @DátumNarodenia, @StatnaPrislusnost, @ŠtátNarodenia, @MiestoNarodenia, @čísloDokladu, @MiestoPobytu, @ŠtátPobytu, @ČísloVíza, @MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte, @TypVízaDokladu, @ZačiatokPobytu, @KoniecPobytu, @ÚčelCesty, @ÚčelCestyVoľnýmTextom, @PočetPrenocovaní, @DospelyDieta, @extIdentifikatorCudzinca)" SelectCommand="SELECT * FROM [knihaHosti]" UpdateCommand="UPDATE [knihaHosti] SET [Meno] = @Meno, [Priezvysko] = @Priezvysko, [DátumNarodenia] = @DátumNarodenia, [StatnaPrislusnost] = @StatnaPrislusnost, [ŠtátNarodenia] = @ŠtátNarodenia, [MiestoNarodenia] = @MiestoNarodenia, [čísloDokladu] = @čísloDokladu, [MiestoPobytu] = @MiestoPobytu, [ŠtátPobytu] = @ŠtátPobytu, [ČísloVíza] = @ČísloVíza, [MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte] = @MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte, [TypVízaDokladu] = @TypVízaDokladu, [ZačiatokPobytu] = @ZačiatokPobytu, [KoniecPobytu] = @KoniecPobytu, [ÚčelCesty] = @ÚčelCesty, [ÚčelCestyVoľnýmTextom] = @ÚčelCestyVoľnýmTextom, [PočetPrenocovaní] = @PočetPrenocovaní, [DospelyDieta] = @DospelyDieta, [extIdentifikatorCudzinca] = @extIdentifikatorCudzinca WHERE [Id] = @Id">
                    
    It works. Thanks

    • Marked as answer by jmzp0 Monday, November 11, 2019 4:54 AM
    Sunday, November 10, 2019 5:03 PM

All replies

  • Well you indicate to UPDATE but you are using a INSERT and guess you meant to say INSERT.

    First thing to do is see what the value of cmd.ExecuteNonQuery() is, ExecuteNonQuery returns an int, you should get 1 for each INSERT, can you see what comes back?


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by jmzp0 Sunday, November 10, 2019 7:56 AM
    • Unmarked as answer by jmzp0 Sunday, November 10, 2019 7:56 AM
    Saturday, November 9, 2019 9:54 PM
    Moderator
  • Hello,

    import to database works well. First I delete all dates from database and then insert new ones from .tab file. All records appear in database. Problem is when I use gridview and want to edit records. No change is made in database and gridview too. Can be some problem with SglDtataSource1? What do you think?

    Thank you.

    Sunday, November 10, 2019 7:55 AM
  • Hello,

    import to database works well. First I delete all dates from database and then insert new ones from .tab file. All records appear in database. Problem is when I use gridview and want to edit records. No change is made in database and gridview too. Can be some problem with SglDtataSource1? What do you think?

    Thank you.

    Your grid knows nothing about what transpired in the database table which means you need to load your grid again to get the newly added records.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, November 10, 2019 10:25 AM
    Moderator
  • I solved it this way: I changed sql command in SglDtataSource as follows

    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM [knihaHosti] WHERE [Id] = @Id" InsertCommand="INSERT INTO [knihaHosti] ([Id], [Meno], [Priezvysko], [DátumNarodenia], [StatnaPrislusnost], [ŠtátNarodenia], [MiestoNarodenia], [čísloDokladu], [MiestoPobytu], [ŠtátPobytu], [ČísloVíza], [MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte], [TypVízaDokladu], [ZačiatokPobytu], [KoniecPobytu], [ÚčelCesty], [ÚčelCestyVoľnýmTextom], [PočetPrenocovaní], [DospelyDieta], [extIdentifikatorCudzinca]) VALUES (@Id, @Meno, @Priezvysko, @DátumNarodenia, @StatnaPrislusnost, @ŠtátNarodenia, @MiestoNarodenia, @čísloDokladu, @MiestoPobytu, @ŠtátPobytu, @ČísloVíza, @MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte, @TypVízaDokladu, @ZačiatokPobytu, @KoniecPobytu, @ÚčelCesty, @ÚčelCestyVoľnýmTextom, @PočetPrenocovaní, @DospelyDieta, @extIdentifikatorCudzinca)" SelectCommand="SELECT * FROM [knihaHosti]" UpdateCommand="UPDATE [knihaHosti] SET [Meno] = @Meno, [Priezvysko] = @Priezvysko, [DátumNarodenia] = @DátumNarodenia, [StatnaPrislusnost] = @StatnaPrislusnost, [ŠtátNarodenia] = @ŠtátNarodenia, [MiestoNarodenia] = @MiestoNarodenia, [čísloDokladu] = @čísloDokladu, [MiestoPobytu] = @MiestoPobytu, [ŠtátPobytu] = @ŠtátPobytu, [ČísloVíza] = @ČísloVíza, [MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte] = @MiestoUdeleniaVízaŠtátVydaniaDokladuoPobyte, [TypVízaDokladu] = @TypVízaDokladu, [ZačiatokPobytu] = @ZačiatokPobytu, [KoniecPobytu] = @KoniecPobytu, [ÚčelCesty] = @ÚčelCesty, [ÚčelCestyVoľnýmTextom] = @ÚčelCestyVoľnýmTextom, [PočetPrenocovaní] = @PočetPrenocovaní, [DospelyDieta] = @DospelyDieta, [extIdentifikatorCudzinca] = @extIdentifikatorCudzinca WHERE [Id] = @Id">
                    
    It works. Thanks

    • Marked as answer by jmzp0 Monday, November 11, 2019 4:54 AM
    Sunday, November 10, 2019 5:03 PM
  • Hi jmzp0,

    It seems that your problem has been solved. If so, please click "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 11, 2019 1:38 AM
    Moderator