locked
Failed to Insert data into database RRS feed

  • Question

  • User-1872583635 posted

    Hi All,

    I am retrieving multiple data from database1 into gridview, in the gridview column I put label and textbox.
    the label is bound to database1 meanwhile the textboxes is where I input the text for later I want to use to insert into another database2.
    I try to insert the info, the data input successfully but for the textbox only okay for the first column when I disable another 2 textbox column (which is textbox 4 and 5), if I put another 2 column data insert fail.
    here is what is in the front view
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
                
                    <Columns>
                    <asp:TemplateField HeaderText="BNE No">
                            <ItemTemplate>
                                <asp:Label ID="Label4" runat="server" Text='<%#Bind("BNENo") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField> 
                    <asp:TemplateField HeaderText="ECN No">
                        <ItemTemplate>
                                <asp:Label ID="Label5" runat="server" Text='<%#Bind("KVNo") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Rec Date">
                        <ItemTemplate>
                                <asp:Label ID="Label6" runat="server" Text='<%#Bind("ReceivedDate") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Issued Date">
                        <ItemTemplate>
                                <asp:Label ID="Label7" runat="server" Text='<%#Bind("IssuedDate") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Status">
                        <ItemTemplate>
                                <asp:Label ID="Label8" runat="server" Text='<%#Bind("Status") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Location">
                        <ItemTemplate>
                            <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>      
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Time">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox> 
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Date">
                    <ItemTemplate>
                        <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox> 
                    </ItemTemplate>
                    </asp:TemplateField>
                    </Columns>
             </asp:GridView>
    here is the insert coding
    protected void Button2_Click(object sender, EventArgs e)
                    {
    
                        foreach (GridViewRow g1 in GridView1.Rows)
                        {
                            con.Open();
                            try
                            {
                                string bnenO = (g1.FindControl("Label4") as Label).Text;
                                string kvnO = (g1.FindControl("Label5") as Label).Text;
                                string RDate = (g1.FindControl("Label6") as Label).Text;
                                string IDate = (g1.FindControl("Label7") as Label).Text;
                                string Stat = (g1.FindControl("Label8") as Label).Text;
                                string Dept = (g1.FindControl("TextBox3") as TextBox).Text;
                                string Time = (g1.FindControl("TextBox4") as TextBox).Text;
                                string DateCi = (g1.FindControl("TextBox5") as TextBox).Text;
                                OleDbCommand cmd = con.CreateCommand();
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = "INSERT INTO tblMoveBNE(BNENo,RNNo,ReceivedDate,IssuedDate,Status,Department,Time,DateC) values ('" + bnenO + "','" + kvnO + "','" + RDate + "','" + IDate + "','" + Stat + "','" + Dept + "','" + Time + "','" + DateCi + "')";
                                cmd.ExecuteNonQuery();
                                con.Close();
                                Label12.Text = "record inserted successfully";
                            }
                            catch
                            {
                                Label12.Text = "record error";
                            }
                        }
                    
                    
                }
    please advice me. I am still new into coding area and I am still in learning process. to tell you I get this coding from youtube tutorial so I will be glad if you guys can help me during my learning process :)
    Monday, October 9, 2017 1:58 AM

