Solved

using the oleDb data reader

Posted on 2004-04-28
21
878 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
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

789 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