none
Weird Issue RRS feed

  • Question

  • Hi all,

    I have an issue with a WinForms/Ado.Net/Dataset application I am maintaining.

    Occasionally I find that rows are written to SQL Server that contains no details (PK is auto-indexing integer/ all other fields are null).

    I find this very strange because to get to where the table adapter calls the update mthod; the code must pass through a validation routine that checks that mandatory fields in the table have valid values. Not just that, but any new records created have certain fields set to initial values in the code.

    I am totally stamped how these records are being inserted in the database with their values all set to null.

    Does anybody have a clue what may be going on?

    Tuesday, March 20, 2012 1:17 PM

Answers

  • I have changed the schema to make the fields not-nullable and I am waiting for an exception. I wil post here if and when I find something.

    Thanks.

    • Marked as answer by MarinosC Monday, June 25, 2012 10:51 AM
    Monday, March 26, 2012 5:15 AM

All replies

  • You're probably missing an .EndEdit() on your BindingSource, or maybe not doing this.Validate(). Neither of these are always necessary, but every once in awhile something out of the ordinary causes a control to not lose focus and the data that is bound to it is left in the Proposed row state (which the .EndEdit() will force into the Changed state).

    That's just a guess ... maybe you can post a little code ...


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, March 20, 2012 3:17 PM
  • You could constrain your database table design to require the fields in question.  If nothing else, by doing so it may force an exception in order to help direct your troubleshooting efforts.

    Brent Spaulding | Access MVP

    Tuesday, March 20, 2012 8:02 PM
  • Bonnie thanks,

    I do call endedit.

    The Pseudo code when save is as follows:

    .EndEdit()

    If ValidData Then

      .Update(Table)

    End If

    Also if creating a new record I call a procedure which sets many fields to default values.

    THis is why I am surprised that I am getting records wich just have null values.

    Wednesday, March 21, 2012 6:31 AM
  • If the only time that you update the database is after it has passed the ValidData check, then obviously there's a hole in your ValidData method somewhere. Finding it may be difficult ... it's probably NOT happening on a new record (since you stuff default values into it, doing a DataRow.EndEdit() on the newly added row I assume ... if not, you should be and maybe that's the problem). 

    Does this seem to happen on a regular basis or is it unpredicatable? If you know what behavior causes the bad data to be saved to the database, you could use SQL Profiler to see what is being sent to the database just before, during and after the problem row. It might help pinpoint the problem.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Wednesday, March 21, 2012 2:03 PM
  • Thanks again for replying.

    I have the application located at about forty different locations and have now only noticed this issue at two so far.

    It is definitely unpredictable.

    The idea to Use the SQL Profiler is a interesting, to be honest I never thought about.


    • Edited by MarinosC Thursday, March 22, 2012 2:25 PM
    Wednesday, March 21, 2012 2:19 PM
  • Does each location have its own SQL Server installed locally or do you have a central server at the "home office" that each location hits? If you only have the one central server, then using SQL Profiler might be a bit tough (unless each location has its own database on that server).

    Keep us posted on your progress (or lack of it ... LOL)!


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Wednesday, March 21, 2012 3:39 PM
  • Tracking and troubleshooting is why I suggest to set the columns to required (NOT NULL) at the database level.  You could then get an exception if your application is trying to write the blank data.  I hilite the 'your application' piece because it almost sounds like some other app is connecting to your data source and adding those blank records.  If that is the case, then that would support the idea that your table constraints be applied at the database level while maintaining the constraints at the application level.

    Brent Spaulding | Access MVP

    Wednesday, March 21, 2012 4:03 PM
  • I am going to change the table columns to not allow nulls at the problem site and keep you posted.

    Thursday, March 22, 2012 2:24 PM
  • Hi MarinosC,

    Have you solved the issue?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, March 26, 2012 2:11 AM
    Moderator
  • I have changed the schema to make the fields not-nullable and I am waiting for an exception. I wil post here if and when I find something.

    Thanks.

    • Marked as answer by MarinosC Monday, June 25, 2012 10:51 AM
    Monday, March 26, 2012 5:15 AM