Returning resultset to ADO.NET dataset

gp_mk · December 17th, 2003, 11:03 AM

I am encountering a strange problem when trying to return the results of a simple SELECT query to an ADO.NET recordset. I can do this without problems with a stored procedure, it is only when I use a "hard-coded" SELECT statement that I get issues. I need to do this because some of my SQL is dynamically generated.

Example code is as follows:-

OracleCommand oraCmd;
OracleDataAdapter oraDa;
System.Data.DataSet ds;

sql = "SELECT * FROM TBLSUBJECTS;";

oraCmd.CommandType = CommandType.Text;
oraCmd.CommandText = sql;

ds = new DataSet();
oraDa = new OracleDataAdapter(oraCmd);
oraDa.Fill(ds, scrTable);

...when the .Fill() method executes, I get the following error returned from Oracle:-

"ORA-01036: illegal variable name/number"

Can anyone tell me what I am doing wrong? Do I need to declare a RefCursor and return the results through that?

Many Thanks,
Gary

joefawcett · December 19th, 2003, 01:33 PM

Did you instantiate the OracleCommand?

Code:

oraCmd = new OracleCommand();

or

Code:

oraCmd = new OracleCommand(sql, conn);

where conn is your connection.

--

Joe

rusted · December 22nd, 2003, 10:28 AM

Yep ...

Your problem is the semi-colon ';' at the end of the statement. Drop this and all will be well!:)

gp_mk · December 22nd, 2003, 10:44 AM

Cheers!