none
TableAdapter Not Finding Row

    Question

  • I am developing a Windows application using SQL, and C#. I am populating a data grid using a TableAdapter. There are several queries written in the TableAdapter. One to find an individual row using the primary key. A couple to find a subset of rows using the value of another field. Eg. find all the rows with the same first five digits in an SSN.

    I realized a couple of months ago that I was not retrieving all of the rows. (Using the Management Studio, I find 9 rows with a particular five digit value. Using the application, I find 4 rows.) I thought it was the TableAdapter timeout value. I found a method using reflection to change the timeout. And as far as I can tell it is changing the value correctly.

    public static void ChangeTimeout(Component component, int timeout)
    {
        if (!component.GetType().Name.Contains("TableAdapter")) { return; }
    
        PropertyInfo adapterProp = component.GetType().GetProperty("CommandCollection", BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance);
        if (adapterProp == null) { return; }
    
        SqlCommand[] command = adapterProp.GetValue(component, null) as SqlCommand[];
        if (command == null) { return; }
    
        for (int I = 0; I < command.Length; I++) { command[I].CommandTimeout = timeout; }
    }

    Someone suggested that I needed to index the columns used in the searches. So, I added appropriate indexes. (Eg. an index on the SSN column.) It seemed to solve the problem. But now I am noticing the same issue.

    As an experiment, I used the Management Studio, and the TableAdapter Preview to search for particular PK values. The table has 470K records. Using the Preview, I can't retrieve a row beyond 467K.

    My division doesn't have a DBA -- there may be one in the greater organization -- so if this involves some type of index maintenance, I may not realize it. And being fairly new to C#, I realize there may be something amiss with my implementation of the TableAdapter. I have another method that I can use to populate the DataGrid, but I use the PK search to populate another form for data maintenance. To use my alternate method would require more changes to the maintenance form than desirable. So, I would appreciate any type of nudge.

    Tuesday, April 18, 2017 6:35 PM

Answers

  • Yesterday, not making any headway on this problem, I began to look at something else. I have a utility which looks at the same database, AND uses the same logic ... and that WORKED!! (A program that performs one function, looking at the same database, same tables, accessing the data with a tableadapter, using the same logic ... but different results.)

    Ergo, there is some other problem ... and something probably pretty dumb too. Too make a long story short, I looked under every rock. Don't know how it happened, but in the Project properties settings I found two connection strings set. One to the correct database; one to an old copy of the correct database, but of course missing a lot of data. I had to recreate the dataset, but I am now retrieving all the rows.

    Thank you for spending a lot of time looking at this for me.

    Thank you for a couple of new ways at addressing row selection.

    And, one more time for good measure, thank you.

    • Marked as answer by wodders Friday, April 21, 2017 1:05 PM
    Friday, April 21, 2017 1:05 PM

All replies

  • I wonder if you could try overriding the query as explained here (and think of this as conceptual because you would not be dynamically changing the where condition as I show in the code sample).

    https://code.msdn.microsoft.com/TableAdapter-WHERE-IN-456d8b74?redir=0


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, April 18, 2017 9:23 PM
    Moderator
  • I have been looking at your WHERE IN suggestion ... attempting to understand it, and see how it would fit with my application. I think it might save some time if I just ask the question ...

    Pretend I am trying to find all the rows in my table that have an SSN that begins with "391". I have created a query in the tableadapter that has this code.

    select * from table where substring(SSN,1,@N) = @R 

    N is the number of characters in "391", and R is "391". This select statement does work. But it seems to read only so far down the table. I know from using other methods that it does not find all the records.

    As an alternative, I have created the command and used a sqldatareader, which also works, AND gets all the records. It is cumbersome populating a form with it though.

    Would this WHERE IN fit in this situation?


    • Edited by wodders Wednesday, April 19, 2017 7:27 PM
    Wednesday, April 19, 2017 7:26 PM
  • What I was eluding to was in the code sample you can alter the behavior of the pre built query.

    Let's take another approach, given this sample table (and understand the following works but may need so alterations but know the concept is passing in the data to look for).

    I create a query as follows

    Usage in a form using a text box for testing.

    Of course you can write more logic around all of the above to take in x numbers via the pattern in this case in the text box.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, April 20, 2017 12:38 AM
    Moderator
  • I thought it was solved. The user has a choice to search on two fields: SSN and de_case. Both are varchar, 10 digits. Both are "numbers". I used the same WHERE expression.

    where cast(ssn as varchar(10)) like @R

    where cast(de_case as varchar(10)) like @R

    Both return rows. But the search -- using a query from the tableadapter -- does not seem to scan all the way to the bottom of the table. Passing the same search to a SQL data reader, or using the management studio, all the rows with the appropriate criteria are returned.

    (de_case is a location code appended to a sequence number.  "70000001", "70000002", "70000003", etc. I know by searching with the Management Studio, that there 400 some rows with de_case values beginning with "71059". The tableadapter does not return any. If I search for "7105", it will only returns rows as far as "71054".)

    Thursday, April 20, 2017 2:29 PM
  • Unfortunately I can not look at this until after work, about nine hours.

    What I can tell you is that the Fill method uses a standard Adapter which populates a DataTable so I can imagine that is the issue e.g.

    public virtual int Fill(DataSet1.CustomerDataTable dataTable) {
        this.Adapter.SelectCommand = this.CommandCollection[0];
        if ((this.ClearBeforeFill == true)) {
            dataTable.Clear();
        }
        int returnValue = this.Adapter.Fill(dataTable);
        return returnValue;
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, April 20, 2017 2:54 PM
    Moderator
  • Hello,

    I populated the table with record values from '70000001' to '73245641' 

    In SQL-Server Management Studio run

    DECLARE @VALUE NVARCHAR(10)
    SET @VALUE = '71059%'
    
    SELECT  id,SocialSecurity
      FROM SocialSecurityTable
      WHERE CAST(SocialSecurity AS NVARCHAR(10)) LIKE  @VALUE

    Outcome was 7804 rows, in code, same record count. Ran a few other LIKE queries in both Mangement Studio and in code, got the same results as expected. So from my perspective there is no issues when I run code yet sounds like you are.

    Output from IntelliTrace


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Thursday, April 20, 2017 11:34 PM
    Moderator
  • Yesterday, not making any headway on this problem, I began to look at something else. I have a utility which looks at the same database, AND uses the same logic ... and that WORKED!! (A program that performs one function, looking at the same database, same tables, accessing the data with a tableadapter, using the same logic ... but different results.)

    Ergo, there is some other problem ... and something probably pretty dumb too. Too make a long story short, I looked under every rock. Don't know how it happened, but in the Project properties settings I found two connection strings set. One to the correct database; one to an old copy of the correct database, but of course missing a lot of data. I had to recreate the dataset, but I am now retrieving all the rows.

    Thank you for spending a lot of time looking at this for me.

    Thank you for a couple of new ways at addressing row selection.

    And, one more time for good measure, thank you.

    • Marked as answer by wodders Friday, April 21, 2017 1:05 PM
    Friday, April 21, 2017 1:05 PM