sql Could not be resolved in current scope or context

sql Could not be resolved in current scope or context

Postby ChrisWalker » Thu 27 May 2010 15:21

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
 
Posts: 10
Joined: Mon 01 Mar 2010 20:17

Postby ChrisWalker » Tue 01 Jun 2010 13:50

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.
ChrisWalker
 
Posts: 10
Joined: Mon 01 Mar 2010 20:17

Postby AndreyR » Tue 01 Jun 2010 14:59

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();
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby ChrisWalker » Tue 01 Jun 2010 17:24

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....
ChrisWalker
 
Posts: 10
Joined: Mon 01 Mar 2010 20:17

Postby AndreyR » Wed 02 Jun 2010 11:12

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle