How to fill a datagrid using a datareader (OleDbDataReader) object

Guess only a dataset/dataview can be used to fillin a datagrid.
Please let me know how can data be filled in to the grid using a filled OleDbDataReader object.
Also i want the data grid to be non editable, but allow the user to copy/double click on the grid rows.

Thanks for your help.
LVL 8
rajaloysiousAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LosBearCommented:
I learned to asp.net by by reading asp.net Unleashed.  They have a reference page with a ton of datagrid examples...maybe this'll help:
http://www.superexpert.com/default.aspx?id=242

Bear
0
pillbug22Commented:
I alway use SQL, but it should all be the same syntax:



   Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If Not IsPostBack Then
                Dim connection As SqlConnection = CreateConnection()
                dgUsers.DataSource = CreateDataSource(connection)
                dgUsers.DataBind()
                connection.Close()
            End If
    End Sub
   


    Private Function CreateConnection() As SqlConnection

        Dim connection As New SqlConnection()
        connection.ConnectionString = ConfigurationSettings.AppSettings("connectionString")
        connection.Open()
        Return connection

    End Function



    Private Function CreateDataSource(ByVal connection As SqlConnection) As SqlDataReader

        Dim strSql As String

        ' Set SQL string
        strSql = "SELECT * FROM table"

        Dim command As New SqlCommand(strSql, connection)
        Return command.ExecuteReader()

    End Function
0
rajaloysiousAuthor Commented:
I am new to datagrid...
Plz...I meant filling the datgrid manually, cell by cell. Is it possible to fill the grid without doing a databind. Also i want to format the numeric columns to show the -ve numbers as red without the -ve sign.

I have never worked with datagrid. The following will be helpful
Can i have multiple row headers(column)
Can I have my own formatting for any column
Can i have the grid readonly and allow double clicks on rows.

I am in the middle of a feasibility study. Please help.

Thanks in advance
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

rajaloysiousAuthor Commented:
i used the method given by pillbug22. But It gives the error -Complex DataBinding accepts as a data source either an IList or an IListSource
difference is that i am forced to use Oledb instead of sqldb as i connect to oracle.
0
pillbug22Commented:
By "multiple row headers(column)", are you wanting multiple columns, or something I'm not understanding?

Yes, you can have independant formatting for any column.  Some formatting you can set at design time (how many decimal places, short/long date, etc.) and some you control at display (conditional formatting like you want: i.e.: show negative numbers in red).  For condistional formatting, you will want to use the myDataGrid_onDataBind() Sub.  That sub is called each time a control within a dataGrid is bound to something (effectively, each cell).

Yes, you can have a grid read-only, and yes, you should be able to set a double-click event for the rows.
0
pillbug22Commented:
Edit:

above, where I said to use the myDataGrid_onDataBind() Sub, it should be myDataGrid_ItemDataBound() Sub
0
LosBearCommented:
Here's a sample that might be useful from the site I posted up above.  It allows you to format each column row, make certain fields links, etc.
Hope it helps.....
Bear


<%@ Import Namespace="System.Data.SqlClient" %>

<Script Runat="Server">

Sub Page_Load
    Dim conMyData As SqlConnection
    Dim cmdSelect As SqlCommand

    conMyData = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=MyData" )
    cmdSelect = New SqlCommand( "Select link_title, link_url From FavLinks", conMyData )
    conMyData.Open()
    dgrdLinks.DataSource = cmdSelect.ExecuteReader()
    dgrdLinks.DataBind()
    conMyData.Close()
End Sub

</Script>

<html>
<head><title>DataGridDataFormatString.aspx</title></head>
<body>
<form Runat="Server">

<asp:DataGrid
  ID="dgrdLinks"
  AutoGenerateColumns="False"
  EnableViewState="False"
  ShowHeader="False"
  CellPadding="10"
  Runat="Server">

<Columns>
  <asp:HyperLinkColumn
    DataNavigateUrlField="link_url"
    DataTextField="link_title" />
</Columns>

</asp:DataGrid>

</form>
</body>
</html>

0
rajaloysiousAuthor Commented:
please let me know how to fill the datgrid manually, cell by cell
is it recommended to do so...
i have the impression of using the datagrid only  to bind it to a datasource...
0
pillbug22Commented:
I guess I'm not completely understanding why you need to fill your dataGrid cell by cell.  Isn't your data coming form a database?

