sql Could not be resolved in current scope or context

[ChrisWalker]

I'm trying to use sql to get the next value in a sequence. I've tried it both with dual and with another table I know exists that I have in my model as a an actual table. I've tried it both with TYC_DATA and without it. If I log into TOAD with the user Id and password then I can execute these commands directly. I'm probably missing something small but any help?

Connection string (modified the IP)
user id=TYC_DATA;password=********;server=1.1.1.1;home=oraodac11g_home1;direct=True;sid=CMCPPRL;persist security info=True

Code: Select all

  
try
{
                            
     // Create the command.
     System.Data.Common.DbCommand command = entities.Connection.CreateCommand();
    //command.CommandText = "SELECT PATIENT_MEDICATION_SEQ.NEXTVAL FROM TYC_DATA.DUAL";
    command.CommandText = "select detail_id from TYC_DATA.pharmacy_order_detail where detail_id = 10091666";
     command.CommandType = CommandType.Text;

     // Open the connection.
    entities.Connection.Open();

     // Retrieve the data.
     string s = (string)command.ExecuteScalar();
                            
    //     System.Data.Common.DbDataReader reader = command.ExecuteReader();
    //while (reader.Read())
    //{
    //      NextValue = Convert.ToInt32(reader[0]);
    //}
                            
}
catch (Exception ex)
{
    Console.WriteLine("Exception.Message: {0}", ex.Message);
}

error message is
{"'TYC_DATA.pharmacy_order_detail' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly., near multipart identifier, line 1, column 23."}

[ChrisWalker]

I think the issue is that dual is not part of my entity model. I would have thought the connection I created would be to the underlying database and not to the model itself. I.E. I assumed I could call other database tables that I didn't put in my model. I guess the answer to that is no.

[AndreyR]

Try using the ObjectContext.Connection.StoreConnection property instead of the ObjectContext.Connection property, like this:

Code: Select all

var command = ((EntityConnection)(entities.Connection)).StoreConnection.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "MyProc";
((EntityConnection)(entities.Connection)).StoreConnection.Open();
int id = (int)command.ExecuteScalar();

[ChrisWalker]

I don't have authority to modify this database. I can't add a stored procedure and I can't put a trigger on the table to automatically create the id on insert of a new row. Either of those would work for me I think.

What I ended up doing was using a different oracle connection to get the next sequence number, closing that connection and then setting that value in my entity table. A horrible hack but....

[AndreyR]

You are not obliged to use stored procedures, I wanted just to show you the way to use the StoreConnection property. It will anyway be a hack, but a bit more elegant.