Solved

using the oleDb data reader

Posted on 2004-04-28
21
876 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 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

11 Experts available now in Live!

Get 1:1 Help Now