Solved

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

Posted on 2003-10-27
15
1,121 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now