Answers

  • User-707554951 posted

    Hi  yura.s,

    The following working sample show you how to insert to data base from gridview

    CREATE TABLE [dbo].[tblMoveBNETable]
    (
    	[BNENo] NVARCHAR(50) NOT NULL PRIMARY KEY, 
        [RNNo] NVARCHAR(50) NULL, 
        [ReceivedDate] NVARCHAR(50) NULL, 
        [IssuedDate] NVARCHAR(50) NULL, 
        [Status] NVARCHAR(50) NULL, 
        [Department] NVARCHAR(50) NULL, 
        [Time] NVARCHAR(50) NULL, 
        [DateC] NVARCHAR(50) NULL
    )

    Code:

     <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">        
                    <Columns>
                    <asp:TemplateField HeaderText="BNE No">
                            <ItemTemplate>
                                <asp:Label ID="Label4" runat="server" Text='<%#Bind("BNENo") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField> 
                    <asp:TemplateField HeaderText="ECN No">
                        <ItemTemplate>
                                <asp:Label ID="Label5" runat="server" Text='<%#Bind("KVNo") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Rec Date">
                        <ItemTemplate>
                                <asp:Label ID="Label6" runat="server" Text='<%#Bind("ReceivedDate") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Issued Date">
                        <ItemTemplate>
                                <asp:Label ID="Label7" runat="server" Text='<%#Bind("IssuedDate") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Status">
                        <ItemTemplate>
                                <asp:Label ID="Label8" runat="server" Text='<%#Bind("Status") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Location">
                        <ItemTemplate>
                            <asp:TextBox ID="TextBox3" runat="server" ></asp:TextBox>      
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Time">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox4" runat="server" ></asp:TextBox> 
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Date">
                    <ItemTemplate>
                        <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox> 
                    </ItemTemplate>
                    </asp:TemplateField>
                    </Columns>
             </asp:GridView>
            <asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click" />
            <asp:Label ID="Label12" runat="server" Text="Label"></asp:Label>

    CodeBehind:

     protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack) { 
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[] { new DataColumn("BNENo",typeof(int)),
                    new DataColumn("KVNo",typeof(string)),
                    new DataColumn("ReceivedDate",typeof(string)),
                    new DataColumn("IssuedDate",typeof(string)),
                     new DataColumn("Status",typeof(string)),
                });
                dt.Rows.Add(1, "KVNo1", "ReceivedDate1", "IssuedDate1", "Status1");
                dt.Rows.Add(2, "KVNo2", "ReceivedDate2", "IssuedDate2", "Status2");
                GridView1.DataSource = dt;
                GridView1.DataBind();
                }
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                string constr = ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {                 
                        cmd.Connection = con;
                        con.Open();
                        foreach (GridViewRow g1 in GridView1.Rows)
                        {                   
                            try
                            {
                                string bnenO = (g1.FindControl("Label4") as Label).Text;
                                string kvnO = (g1.FindControl("Label5") as Label).Text;
                                string RDate = (g1.FindControl("Label6") as Label).Text;
                                string IDate = (g1.FindControl("Label7") as Label).Text;
                                string Stat = (g1.FindControl("Label8") as Label).Text;
                                string Dept = (g1.FindControl("TextBox3") as TextBox).Text;
                                string Time = (g1.FindControl("TextBox4") as TextBox).Text;
                                string DateCi = (g1.FindControl("TextBox5") as TextBox).Text;
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = "INSERT INTO tblMoveBNETable(BNENo,RNNo,ReceivedDate,IssuedDate,Status,Department,Time,DateC) values ('" + bnenO + "','" + kvnO + "','" + RDate + "','" + IDate + "','" + Stat + "','" + Dept + "','" + Time + "','" + DateCi + "')";
                                cmd.ExecuteNonQuery();
                              
                                Label12.Text = "record inserted successfully";
                            }
                            catch
                            {
                                Label12.Text = "record error";
                            }
                        }
                        con.Close();
                    }
                }
            }

    Output:

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 10, 2017 2:31 AM

