Share This!

Tuesday, December 11, 2012

SQL Server Matching on NULL parameter

So I have this query where I am trying to select on customer, unless the user doesn't enter a parameter for customer.  When they leave it blank, we want to see all customers.

So normally I would do it like this:

Select * from Orders where Customer=@customer And then to handle the null parameter I would change it like this:

Select * from Orders where ((Customer = @customer) OR (@customer is null))  This works great but then I came across this way of making it simpler.

Select * from Orders where Customer = isnull(@customer, Customer) The isnull()  effectively handles the case where the parameter (@customer) is null by replacing it with the content of the [Customer] data column, matching to itself!  Problem solved!


...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Tuesday, December 4, 2012

Operation is not valid due to the current state of the object.

App Details:

Visual Studio 2010
Desktop Winforms App
MDI Child form with a Datagridview connected to SQL Server 2008

Error details:
When closing the form, debugger jumps to this line in form.designer.cs (one of the files you are not supposed to edit).

/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
    if (disposing && (components != null))
    {
        components.Dispose();  //<<<---error points here
    }
    base.Dispose(disposing);
}
and here is the massive error...

System.InvalidOperationException was unhandled
  Message=Operation is not valid due to the current state of the object.
  Source=System.Windows.Forms
  StackTrace:
       at System.Windows.Forms.DataGridViewCell.GetInheritedStyle(DataGridViewCellStyle inheritedCellStyle, Int32 rowIndex, Boolean includeColors)
       at System.Windows.Forms.DataGridViewCell.GetPreferredWidth(Int32 rowIndex, Int32 height)
       at System.Windows.Forms.DataGridViewColumn.GetPreferredWidth(DataGridViewAutoSizeColumnMode autoSizeColumnMode, Boolean fixedHeight)
       at System.Windows.Forms.DataGridView.AutoResizeColumnInternal(Int32 columnIndex, DataGridViewAutoSizeColumnCriteriaInternal autoSizeColumnCriteriaInternal, Boolean fixedHeight)
       at System.Windows.Forms.DataGridView.OnColumnGlobalAutoSize(Int32 columnIndex)
       at System.Windows.Forms.DataGridView.OnColumnCommonChange(Int32 columnIndex)
       at System.Windows.Forms.DataGridViewCell.OnCommonChange()
       at System.Windows.Forms.DataGridViewComboBoxCell.set_DataSource(Object value)
       at System.Windows.Forms.DataGridViewComboBoxCell.DataSource_Disposed(Object sender, EventArgs e)
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.ComponentModel.Component.Dispose(Boolean disposing)
       at System.Windows.Forms.BindingSource.Dispose(Boolean disposing)
       at System.ComponentModel.Component.Dispose()
       at System.ComponentModel.Container.Dispose(Boolean disposing)
       at System.ComponentModel.Container.Dispose()
       at Onesource.MaterialReceiptsEntry.Dispose(Boolean disposing) in P:\Projects\myproject\sample\MaterialReceiptsEntry.Designer.cs:line 18
       at System.Windows.Forms.Form.WmClose(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DefMDIChildProc(IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at System.Windows.Forms.Form.DefWndProc(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmSysCommand(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DefMDIChildProc(IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at System.Windows.Forms.Form.DefWndProc(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ContainerControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmNcButtonDown(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Onesource.Program.Main() in P:\Projects\myproject\sample\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:
The solution:
I was looking through the ugly error stack above and found a lot of references to stuff like GetPreferredWidth. Having previously dealt with numerous problems involving AutoSizeColumnsMode and DataGridViewComboboxColumns,   I decided to try testing the form with AutoSizeColumnsMode set to None.  The layout did suck, but the form stopped crashing...

This means that the datagridview is trying to resize its columns during a form close!  Here is what I did to solve the problem.

I set the AutoSizeColumnsMode to AllCells and added a FormClose event as follows...




private void MaterialReceiptsEntry_FormClosing(object sender, FormClosingEventArgs e)

{

myDataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;

}

This ensures that the grid will not be updating its column sizes as the form is closing.



...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Contact Us

Name

Email *

Message *