none
Dataset rows not matching Access records RRS feed

  • Question

  • I have a typed dataset being filled by a table adapter that is using the same sql string as my MS Access report is using.  However, when ran, the dataset does not contain as many rows as Access contains records.  Can anyone explain why?
    Monday, May 19, 2008 2:27 PM

All replies

  • Hi,

     

    Not really.

     

    Well actually maybe, depends on how your coding it, could be that the query is based on a table that has a relationship to another table. Maybe the records that are missing are records where there is no key values for the relationship. So it's missing out all records where there is no valid status value (as an example) Very difficult to help you here as there is not a lot of information in your post. Could you expand on what the query is how you created the data-adapter and just other information that would help pinpoint the problem.

     

    Monday, May 19, 2008 4:44 PM
  • I sure can.  The database does infact use linked tables for just about everything, however the table with this problem is not linked to anything else.  Personally, I find it a pain in the rear, though it is what I must work with since I'm picking up someone else's project.

    This is how I fill the dataset.

    Code Snippet

    ' Create Connection To Database
            Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & m_Database & "';User Id=admin;Password=;"
            Dim oledbConn As New OleDbConnection(connStr)
            Dim q As String = DefaultQuery()
            Dim oledbAdapter As OleDbDataAdapter = New OleDbDataAdapter(DefaultQuery(), connStr)
            Dim dataSet As DataSet = New MedfordTruckingDataSet()
            Try
                oledbConn.Open()
                System.Diagnostics.Debug.WriteLine("Select command is: " & oledbAdapter.SelectCommand.CommandText)
                oledbAdapter.Fill(dataSet.Tables("TruckInformation"))
            Catch ex As Exception
            Finally
                oledbConn.Close()
            End Try

            System.Diagnostics.Debug.WriteLine("There are " & dataSet.Tables("TruckInformation").Rows.Count & " records.")



    This is the sql being used for the command.

    Code Snippet

    Private Function DefaultQuery() As String
            Return "SELECT tbltruck.strTruckmake AS [Make], tbltruck.[License Number] AS License, tbltruck.numtruckserial AS TruckSerial, tbltruck.numengineserial AS EngineSerial, tbltruck.[Weigth Permit Number] AS WeightPermit, tbltruck.numtrucknumber AS [Number] FROM(tbltruck);"
        End Function



    All the query really does is a Select * on the table.  I am just aliasing the columns names to match that of the dataset table that its being used with.  There are 114 records in the database table.  When this code is ran, the dataset contains only 54 rows.
    Monday, May 19, 2008 5:07 PM
  • Hi,

     

    Ok you need to change something.... the try catch statement is catching an error but not doing anything....

     

            Try
                oledbConn.Open()
                System.Diagnostics.Debug.WriteLine("Select command is: " & oledbAdapter.SelectCommand.CommandText)
                oledbAdapter.Fill(dataSet.Tables("TruckInformation"))
            Catch ex As Exception

                  'if an error happens then you will never know about it!

                  'put something in here
            Finally
                oledbConn.Close()
            End Try

     

    It could be a record, the 55th row, has caused an error during the read of the select statement and everything stops, no error is raised so it looks like everything was fine.

    Monday, May 19, 2008 5:27 PM
  • I placed a

    Code Snippet

    System.Diagnostics.Debug.WriteLine(ex.StackTrace)



    call there and ran the code once more.  Nothing changed in the output.
    Monday, May 19, 2008 5:53 PM
  • I've got no idea. The only other thing that might be wrong is the last bit of the SQL query but if that was the case you would get 0 records. I don't know...

     

    there is something here in the code

    Dim q As String = DefaultQuery()
    Dim oledbAdapter As OleDbDataAdapter = New OleDbDataAdapter(DefaultQuery(), connStr)

    You don't need variable q there as DefaultQuery is called directly in the Adapter constructor.... but thats nothing.

     

    I don't know. If you want, I understand if you can't or won't, send me the project and I'll have a look but your code looks fine... I can't see anything wrong with it.

    Monday, May 19, 2008 9:54 PM
  • I can send you the code, but not the database.  Its a few hundred megs.  The q variable was used to output the query in a diagnostics lines so I could check if thats the problem.  I just forgot to remove it.  Thanks for pointing it out.
    Tuesday, May 20, 2008 12:05 PM
  • In trying to seperate the two tables from the database, I have found that your first thoughts were correct and that I was wrong.  The tables are linked somehow as when exported, they contain the same data that the dataset does.  However, when I open the tables in Access, I get more data, but no sign of them being linked (the table within a table look).  How do I get teh data from a linked table?  My SQL is not all that great, but I believe you must always specify each table you are aquiring from.  When they are linked, I do not know where the rest of the data resides.
    Tuesday, May 20, 2008 12:26 PM
  • Ok, here is what I did.

    At first, I tried to export from the base tables, which did not work as stated above.  However, when I exported from the linked tables, I successfully captured all records.  I exported all linked tables and then imported them into a new database.  I will just make use of this new database and throw out the old, provided the client is ok with this action.

    Its completely ok if you do not know, but just out of curiousity, how do linked tables work?  I understand data is taken from a "link" table and merged with new information to create another table.  Does the just "link" act as a shallow copy appended to the new data? 
    Tuesday, May 20, 2008 1:15 PM
  • Hi Stuby085,

     

    Linked tables might not be the best way to describe them, related tables is better. In Access linked tables are tables that are stored in one table but accessed from another as though both databases where linked. Related tables are different, related tables are tables where data in one table is related to data in another table. Give you an example, company and employees. A company has more than one employee working for them while one employee works for a single company (normally). In a database you would have a company table and an employee table and they would be related based on an key (a primary key in company (parent) and a foreign key in the employee table (child)).

     

    So a link table is a table that lives in one database but is accessible through another database. The two databases are linked together.


    A related table is a table whos data is related to data stored in another table.

     

    The reason you have related tables is it reduces the amount of data you need to store. And your right it lets you create a query that basically uses the relationship to create views of the data.... like... all employees who work for a company. The information looks like a new table but it's just a view, nothing new is created.

     

    (Hopefully I haven't just told you stuff you already know)

     

    What I originally thought the problem was is this..... the dataset your filling contains the tables and the relationship between the tables. What I thought might happen is if you get the data for one of the tables some of the rows might need information stored in the other related tables.  For example in order to get a list of employees you need to get the company first.

     

    I don't know if this is the case though looking at your code. What might be happening is for 54 of the trucks there is no related data between the downloaded rows in the truck table and other rows in the related tables. The other truck rows do have related data and in fact the truck table is a child of other related table. So when the truck table is filled a check is down and if no related data exists then that row isn't stored.

     

    Does that make sense? Thats what I originally thought. There is an easy way to check. You can set EnforceConstraints to let this data go. If this is the problem then the table should fill completely and then an error is thrown once EnforceConstraints is set to true. Give it a go and see what happens...

     

     

    ' Create Connection To Database
            Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & m_Database & "';User Id=admin;Password=;"
            Dim oledbConn As New OleDbConnection(connStr)
            Dim q As String = DefaultQuery()
            Dim oledbAdapter As OleDbDataAdapter = New OleDbDataAdapter(DefaultQuery(), connStr)
            Dim dataSet As DataSet = New MedfordTruckingDataSet()
            Try
                oledbConn.Open()
                dataSet.EnforceConstraints = False
                oledbAdapter.Fill(dataSet.Tables("TruckInformation"))

                System.Diagnostics.Debug.WriteLine("There are " & dataSet.Tables("TruckInformation").Rows.Count & " records.")

                 dataSet.EnforceConstraints = True

                System.Diagnostics.Debug.WriteLine("There are " & dataSet.Tables("TruckInformation").Rows.Count & " records.")
            Catch ex As Exception
            Finally
                oledbConn.Close()
            End Try

            System.Diagnostics.Debug.WriteLine("There are " & dataSet.Tables("TruckInformation").Rows.Count & " records.")

    Tuesday, May 20, 2008 4:03 PM