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

Answer 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.