Hi

I am trying to extract data from an Oracle RDB database on an Alpha (seems not many / no-one is doing this - or if they are, they aren't putting samples etc up on the Net). I also come from a COBOL/VMS background (no laughing !) with 12 years experience of this and 4 days experience of ASP .NET.

Anyway, I have figured out how to connect to the database and execute a query - I know I should really use a stored procedure but haven't figured that pout yet.

How do I get the data from my query into a DataGrid etc ?  I can do it if I set the query in the SQLDataSource and then Bind this to the DataGrid - but how to I do it via code and not use the SqlDataSource ?  My code so far is below :

Dim myConnection As String

 

If myConnection = "" Then

myConnection = "Dsn=dsnName;server=servername;uid=username;pwd=password;tlo=0;dba=W;svr=svrName;cls=generic;database=attach 'filename l$iv_testdb';xpt=2=tcp/ip;cso=1"

End If

Dim myConn As New Data.Odbc.OdbcConnection(myConnection)

Dim mySelectQuery As String = "SELECT stock, shortname from iv_stocks limit to 10 rows"

Dim myOdbcCommand As New Data.Odbc.OdbcCommand(mySelectQuery)

myOdbcCommand.Connection = myConn

myConn.Open()

myOdbcCommand.ExecuteNonQuery()

 

myOdbcCommand.Connection.Close()

TIA

Rob

I looked in the Help file just to double check on this one, ExecuteNonQuery definition:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For CREATE TABLE and DROP TABLE statements, the return value is 0. For all other types of statements, the return value is -1.

You can use ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables); or to change the data in a database, without using a DataSet, by executing UPDATE, INSERT, or DELETE statements.

Although ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For CREATE TABLE and DROP TABLE statements, the return value is 0. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

-----------------------

I would either use a DataAdapter or DataReader

DataAdapter:

Dim myConnection As String = ""

        If myConnection = "" Then
            myConnection = "Dsn=dsnName;server=servername;uid=username;pwd=password;tlo=0;dba=W;svr=svrName;cls=generic;database=attach 'filename l$iv_testdb';xpt=2=tcp/ip;cso=1"
        End If

        Dim myConn As New Data.Odbc.OdbcConnection(myConnection)
        Dim mySelectQuery As String = "SELECT stock, shortname from iv_stocks limit to 10 rows"

        Dim myOdbcCommand As New Data.Odbc.OdbcCommand(mySelectQuery)
        myOdbcCommand.Connection = myConn
        myConn.Open()

        Dim da As New Data.Odbc.OdbcDataAdapter
        Dim ds As New Data.DataSet

        da.SelectCommand = myOdbcCommand

        Try
            da.Fill(ds, "UserDefinedTableName")
        Catch ex As Exception
            'maybe display the error message
            lblError.text = ex.Message
        End Try
        myConn.Close()

        DataGrid1.DataSource = ds.Tables("UserDefinedTableName")
        DataGrid1.DataBind()

 --------------------

 DataReader

Dim myConnection As String = ""

        If myConnection = "" Then
            myConnection = "Dsn=dsnName;server=servername;uid=username;pwd=password;tlo=0;dba=W;svr=svrName;cls=generic;database=attach 'filename l$iv_testdb';xpt=2=tcp/ip;cso=1"
        End If

        Dim myConn As New Data.Odbc.OdbcConnection(myConnection)
        Dim mySelectQuery As String = "SELECT stock, shortname from iv_stocks limit to 10 rows"

        Dim myOdbcCommand As New Data.Odbc.OdbcCommand(mySelectQuery)
        myOdbcCommand.Connection = myConn
        myConn.Open()

        Try
            Dim rdr As Data.Odbc.OdbcDataReader = myOdbcCommand.ExecuteReader
            DataGrid1.DataSource = rdr
            DataGrid1.DataBind()
        Catch ex As Exception

        End Try
        
        myConn.Close()

 You also may want to consider using the Imports statement:

Imports System.Data
Imports System.Data.Odbc

That way you can define objects like Dim myOdbcCommand As New OdbcCommand rather than  

Dim myOdbcCommand As New Data.Odbc.OdbcCommand

I think it reads easier.