Solved

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

Posted on 2003-10-27
15
1,143 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:rajaloysious
  • 7
  • 6
  • 2
15 Comments
 
LVL 1

Expert Comment

by:LosBear
ID: 9626985
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
 
LVL 6

Expert Comment

by:pillbug22
ID: 9627212
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
 
LVL 8

Author Comment

by:rajaloysious
ID: 9631637
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 8

Author Comment

by:rajaloysious
ID: 9631733
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
 
LVL 6

Expert Comment

by:pillbug22
ID: 9633631
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
 
LVL 6

Expert Comment

by:pillbug22
ID: 9633637
Edit:

above, where I said to use the myDataGrid_onDataBind() Sub, it should be myDataGrid_ItemDataBound() Sub
0
 
LVL 1

Assisted Solution

by:LosBear
LosBear earned 200 total points
ID: 9633854
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
 
LVL 8

Author Comment

by:rajaloysious
ID: 9655509
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
 
LVL 6

Expert Comment

by:pillbug22
ID: 9657476
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
 
LVL 8

Author Comment

by:rajaloysious
ID: 9662057
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
 
LVL 6

Expert Comment

by:pillbug22
ID: 9670716
Can you post the code that gives the error?  (the section where you are attempting to connect to the DB)
0
 
LVL 8

Author Comment

by:rajaloysious
ID: 9678274
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
 
LVL 8

Author Comment

by:rajaloysious
ID: 9678279
Your answer will mean a lot to me...
0
 
LVL 6

Accepted Solution

by:
pillbug22 earned 300 total points
ID: 9723405
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
 
LVL 8

Author Comment

by:rajaloysious
ID: 9728498
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question