Separate function for Oracle Commads?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to call a function in a different class passing only the sql statement
as argument and I should get the results back. The problem is, I dont know
how to retrieve the datareader contents.

Say I'm calling
Line 1: Dim cmdResult = oracleSqlCommand("select * from Main where
main_id='402'")
Line 2: Dim strTest = cmdResult.Item("title")

I get the following error when executing Line 2: "Invalid operation. The
OracleDataReader is closed. "

How do I get the result set after calling oracleSqlCommand function so
that I can manipulate the database column name, values where I'm calling the
function. Is this possible? Thanks for your time.

Oracle FUNCTION:

Public Shared Function oracleSqlCommand(ByVal sqlCommand As String)
Dim oracleConnection As New OracleConnection

Dim oracleCommand As New OracleCommand
Dim oracleReader As OracleDataReader
Dim connString As String = "Data Source=dsn1;User Id=user;password=#123"

Try
oracleConnection.ConnectionString = connString
oracleConnection.Open()
oracleCommand.CommandText = sqlCommand
oracleCommand.Connection = oracleConnection
oracleReader = oracleCommand.ExecuteReader
Dim resultSet = oracleReader
Return resultSet

oracleReader.Close()
oracleCommand.Dispose()

Catch ex As OracleException
'handle the exception
Return ex.Message

Catch ex As Exception
'handle the exception
Return ex.Message

Finally
If Not (oracleConnection Is Nothing) Then
oracleConnection.Close()
End If

End Try
End Function
 
Varad said:
I want to call a function in a different class passing only the sql statement
as argument and I should get the results back. The problem is, I dont know
how to retrieve the datareader contents.

Say I'm calling
Line 1: Dim cmdResult = oracleSqlCommand("select * from Main where
main_id='402'")
Line 2: Dim strTest = cmdResult.Item("title")

I get the following error when executing Line 2: "Invalid operation. The
OracleDataReader is closed. "
Click to expand...

And it's absolutely right. DataReaders are attached to live connections
to the database. You're closing the connection, which makes the reader
useless.
How do I get the result set after calling oracleSqlCommand function so
that I can manipulate the database column name, values where I'm calling the
function. Is this possible? Thanks for your time.
Click to expand...

It sounds like you need to return a ResultSet rather than a DataReader.
 
hi Jon
Thanks for your reply. How do I use a resultset to return the data from
the called function?

Thanks
Varad
 
Varad said:
Thanks for your reply. How do I use a resultset to return the data from
the called function?
Click to expand...

I meant DataSet rather than ResultSet actually - but you just return
it. Use DataAdapter.Fill to fill the DataSet in the first place, and
then return the DataSet to the caller.
 
Hi Jon
I cannot comprehend what you mean! Pls note that I'm not a .net expert and
I have no clue as to what you are suggesting. Can you write an small example
if you dont mind. Thanks.

V
 
Varad said:
I cannot comprehend what you mean! Pls note that I'm not a .net expert and
I have no clue as to what you are suggesting. Can you write an small example
if you dont mind. Thanks.
Click to expand...

Rather than that, I'd suggest you read an ADO.NET tutorial - there are
loads of them on the net. That's likely to be far better in the long
run than me writing one very specific example.

If you look in MSDN, there's a lot about ADO.NET with examples, too.
 
Thanks Jon. I'm already reading through Ado.Net tutorials and have also got
the dataset working. Now I have to figure out how to extract data from a
dataset.
 
Back
Top