We help IT Professionals succeed at work.

how to use dataview with oracle database, BIG Points !!!

mackanenziebl
on
293 Views
Last Modified: 2011-10-03
I'm using asp.net with code behind and need to use a dataview based on my database, using Oracle.  Haven't been able to make this work though.  Can anyone help me on setting up my dataview?  Here's some of my code...

  Dim objCommand, sql, sqlCount, sCount, sWherePos, sSql1, sSql2, sql3, dbread, sLoannum
  Dim strConnection as string
  Dim objConnection as OleDbConnection
  strConnection = ConfigurationSettings.AppSettings("ConnectionString")

  objConnection = New OleDbConnection(strConnection)
  objConnection.Open()

   dbread=New Dataset()
   sql3="SELECT Loannum FROM Loandata WHERE Serialnum='" & ViewState("LastKey") & "'"
   objCommand = New OleDbCommand(sql3,objConnection)
   dbread = objCommand.ExecuteReader()
   If (dbread.read()) Then
      sLoannum=dbread("Loannum")
   End If
   dbread.Close()
   objConnection.Close()
   objConnection.Open()

   sWherePos = instr(session("searchsql"), "WHERE")
   sSql1 = left(session("searchsql"),sWherePos + 5)
   sSql2 = right(session("searchsql"),len(session("searchsql"))-(sWherePos + 5))
   If Session("Flag")="Next" Then
      sql=sSql1 & "Serialnum > '" & ViewState("LastKey") &"' AND Loannum >= '" & sLoannum & "' AND " & sSql2
        sql="SELECT * FROM (" & sql & ") ORDER BY Loannum"
      'sql="SELECT * FROM (" & Session("searchsql") & ") WHERE Serialnum > '" & ViewState("LastKey") & "'"
   Else
      sql="SELECT * FROM (" & Session("searchsql") & ") WHERE Serialnum <'" & ViewState("FirstKey") & "' AND ROWNUM < 11 ORDER BY Loannum DESC"
   End If
searchfor.text=sql
  objCommand = New OleDbCommand(sql,objConnection)

'Dim dv as DataView = New DataView(set to what here ???)
        'dv.Sort = SortField
'Set appsgrid.DataSource=dv ???

  appsgrid.DataSource = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
  appsgrid.DataBind()
  objConnection.Close()
Comment
Watch Question

Hi,

I'm not sure if this will totally solve your problem (I usually only work on windows based apps, not ASP.NET).  There are a couple of things I've noticed in the above code that might be giving you some trouble:

   dbread=New Dataset()
   sql3="SELECT Loannum FROM Loandata WHERE Serialnum='" & ViewState("LastKey") & "'"
   objCommand = New OleDbCommand(sql3,objConnection)
   dbread = objCommand.ExecuteReader()
   If (dbread.read()) Then
      sLoannum=dbread("Loannum")
   End If
   dbread.Close()
   objConnection.Close()
   objConnection.Open()

Firstly, with the above code, I assume that you are trying to retrieve a single value from a record and that only one record will be retrieved.

The key here is the method you are using on objCommand.  ExecuteReader will return an object of type IDbReader, whereas you are attempting to assign this directly to a dataset.  The difference is essentiually that the Reader will provide a readonly forward cursor for the data, while the dataset will load all the data into DataTables in memory.  

If you wanted to use the datareader for this you would use the following:

   ' Set up your SQL
   sql3="SELECT Loannum FROM Loandata WHERE Serialnum='" & ViewState("LastKey") & "'"

   ' Create the command object directly from the connection
   objCommand = objConnection.CreateCommand()

   ' Set the sql for your command
   objCommand.CommandText = sql3
   
   ' Read in the record (s)
   Dim objDataReader as OldDbDataReader = objCommand.ExecuteReader()

   If (objDataReader.Read()) {
     ...
   }

In this case however, I'd recommend using the ExecuteScalar method of objCommand.  This method will return only the first column of the first record returned (since you are only returning one record of one column this is appropriate).

    ' Set up your SQL
    sql3="SELECT Loannum FROM Loandata WHERE Serialnum='" & ViewState("LastKey") & "'"

    ' Create the command object directly from the connection
    objCommand = objConnection.CreateCommand()

    ' Set the sql for your command
    objCommand.CommandText = sql3

    ' retrieve the value from the database
    Dim objReturnedValue as Object = objCommand.ExecuteScalar()


Also at this point I'd probably leave the connection open (as you are about to use it again anyway)



For the second part of the data, you will need to use a DataAdapter to populate a DataTable before you can setup the DataView.

Code starts from after you have generated you sql statement:

    ...

    Dim objDataTable as OleDbDataTable
    Dim objDataAdapter as new OleDbDataAdapter()
    objDataAdapter.SelectCommand = new OleDbCommand(sql, objConnection)
    objDataAdapter.Fill(objDataTable)

    Dim objDataView as New DataView(objDataTable)

    appsgrid.DataSource = objDataView

    ...

Hopefully this will help you out toward getting your problem solved.  Please let me know if you have any problems.

Please note that I have just typed the code above and have not run it through the VS.NET compiler, so there may be a couple of minor typo's :-)

Nick Hoggard

Author

Commented:
The code I have is working as far as pulling my data, and I'm pulling all data based on a criteria, not just one row.  However, my dataview is still not working with the above code.  It says the objDataTable is null.  Any more ideas on that?  Thanks!

Author

Commented:
Actually, first it says the OleDbDataTable is not defined, I have system.data.oledb imported ( and many others ), any others I need for this though?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
worked good, thanks!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.