ORA-01036: illegal variable name/number

Question

I retrieve data from Oracle database and populate a gridview. Next, I try to run a query to select some data but I get an error. Here is the code:

Db.cs:

public static OracleConnection GetConnection()
{
    OracleConnection connection = null;

    string connectionString = "Data Source=" + Database +
        ";User ID=" + UserID +
        ";Password=" + Password +
        ";Unicode=True";

    try
    {
        connection = new OracleConnection(connectionString);
    }
    catch (OracleException ex)
    {
        throw ex;
    }

    return connection;
}

Parameters are sent from default.aspx.cs:

new Db(database, userID, password);
OracleConnection connection = Db.GetConnection();

main.aspx.cs retrieves all the data:

private OracleConnection connection = new OracleConnection();
private Select select = new Select();

protected void Page_Load(object sender, EventArgs e)
{
    Response.Buffer = true;

    if (Db.IsLoggedIn())
    {
        string selectCommand = 
           "SELECT " + Settings.TABLE + ".* FROM " + Settings.TABLE + " ORDER BY ";
        foreach (string ob in Settings.OB) selectCommand += ob + ", ";

        Session["Error"] = null;
        connection = Db.GetConnection();

        select = new Select(ddlBubID, ddlBusArea, ddlDrillSite, ddlWell, connection);

        gvData.DataKeyNames = Settings.PK;
        gvData.SelectedIndex = -1;

        DS.ConnectionString = connection.ConnectionString;
        DS.SelectCommand = selectCommand.Remove(selectCommand.Length - 2, 2);
        DS.ProviderName = Settings.PROVIDER_NAME;

        PopulateFooter(gvData.FooterRow);
    }
    else
    {
        Session["Error"] = Settings.ERROR_MESSAGE[0, 0];
        Response.Clear();
        Response.Redirect("default.aspx");
    }
}

public string ToolTip(string column)
{
    string value = "";
    OracleCommand cmd = new OracleCommand();
    cmd.Connection = connection;
    cmd.CommandText = "SELECT DISTINCT COMMENTS " +
                      "FROM SYS.ALL_COL_COMMENTS " +
                      "WHERE (TABLE_NAME = 'CTD_PROBLEM_EDIT_V') " +
                      "AND (COLUMN_NAME = " + column + ")";
    cmd.CommandType = CommandType.Text;
    OracleDataReader reader = cmd.ExecuteReader(); // I get an error here
    reader.Read();
        value = reader["COMMENTS"].ToString();
    reader.Close();
    return value;
}

protected void gvData_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
        for (int i = 1; i < e.Row.Cells.Count; i++)
        {
            try
            {
                LinkButton lb =
                    (LinkButton)gvData.HeaderRow.Cells[i].Controls[0];
                lb.ToolTip = ToolTip(lb.Text);

                /* Blah Blah*/
            }
            catch { }

        }

    if (e.Row.RowType == DataControlRowType.Footer)
        PopulateFooter(e.Row);
}

ToolTip(); throws an error: Invalid operation. The connection is closed.

EDIT:

This would have been helpful: Static Classes and Static Class Members

Solution 1

Might not be the problem but this looks weird:

new Db(database, userID, password);
OracleConnection connection = Db.GetConnection();

GetConnection is a static method and thus it does not see any member attributes you might be setting in the constructor (unless they are static as well). If they are all static, consider refactoring your code to use the singleton pattern as it is more readable.

Another thing is that the connection attribute is a member of the page class which is generated for each request (not per application). This means you need either create a new connection in ToolTip method (and any other method that accesses the database) or make the connection attribute static to make it per-application.

Solution 2

Try 2 things:

1.. For your ToolTip() method, the value column to compare for COLUMN_NAME will need to be wrapped properly with single quotes indicating a string/varchar literal value. Likely it's evaluating to COLUMN_NAME = foo when it should be COLUMN_NAME = 'foo'.

cmd.CommandText = "SELECT DISTINCT COMMENTS " +                      
"FROM SYS.ALL_COL_COMMENTS " +                      
"WHERE (TABLE_NAME = 'CTD_PROBLEM_EDIT_V') " +                      
"AND (COLUMN_NAME = '" + column + "')";

2.. Try wrapping your ad-hoc SQL statements in BEGIN and END

3.. Consider refactoring your string building for your SELECT and dynamic ORDER BY clause. That you're doing it on the SelectCommand many lines below isn't obvious to the casual observer or maintainers later in its life.

 string selectCommand = string.Format("SELECT {0}.* FROM {0} ORDER BY {1}"
                                       ,Settings.TABLE 
                                       ,string.Join(",",Settings.OB));

Comments

[Nikita Silverstruk]

1. It evaluates as 'foo'. There are single quotes in the statement.

[Nikita Silverstruk]

2. It gives an error: ORA-06550: line 1, column 105: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored ORA-06550: line 1, column 107: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ;

[Nikita Silverstruk]

3. Thanks! How would you refactor delete or other statement that contain parameters? foreach (string pk in Settings.PK) { deleteCommand += " " + pk + " = :" + pk + " AND"; DS.DeleteParameters.Add( new Parameter(pk, TypeCode.String, e.Keys[pk].ToString())); }

[p.campbell]

@Nikita The code you pasted didn't have single quotes around the value compared against the COLUMN_NAME.

[Nikita Silverstruk]

Oh, sorry. guess I fixed it earlier :) Thanks

[Nikita Silverstruk]

Thanks! The problem actually was in static methods and classes.