?
Solved

using the oleDb data reader

Posted on 2004-04-28
21
Medium Priority
?
881 Views
Last Modified: 2010-05-18
Hello, i want to use the oleDb data reader and i am not sure how,  i need to read through the row of data in the Table name called Timecards and pick out the rows when the value in column one matches a value the user types in a textbox then bind the datatable to a datagrid. any ideas i was thinking of something like i have below but it doesnt work, i know i am missing stuff the actual table Timecards has 12 columns and i need to compare txtMaintnum with column 1 if the match add row.

Dim dt As DataTable
        Dim dr As DataRow
        Dim dataread2 As OleDbDataReader
        Dim objDataAdapter As New OleDbDataAdapter("SELECT * FROM TimeCards", strConn)
        objDataAdapter.Fill(objDataset)

 
        dt = New DataTable
        dt.Columns.Add(New DataColumn("Date", GetType(Date)))
        dt.Columns.Add(New DataColumn("colname", GetType(String)))


                 dataread2 = objDataset.executereader()
            While dataread2.Read()
                dr = dt.NewRow()
                dr(0) = dataread2.GetDateTime(0)
                dr(1) = dataread2.GetString(1)
                dr(2) = dataread2.GetInt32(2)
   ' somehow check the row if it matches add
                dt.Rows.Add(dr)
            End While
0
Comment
Question by:tentavarious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 3
  • +1
21 Comments
 
LVL 10

Expert Comment

by:GoodJun
ID: 10942364
use the dataview and bind the dataview to your control.
Dim dt As DataTable
        Dim dr As DataRow
        Dim dataread2 As OleDbDataReader
        Dim objDataAdapter As New OleDbDataAdapter("SELECT * FROM TimeCards", strConn)
        objDataAdapter.Fill(objDataset)

 
        dim dv as dataview
        dv=objdataset.tables(0).select( col1='userTypedValue') 'col1 is the name of column 1
 yourcontrol.datasource=dv
0
 

Author Comment

by:tentavarious
ID: 10942596
That will work to select out all the rows that dont match with the user input from the textbox?  I thought i would have to do something like below and use a oleDb datareader. I will give your example a try.

 Dim dt As DataTable
        Dim dr As DataRow
        Dim dataread2 As OleDbDataReader
        Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\prodsvr\tool\tool128\mainttimecards.mdb")
        Dim objcom As New OleDbCommand("SELECT * FROM TimeCards", objConn)
       
 
        objConn.Open()
        dataread2 = objcom.ExecuteReader()

            While dataread2.Read()
            dr = dt.NewRow()
            dr(0) = dataread2.GetString(0)
                dt.Rows.Add(dr)
            End While


        objConn.Close()
        dgMaint.DataSource = dt.DefaultView
        dgMaint.DataBind()
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 10942624
I would use instead of
dr(0) = dataread2.GetDateTime(0)
dr(1) = dataread2.GetString(1)

dr(0) = dataread2.("FIeld with Time")
dr(1) = dataread2.("FIleld name where the string")


0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 10

Expert Comment

by:GoodJun
ID: 10942693
Sorry for a mistake. the dataview use rowfilter, not the select (which is for the table.rows)
Here is an example:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadataviewclassrowfiltertopic.asp
0
 

Author Comment

by:tentavarious
ID: 10942697
Will this code below work to select and display only the rows if Maint_Num matches the value in txtSearch.text, if it does work then how come it doesnt like my column name.  this looks like the easiest way to do it, if i could get it to accept the column name. any ideas?
Dim strConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\prodsvr\tool\tool128\mainttimecards.mdb")
    Dim objDataset As New DataSet
 Dim objDataAdapter As New OleDbDataAdapter("SELECT * FROM TimeCards", strConn)
        objDataAdapter.Fill(objDataset)
        Dim dv As DataView
        dv = objDataset.Tables(0).Select(Maint_Num = txtSearch.Text) 'col1 is the name of column 1
        dgMaint.DataSource = dv
        dgMaint.DataBind()
0
 
LVL 10

Expert Comment

by:GoodJun
ID: 10942795
'This example returns Datarow() collection, it use the datable.select
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatatableclassselecttopic2.asp

'my last comments, the rowfilter returns dataview.

They both should work.

Dim drs() As DataRow
        drs = objDataset.Tables(0).Select("Maint_Num = '" & txtSearch.Text & "'") 'col1 is the name of column 1
0
 

Author Comment

by:tentavarious
ID: 10942898
This is what i tried and i got a error saying objDataset.table not set to an instance of an object
Dim strConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\prodsvr\tool\tool128\mainttimecards.mdb")
        Dim objDataset As New DataSet
        Dim objDataAdapter As New OleDbDataAdapter("SELECT * FROM TimeCards", strConn)
        objDataAdapter.Fill(objDataset)
        Dim dv As DataView
        With dv
            .Table = objDataset.Tables("TimeCards")
            .AllowDelete = True
            .AllowEdit = True
            .AllowNew = True
            .RowFilter = "Maint_Num = txtSearch.text"
            .RowStateFilter = DataViewRowState.ModifiedCurrent
            '.Sort = "CompanyName DESC"
        End With

        dgMaint.DataSource = dv
        dgMaint.DataBind()
0
 

Author Comment

by:tentavarious
ID: 10942988
I am getting close i dont receive any errors, but my datagrid columns all they say is has errors how do i bind the row to the datagrid do i need to use the data reader. this is what i did

  Dim objDataAdapter As New OleDbDataAdapter("SELECT * FROM TimeCards", strConn)
        objDataAdapter.Fill(objDataset)
        Dim drs() As DataRow
        drs = objDataset.Tables(0).Select("Maint_Num = '" & txtSearch.Text & "'") 'col1 is the name of column 1

    what is the datasource  do i add a new datatable using the drs rows?
        dgMaint.DataSource =
        dgMaint.DataBind()
