Solved

using the oleDb data reader

Posted on 2004-04-28
21
879 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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