Solved

using the oleDb data reader

Posted on 2004-04-28
21
875 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

23 Experts available now in Live!

Get 1:1 Help Now