0
 
LVL 10

Expert Comment

by:EBatista
ID: 10943029
     dgMaint.DataSource = objDataset
        dgMaint.DataBind()
0
 
LVL 10

Expert Comment

by:EBatista
ID: 10943048
also in your previous post the only thing you missed was the new keyword in the dataview declaration:
Dim dv As New DataView

regards
0
 

Author Comment

by:tentavarious
ID: 10943110
if i use objDataset as the source it  shows the whole table i only want the selected rows to show up. So i wonder what i am doing wrong.
0
 
LVL 10

Expert Comment

by:GoodJun
ID: 10943208
The method use dataview, as Ebatista said, just dim dv as new dataview, it shall work.

The method use datarow().
dgMaint.DataSource = drs
        dgMaint.DataBind()
0
 

Author Comment

by:tentavarious
ID: 10943300
i am a little lost i tried these two ways and neither worked  the first method i dont recieve any errors, but my data grid header s contain the text header error and the rows in the first columns say has errors and the second columns says false, the second method i get an error saying can't automatically create data so i am forgetting something.

' first method tried  doesnt work
 Dim drs() As DataRow
        drs = objDataset.Tables(0).Select("Maint_Num = '" & txtSearch.Text & "'") 'col1 is the name of column 1
dgMaint.DataSource = drs
        dgMaint.DataBind()

'second method tried doesnt work

    Dim objDataAdapter As New OleDbDataAdapter("SELECT * FROM TimeCards", strConn)
        objDataAdapter.Fill(objDataset)
        Dim drs() As DataRow
        Dim dv As New DataView
        With dv
            .Table = objDataset.Tables("TimeCards")
            .AllowDelete = True
            .AllowEdit = True
            .AllowNew = True
            .RowFilter = "Maint_Num = txtSearch.text"
            .RowStateFilter = DataViewRowState.ModifiedCurrent
            '.Sort = "CompanyName DESC"
        End With
        dgMaint.DataSource = dv
        dgMaint.DataBind()
0
 
LVL 10

Accepted Solution

by:
GoodJun earned 2000 total points
ID: 10943379
'try this one, notice the filter expression  
      With dv
            .Table = objDataset.Tables("TimeCards")
            .RowFilter = "Maint_Num = '" & txtSearch.Text & "'"
        End With
 dgMaint.DataSource = dv
        dgMaint.DataBind()
0
 
LVL 10

Expert Comment

by:EBatista
ID: 10943435
do you set the AutoGenerateColumns property to False in the datagrid?
0
 

Author Comment

by:tentavarious
ID: 10943543
I made the changes and still nothing if i set my autogenerate to false i get nothing otherwise i get this error
DataGrid with id 'dgMaint' could not automatically generate any columns from the selected data source.
0
 
LVL 10

Expert Comment

by:GoodJun
ID: 10943699
What's the data type of Maint_Num? my filter string assume it is a varchar. if it is a number, change the filter string like this:
   .RowFilter = "Maint_Num = " & txtSearch.Text

set the autogenerate to true
0
 

Author Comment

by:tentavarious
ID: 10943777
i have the Maint_Num column set up as text, should i be using a datareader along with the dataview here is my complete code handled by the button click maybe i am forgetting something
 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       
        Dim strConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\prodsvr\tool\tool128\mainttimecards.mdb")
        Dim objDataAdapter As New OleDbDataAdapter("SELECT * FROM TimeCards", strConn)
        objDataAdapter.Fill(objDataset)

        Dim dattab As New DataTable
        Dim dv As New DataView
        With dv
            .Table = objDataset.Tables("TimeCards")
            .AllowDelete = True
            .AllowEdit = True
            .AllowNew = True
            .RowFilter = "Maint_Num = '" & txtSearch.Text & "'"
             .RowStateFilter = DataViewRowState.ModifiedCurrent
           ' .Sort = "Dept_Num"
        End With

        dgMaint.DataSource = dv
        dgMaint.DataBind()
        End Sub
0
 

Author Comment

by:tentavarious
ID: 10943778
i have the Maint_Num column set up as text, should i be using a datareader along with the dataview here is my complete code handled by the button click maybe i am forgetting something
 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       
        Dim strConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\prodsvr\tool\tool128\mainttimecards.mdb")
        Dim objDataAdapter As New OleDbDataAdapter("SELECT * FROM TimeCards", strConn)
        objDataAdapter.Fill(objDataset)

        Dim dattab As New DataTable
        Dim dv As New DataView
        With dv
            .Table = objDataset.Tables("TimeCards")
            .AllowDelete = True
            .AllowEdit = True
            .AllowNew = True
            .RowFilter = "Maint_Num = '" & txtSearch.Text & "'"
             .RowStateFilter = DataViewRowState.ModifiedCurrent
           ' .Sort = "Dept_Num"
        End With

        dgMaint.DataSource = dv
        dgMaint.DataBind()
        End Sub
0
 

Author Comment

by:tentavarious
ID: 10943809
Does it matter then there are more then one rows that may have the matching number that the user types in? Other then that i am lost.
0
 

Author Comment

by:tentavarious
ID: 10944145
thanks for all your help i new i forgot something   objDataAdapter.Fill(objDataset, "TimeCards") that's what i forgot also
i had to comment out everything in the dv except for the rowfilter and table. thanks again

0

Featured Post

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

762 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