Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1219
  • Last Modified:

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.
0
rajaloysious
Asked:
rajaloysious
  • 7
  • 6
  • 2
2 Solutions
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now