All replies

  • User1068175894 posted

    do this:

    1.- put a breakpoint in the line after:

    cmd.CommandText = "INS...

    2.- Copy the value from cmd.CommandText (hovering over it or clicking on it and typing shift-f9)

    3.- Run it manually on Sql Server Management Studio(You will get a more undestandable message in management studio)

    Monday, October 9, 2017 1:21 PM
  • User-707554951 posted

    Hi  yura.s,

    The following working sample show you how to insert to data base from gridview

    CREATE TABLE [dbo].[tblMoveBNETable]
    (
    	[BNENo] NVARCHAR(50) NOT NULL PRIMARY KEY, 
        [RNNo] NVARCHAR(50) NULL, 
        [ReceivedDate] NVARCHAR(50) NULL, 
        [IssuedDate] NVARCHAR(50) NULL, 
        [Status] NVARCHAR(50) NULL, 
        [Department] NVARCHAR(50) NULL, 
        [Time] NVARCHAR(50) NULL, 
        [DateC] NVARCHAR(50) NULL
    )

    Code:

     <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">        
                    <Columns>
                    <asp:TemplateField HeaderText="BNE No">
                            <ItemTemplate>
                                <asp:Label ID="Label4" runat="server" Text='<%#Bind("BNENo") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField> 
                    <asp:TemplateField HeaderText="ECN No">
                        <ItemTemplate>
                                <asp:Label ID="Label5" runat="server" Text='<%#Bind("KVNo") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Rec Date">
                        <ItemTemplate>
                                <asp:Label ID="Label6" runat="server" Text='<%#Bind("ReceivedDate") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Issued Date">
                        <ItemTemplate>
                                <asp:Label ID="Label7" runat="server" Text='<%#Bind("IssuedDate") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Status">
                        <ItemTemplate>
                                <asp:Label ID="Label8" runat="server" Text='<%#Bind("Status") %>'></asp:Label>  
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Location">
                        <ItemTemplate>
                            <asp:TextBox ID="TextBox3" runat="server" ></asp:TextBox>      
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Time">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox4" runat="server" ></asp:TextBox> 
                            </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Date">
                    <ItemTemplate>
                        <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox> 
                    </ItemTemplate>
                    </asp:TemplateField>
                    </Columns>
             </asp:GridView>
            <asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click" />
            <asp:Label ID="Label12" runat="server" Text="Label"></asp:Label>

    CodeBehind:

     protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack) { 
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[] { new DataColumn("BNENo",typeof(int)),
                    new DataColumn("KVNo",typeof(string)),
                    new DataColumn("ReceivedDate",typeof(string)),
                    new DataColumn("IssuedDate",typeof(string)),
                     new DataColumn("Status",typeof(string)),
                });
                dt.Rows.Add(1, "KVNo1", "ReceivedDate1", "IssuedDate1", "Status1");
                dt.Rows.Add(2, "KVNo2", "ReceivedDate2", "IssuedDate2", "Status2");
                GridView1.DataSource = dt;
                GridView1.DataBind();
                }
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                string constr = ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {                 
                        cmd.Connection = con;
                        con.Open();
                        foreach (GridViewRow g1 in GridView1.Rows)
                        {                   
                            try
                            {
                                string bnenO = (g1.FindControl("Label4") as Label).Text;
                                string kvnO = (g1.FindControl("Label5") as Label).Text;
                                string RDate = (g1.FindControl("Label6") as Label).Text;
                                string IDate = (g1.FindControl("Label7") as Label).Text;
                                string Stat = (g1.FindControl("Label8") as Label).Text;
                                string Dept = (g1.FindControl("TextBox3") as TextBox).Text;
                                string Time = (g1.FindControl("TextBox4") as TextBox).Text;
                                string DateCi = (g1.FindControl("TextBox5") as TextBox).Text;
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = "INSERT INTO tblMoveBNETable(BNENo,RNNo,ReceivedDate,IssuedDate,Status,Department,Time,DateC) values ('" + bnenO + "','" + kvnO + "','" + RDate + "','" + IDate + "','" + Stat + "','" + Dept + "','" + Time + "','" + DateCi + "')";
                                cmd.ExecuteNonQuery();
                              
                                Label12.Text = "record inserted successfully";
                            }
                            catch
                            {
                                Label12.Text = "record error";
                            }
                        }
                        con.Close();
                    }
                }
            }

    Output:

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 10, 2017 2:31 AM
  • User-1872583635 posted

    Hi Cathy,

    thank you for your help, I can see it working there. however I have several question.

    1. I am using access database for my data, can I use this sql command as well?

    2. before I insert the data, I retrieve the data from another access databse (database1), can I still be able to insert the generated table to the database2?

    I will try to adjust my coding using yours.

    do you want to see the full coding?

    Tuesday, October 10, 2017 8:28 AM
  • User-707554951 posted

    Hi  yura.s,

    1. I am using access database for my data, can I use this sql command as well?

    SqlClient should be used to access SQL Server 

    ADO.NET uses the OLE-DB data provider to read a Microsoft Access database

    http://www.c-sharpcorner.com/article/read-microsoft-access-database-in-C-Sharp/

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples#sqlclient

    can I still be able to insert the generated table to the database2?

    Yes, of course, you could modify your code based on sample I provided.

    Best regards

    Cathy

    Wednesday, October 11, 2017 1:42 AM
  • User-1872583635 posted

    hi Cathy, thanks for reply.

    the problem already solve, I found the problem.

    but thanks to you too.Laughing

    Wednesday, October 11, 2017 6:02 AM