Fail to save multiple data from gridview to database

Question

Here is the HTML markup of the gridview.I mean aspx page

 <asp:GridView ID="Gridview1" runat="server" ShowFooter="true" AutoGenerateColumns="false"
    OnRowCreated="Gridview1_RowCreated" Height="145px">
    <Columns>
        <asp:BoundField DataField="RowNumber" HeaderText="Row Number" />
        <asp:TemplateField HeaderText="Header 1">
            <ItemTemplate>
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Header 2">
            <ItemTemplate>
                <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Header 3">
            <ItemTemplate>
                <asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="true" DataTextField="CURRENCY_NAME"
                    DataValueField="CURRENCY_ID">
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Header 4">
            <ItemTemplate>
                <asp:DropDownList ID="DropDownList2" runat="server" AppendDataBoundItems="true" DataTextField="BRAND_NAME"
                    DataValueField="BRAND_ID">
                </asp:DropDownList>
            </ItemTemplate>
            <FooterStyle HorizontalAlign="Right" />
            <FooterTemplate>
                <asp:Button ID="ButtonAdd" runat="server" Text="Add New Row" OnClick="ButtonAdd_Click" />
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton1_Click">Remove</asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<asp:Button ID="BtnSave" runat="server" Text="Save All" OnClick="BtnSave_Click" />
<asp:Label ID="lblMessage" runat="server" Text=""></asp:Label>`

Here is the code behind to save the data into the database

private void InsertRecords(StringCollection sc)
    {
        StringBuilder sb = new StringBuilder(string.Empty);
        string[] splitItems = null;
        const string sqlStatement = "INSERT INTO GridViewDynamicData (Field1,Field2,Field3,Field4) VALUES";
        foreach (string item in sc)
        {
            if (item.Contains(","))
            {
                splitItems = item.Split(",".ToCharArray());
                sb.AppendFormat("{0}('{1}','{2}','{3}','{4}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3]);
            }
        }

        using (OracleConnection strConn = GetConnection())
        {

                strConn.Open();
                OracleCommand cmd = new OracleCommand(sb.ToString(), strConn);

                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();


                lblMessage.Text = "Records successfully saved!";


        }
    }

    protected void BtnSave_Click(object sender, EventArgs e)
    {
        int rowIndex = 0;
        StringCollection sc = new StringCollection();
        if (ViewState["CurrentTable"] != null)
        {
            DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
            if (dtCurrentTable.Rows.Count > 0)
            {
                for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
                {
                    //extract the TextBox values  
                    TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox1");
                    TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("TextBox2");
                    DropDownList ddl1 = (DropDownList)Gridview1.Rows[rowIndex].Cells[3].FindControl("DropDownList1");
                    DropDownList ddl2 = (DropDownList)Gridview1.Rows[rowIndex].Cells[4].FindControl("DropDownList2");
                    //get the values from TextBox and DropDownList  
                    //then add it to the collections with a comma "," as the delimited values  
                    sc.Add(string.Format("{0},{1},{2},{3}", box1.Text, box2.Text, ddl1.SelectedItem.Text, ddl2.SelectedItem.Text));
                    rowIndex++;
                }
                //Call the method for executing inserts  
                InsertRecords(sc);
            }
        }   
    }

My database table is here

CREATE TABLE ERP.GRIDVIEWDYNAMICDATA
(
  FIELD1  VARCHAR2(500 BYTE),
  FIELD2  VARCHAR2(500 BYTE),
  FIELD3  VARCHAR2(500 BYTE),
  FIELD4  VARCHAR2(500 BYTE)
)

When I am running this project it is showing error "ORA-00911: invalid character". I don't know what is wrong. Any help will be appreciated.

Can you please take the values that you are trying to insert into DB, and write an insert statement directly.. and figure out what is wrong in the incoming data? — Amnesh Goel, Sep 17 '15 at 06:04
Insert into ERP.GRIDVIEWDYNAMICDATA values (your values); try this first directly on your oracle.. instead of checking your C# code.. because error is in incoming data.. and then accordingly you would need to restrict your user to enter that data.. — Amnesh Goel, Sep 17 '15 at 06:05
here what it is trying to insert 'INSERT INTO GridViewDynamicData (Field1,Field2,Field3,Field4) VALUES('a','b','US $','Dual'); INSERT INTO GridViewDynamicData (Field1,Field2,Field3,Field4) VALUES('aa','bbb','US $','Tiger');' — Nagib Mahfuz, Sep 17 '15 at 06:13
when i am trying this directly into oracle its work fine...no problem there.. — Nagib Mahfuz, Sep 17 '15 at 06:16
DId this fail while you tried to insert directly in your DB? Not via ur code. — Amnesh Goel, Sep 17 '15 at 06:16
while trying directly into database it did not fail...it insert succesfully — Nagib Mahfuz, Sep 17 '15 at 06:32
Ok, while you are inserting value in your UI (c# view) then avoid `$` sign, and give it a try.. probably that could be the problem — Amnesh Goel, Sep 17 '15 at 06:33

score 0 · Accepted Answer · edited May 23 '17 at 10:27

I am not having enough reputation to comment, so I am posting my research as an answer.

Most probable cause is using ; in query building.

Remove ;(semi-colon) from the end of SQL string. From the SQL query you are building from the code.

With semicolon (May causing the error)

sb.AppendFormat("{0}('{1}','{2}','{3}','{4}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3]);

Without Semicolon (Should try this)

sb.AppendFormat("{0}('{1}','{2}','{3}','{4}') ", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3]);

Or

Your string may not have straight ' single quotes. Try to write that again. (It seems OK though in your code posted along with question. But nothing wrong in verifying.)

References: ORA-00911: invalid character

https://community.oracle.com/thread/2511511

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/hol08/dotnet/getstarted-c/getstarted_c_otn.htm

your answer was help full..after removing comma its adding single row...but while adding multiple row its giving error..thanks anyway — Nagib Mahfuz, Sep 17 '15 at 07:38

Fail to save multiple data from gridview to database

1 Answers1