CODEWITHSUNDEEP
c#asp.netoracle-databasegridview
HEEN
HEEN

Reputation: 4721

ORA-01036: illegal variable name/number while updating gridview

I want to update a cell of the row in gridview. But I am getting error as

ORA-01036: illegal variable name/number

at cmd.ExecuteNonQuery()

Here is my code below:-

protected void SaveChanges(object sender, EventArgs e)
{   
    myConn.Open();
    string excelData = Grid1ExcelData.Value;
    string excelDeletedIds = Grid1ExcelDeletedIds.Value;

    string[] rowSeparator = new string[] { "|*row*|" };
    string[] cellSeparator = new string[] { "|*cell*|" };

    string[] dataRows = excelData.Split(rowSeparator, StringSplitOptions.None);

    for (int i = 0; i < dataRows.Length; i++)
    {
        string[] dataCells = dataRows[i].Split(cellSeparator, StringSplitOptions.None);
        string mkey = dataCells[0];
        string shipName = dataCells[1];
        string shipCity = dataCells[2];
        string shipAddress = dataCells[3];
        string shipCountry = dataCells[4];
        string orderDate = dataCells[5];
        bool sent = dataCells[6] == "yes";

        string insertUpdateQuery = "";
        if (!string.IsNullOrEmpty(mkey))
        {
            insertUpdateQuery = "UPDATE B_Order_new SET ShipName = @ShipName, ShipCity = @ShipCity, ShipAddress = @ShipAddress, ShipCountry = @ShipCountry, OrderDate = @OrderDate, Sent = @Sent WHERE MKEY = @MKEY";
        }
        else
        {
            insertUpdateQuery = "INSERT INTO B_Order_new (ShipName, ShipCity, ShipAddress, ShipCountry, OrderDate, Sent) " +
                                 "VALUES(@ShipName, @ShipCity, @ShipAddress, @ShipCountry, @OrderDate, @Sent)";
        }
        OracleCommand cmd = new OracleCommand(insertUpdateQuery, myConn);
        var orderedOn = DateTime.ParseExact(orderDate, "dd/MM/yyyy", null);
        cmd.Parameters.Add("@ShipName", OracleType.VarChar).Value = shipName;
        cmd.Parameters.Add("@ShipCity", OracleType.VarChar).Value = shipCity;
        cmd.Parameters.Add("@ShipAddress", OracleType.VarChar).Value = shipAddress;
        cmd.Parameters.Add("@ShipCountry", OracleType.VarChar).Value = shipCountry;
        cmd.Parameters.Add("@OrderDate", OracleType.DateTime).Value = orderedOn;
        cmd.Parameters.Add("@Sent", OracleType.Char).Value = true;

        if (!string.IsNullOrEmpty(mkey))
        {
            cmd.Parameters.Add("@MKEY", OracleType.Number).Value = mkey;
        }
        cmd.ExecuteNonQuery();
        if (insertUpdateQuery != "")
        {
            Page.ClientScript.RegisterStartupScript(typeof(Page), "CloseScript", "alert('Data Updated succesfully');", true);
        }
    }
    if (!string.IsNullOrEmpty(excelDeletedIds))
    {
        OracleCommand deleteComm = new OracleCommand("DELETE FROM Orders WHERE OrderID IN (" + excelDeletedIds + ")", myConn);
        deleteComm.ExecuteNonQuery();
    }
    myConn.Close();
    Grid1.DataBind();
}

Upvotes: 0

Views: 2663

Answers (1)

sstan
sstan

Reputation: 36473

The problem is that you are using the wrong bind variable syntax (looks like you're using SQL Server parameter binding syntax). Oracle's ADO.NET provider expects you to use a : prefix rather than a @.

So try something like this instead:

insertUpdateQuery = "UPDATE B_Order_new SET ShipName = :ShipName, ShipCity = :ShipCity, ...

And then when setting the values, you don't need to prefix it:

cmd.Parameters.Add("ShipName", OracleType.VarChar).Value = shipName;
cmd.Parameters.Add("ShipCity", OracleType.VarChar).Value = shipCity;
...

EDIT

Also, if you are binding the parameters by name, make sure you set the OracleCommand.BindByName property to true. Otherwise, the binding will be done positionally.

Upvotes: 2

Related Questions