Oracle 11G and SqlDataSource: The ugly 'ORA-01036: illegal variable name/number' Issue

Update: Fixed as described here: http://weblogs.asp.net/kencox/archive/2008/02/25/fixed-oracle-11g-and-sqldatasource-the-ugly-ora-01036-illegal-variable-name-number-issue.aspx 

 ----

I've created a short video to explain my problem with Oracle and SqlDataSource. If you'd rather watch than read, go see the video.

I'm trying to do some extremely simple drag and drop development in Visual Web Developer 2008 with the Oracle 11g database and ODP.NET. According to Oracle, "The data provider can be used with the latest .NET Framework 3.5 version. "

The SELECT statement works fine, but any attempt to use UPDATE via the SQLDataSource UpdateCommand nets me the infamous 'ORA-01036: illegal variable name/number'  error.

I wouldn't mind using one of my MSDN Support Incidents to have Microsoft take a look at this issue, but I'm not sure the problem is Microsoft's. It seems like Oracle is doing a poor job in its Visual Studio integration.

Question: Has anyone successfully used an UpdateCommand statement in SQLDataSource against Oracle 11g? If so, let me know!

Below, you see the entire code as generated in Visual Web Developer 2008. To see it in action, check the video.

Ken

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataSourceID="SqlDataSource1" 
            EmptyDataText="There are no data records to display.">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="ACCOUNTID" HeaderText="ACCOUNTID" 
                    SortExpression="ACCOUNTID" />
                <asp:BoundField DataField="ACCOUNTNAME" HeaderText="ACCOUNTNAME" 
                    SortExpression="ACCOUNTNAME" />
                <asp:BoundField DataField="ACCOUNTLOCATION" HeaderText="ACCOUNTLOCATION" 
                    SortExpression="ACCOUNTLOCATION" />
                <asp:BoundField DataField="ACCOUNTPHONE" HeaderText="ACCOUNTPHONE" 
                    SortExpression="ACCOUNTPHONE" />
                <asp:BoundField DataField="ACCOUNTCONTACT" HeaderText="ACCOUNTCONTACT" 
                    SortExpression="ACCOUNTCONTACT" />
                <asp:BoundField DataField="ACCOUNTWEBSITE" HeaderText="ACCOUNTWEBSITE" 
                    SortExpression="ACCOUNTWEBSITE" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" 
            ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>" 
            SelectCommand="SELECT &quot;ACCOUNTID&quot;, &quot;ACCOUNTNAME&quot;, &quot;ACCOUNTLOCATION&quot;, &quot;ACCOUNTPHONE&quot;, &quot;ACCOUNTCONTACT&quot;, &quot;ACCOUNTWEBSITE&quot; FROM &quot;ACCOUNTS&quot;" 
            UpdateCommand="UPDATE ACCOUNTS SET ACCOUNTLOCATION = 'This Place' WHERE (ACCOUNTID = 4)">
        </asp:SqlDataSource>
    
    </div>
    </form>
</body>
</html>
//////////
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
}

6 Comments

  • Thanks Ken.
    I migrated an Oracle world to SQL Server for Siemens and am still recovering from that experience. ;-)

  • i have exactly the same problem.Could you find the solution?
    regards

  • I have the same problem of getting an exception ORA 01036 illegal variable name/number and i am using oracle 8i, updation works fine if I give the update command and select command in the "DESIGN MODE" but it gives above mentioned exception when i code the command in the code behind page. Can anybody help?

  • I had similar problem today ...

    I was trying to make a simple code to see how connection to Oracle works ...

    this is how my code looked like :

    OracleCommand insCom = new OracleCommand("INSERT INTO pomoctechniczna VALUES(1,1,:Cat,:Sub,"
    + ":Opis,1)" , DataBaseCon);

    insCom.Parameters.Add(":Cat", OracleType.Number,11);
    insCom.Parameters[":Cat"].Value = Convert.ToDecimal(categoryList.SelectedItem.Value);

    insCom.Parameters.Add(":Sub", OracleType.Number, 11);
    insCom.Parameters[":Sub"].Value = Convert.ToDecimal(subjectList.SelectedItem.Value);

    insCom.Parameters.Add(":Opis", OracleType.VarChar, 50);
    insCom.Parameters[":Opis"].Value = descriptionTextBox.Text;

    I had this "ORA-01036: illegal variable name/number "

    and this is how it looks like now :

    OracleCommand insCom = new OracleCommand("BEGIN INSERT INTO pomoctechniczna VALUES(1,1,:Cat,:Sub,"
    + ":Opis,1); END;" , DataBaseCon);


    And now it works ... Anonymous block must be used ( BEGIN ... END ) ... the prefix really doesn't matter :)

  • Because you're using the Oracle provider shipped with .NET (System.Data.OracleCliente), and it uses ExecuteNonQuery underneath, you might have to surround your UPDATE clause with a BEGIN and END, to make it an anonymous block. Something like this:

    UpdateCommand="
    BEGIN
    UPDATE ACCOUNTS SET ACCOUNTLOCATION = 'This Place' WHERE (ACCOUNTID = 4)
    END
    "

  • Another way this bug can happen in oracle 10 with PL/SQL:

    starting a comment with -- which contains code/variables. Using /* */ for the comment makes the error dissappear

Comments have been disabled for this content.