If you want to create your own values for each cell, the easiest way would be to create your own DataTable, then bind the dataTable to the dataGrid.

ex:

Dim dtNames As DataTable = New DataTable()
Dim dr As DataRow
Dim dcId As New DataColumn("id")
Dim dcLinkTitle As New DataColumn("link_title")
Dim dcLinkTitle As New DataColumn("link_urk")
dtNames.Columns.Add(dcId)
dtNames.Columns.Add(dcLinkTitle)
dtNames.Columns.Add(dcLinkUrl)

' Begin by adding a row with values to dataTable
dr = dtNames.NewRow()
dr(0) = "1"                                        ' Id
dr(1) = "Microsoft"                             ' Link Title
dr(2) = "http://www.microsoft.com"    ' Link Url
dtNames.Rows.Add(dr)



Just keep adding rows until you have all your data, then bind your dataGrid to the dataTable.
0
rajaloysiousAuthor Commented:
Data comes from an oracle database. The comment given by pillbug22 (10/27/2003 06:56AM PST ) to bind it to the data reader object does not work.
But It gives the error -Complex DataBinding accepts as a data source either an IList or an IListSource
difference is that i am forced to use Oledb instead of sqldb as i connect to oracle.
If this issue can be solved, i can directly bind the oledbreader to the datagrid. That was the reason ia was asking for a cel by cell manipulation. But this is double work for me to get from a db and then create a datatable and insert records into it...
0
pillbug22Commented:
Can you post the code that gives the error?  (the section where you are attempting to connect to the DB)
0
rajaloysiousAuthor Commented:
As per ur request, i have posted the code--- mind the oracle package works in vb6
        Dim myConnection As OleDbConnection
        myConnection = New OleDbConnection("Provider=MSDAORA; Data Source=MYDB ; User ID=myname; Password=mypwd")
        Dim myOleDbCommand As OleDbCommand = myConnection.CreateCommand()
        myOleDbCommand.CommandType = CommandType.StoredProcedure
        myOleDbCommand.CommandText = "PCK_one.GetDetails"
        myConnection.Open()
        myOleDbCommand.Connection = myConnection
        Dim myReader As System.Data.OleDb.OleDbDataReader = myOleDbCommand.ExecuteReader()

        DataGrid1.DataSource = myReader ********* error occurs here
                      *Complex DataBinding accepts as a data source either an IList or an IListSource*
      PCK_one.GetDetails returns a ref cursor qhich works in VB 6
0
rajaloysiousAuthor Commented:
Your answer will mean a lot to me...
0
pillbug22Commented:
any luck yet?

As a work-around, I would see if you can assign the dataReader values to a dataTable, then bind the dataGrid to the dataTable:

' ********************************************

                Dim dTable As New DataTable()
                Dim dr As DataRow
                Dim dcField1 As New DataColumn("sqlField1")
                Dim dcField2 As New DataColumn("sqlField2")
                Dim dcField3 As New DataColumn("sqlField3")
                untouchedOrdersTable.Columns.Add(dcField1)
                untouchedOrdersTable.Columns.Add(dcField2)
                untouchedOrdersTable.Columns.Add(dcField3)
               

                   While myReader.Read()
                        If IsDBNull(myReader(0)) Then
                            Label1.ForeColor = Color.Black
                            Label1.Text = "No results returned"
                            Label1.Visible = True
                            dataGrid1.Visible = False
                        Else
                            Try
                                dr = dTable.NewRow
                                dr(0) = myReader(0)
                                dr(1) = myReader(1)
                                dr(2) = myReader(2)
                                dTable.Rows.Add(dr)
                            Catch ex As Exception
                                Label1.ForeColor = Color.Red
                                Label1.Text = "Error building data table."
                                Label1.Visible = True
                                dataGrid1.Visible = False
                            End Try
                        End If
                    End While

                    dataGrid1.datasource = dTable

' ********************************************
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rajaloysiousAuthor Commented:
I have used the source code in planetsource.com which converts a oledbdatareader to a datatable and it works for me. Thanks all for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.