Updateable Result Pane Part II — How do we deal with data in the updateble result pane (By Ming Chen)

In this version updatable result pane, since we rewrite it from ground up.  Beside on the UI fanciness, there are some rule on how do we deal with data in updateable result pane

  1. Minimum data for update/delete.

For example, if user only change 2 columns data in a 12 columns rows, only those two data that’s changed will be in the Set clause of the update statement.   The update commit is base on row commit.  If you change multiple columns in a single row,  you can always come back and hit <escape> to revert your change back to original data.

  1. Optimistic Concurrency Control:

Take the previous example, all 12 columns (note: plus primary keys) of the original data will be put in to the where clause.  The note on the primary keys is that in case of  the primary keys is not in the projection list, we will try to include the primary keys columns in addition to user specified columns.

If this update/delete come back as 0 update, we assume the data in the database has been changed since the last retrieval.  We will offer a dialog to allow user to update only base on the primary keys, or allow user to see what’s the data changed first by retrieve the data back base on primary keys.

The caveats are

    • Some column types are not allow to put into the Where clause.  For example binary data type, xml data type. -- they are degrade to only compare to NULL or non-NULL
    • The data that we failed to read cannot be put into the where clause.
    • If underline data on the database was deleted or the primary keys was updated, then even use the primary keys to update/delete/retrieve, it will obviously failed.  If that’s what happening, the row will then marked as read only.
  1. Clear distinguish Empty String, NULL and “NULL” string

NULL will be represent as NULL

If you need to input a “NULL” string, you need to type in ‘NULL’ (6 characters including single quotes.)  If the data from the database is “NULL” string (4 characters) then it will display as ‘NULL’.  That is, for example,  for a char(10) column if you type in ‘NULL’ data to update the row.  The “NULL” string will be sent to database. However, after successful updated, the data retrieve back will be “NULL       “ (NULL string with 6 padding space). IT will be displayed as NULL    (with 6 space padding)

You might ask that if I really want to put a ‘NULL’ (with quote) into database, well, you are out of luck.  We don’t want to introduce the whole set of escape characters that user have to learn.  Beside, the current implementation, the UI is pretty consistent (so copy & paste will work as consistent as possible – what you see from retrieved data from the database, you can input the same way to the updatable result set.)


There are some casualties of the above rules:

  • For new insertion rows – How do you make the NULL into the Values clause?

Well, the original data is nothing—that is why it original show up as NULL.  If you don’t touch anything, well, according to above Rule 1.  Nothing will be put into the Values clause of the insert statement.  So, you might ask, what if you really want to insert a data which happen to be NULL.  Well, you have to type it in literally. (So we can tell it is not original NULL or cause by user hitting the <escape> key in the cell).  Tip: you can also use Ctrl-0 to insert a NULL

You can also tell which NULL we will put in the Values clause – the NULL with a dirty pencil on that cell.  The only exception will be if you are currently editing the cell – the textbox will be on top of the whole cell – the dirty pencil is there, but it is under the textbox – you can’t see it.


Next subject:  When execute parameterized Query and execute it, if I input NULL in that dialog, why the result pane doesn’t show any record?

Comments (4)

  1. Bill Burrows [MVP] says:


    I see Part I (7/14) in these blogs but I do not see Part II. Am I blind <g>?


  2. Ming Chen<VSData> says:

    Bill, thanks for pointing that out. I thought no one will notice. I had fix the subject. 🙂

    (I must be thinking another subject but never get around to post it… But I forget what it was about now….)

  3. Bob Archer says:

    If I want to set a field to null, as in unknown then why wouldn’t I use CTRL-0 like SQL and FoxPro support?

    What is a null string? Do you mean an empty string or zero length string? Why wouldn’t I type in ” in that case? Seems you are making this null stuff to hard.


  4. Ming Chen<VSData> says:

    > why wouldn’t I use CTRL-0 like SQL and FoxPro support?

    I did mention that in the blog.

    >> Tip: you can also use Ctrl-0 to insert a NULL

    > What is a null string?

    If we take NULL as a representation of DBNULL. User will begin to ask — if the data is really string NULL. How does it appear in the screen? How do he/she input a string NULL?

    Empty string(”) is not in the argument here since it is obviously different look(empty cell) than NULL.

    Remember, Result pane is not only used to connect to SQL but also to ORACLE, ACCESS, FoxPro and any database you can think of which has OLEDB or ODBC provider. Most databases treat empty string and DBNULL very differently internally. There need to have clear distinguish between empty string and DBNull.

    Visual Studio is just a tool — We should not block user to do what they want to do to their database in most common secnario.(Empty string and DBNull is very common data for any database.)

Skip to main content