Pages

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.

Thursday, November 29, 2012

Case Schizophrenia and the DataGridViewComboboxColumn

So by default SQL Server (Microsoft) creates indices* and  does searches that are case insensitive.

Select * from orders where salesrep='bob' Will find BOB, bob, bOb, etc. (but not Robert)

So I have a DataGridViewComboboxColumn for that field, but since the data all comes from Paradox, where anything goes, I have a mixture of case scenarios.

As it turns out, even though your database cares not what capitalization you use, the combobox does.  It WILL NOT MATCH a field that has an alternate capitalization of whats in your data.

Example: if you have DONNA in the lookup list, and Donna in the orders table, the dropdown will display SOME RANDOM OTHER NAME.

It also throws a dataerror that looks like...

Order Entry [OrdersGrid]: Row 2 Column 16 Context Formatting, PreferredSize... System.Windows.Forms.DataGridViewDataErrorEventArgs DATA: TONYA 

...and it throws this error for each and every row you attempt to display on the screen.

The solution?

All I did was load the Purchase Orders table using the salesman as its own lookup.  like this

update Purchase_Order set Salesman=(select Salesman from Salesman S where S.Salesman=Purchase_Order.Salesman)

So, we're looking up the salesman, say "Bob" in the Salesman lookup table (finding, say "BOB") and writing that back over the Bob in the row, essentially updating the names in such a way as to match exactly what's in the lookup table.

Now our DataGridViewComboboxColumns work, and we didn't even have to edit the program!




...
* I still can't make myself use the word indexes.

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.

Saturday, November 17, 2012

Easy Tabbed CSS3 Menus

Look no further than here http://cssmenumaker.com/menu/grey-tabbed-menu for an easy CSS guide to making awesome menus.

This works in ASP.NET and pretty much anything else.

Enjoy!

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.

Sunday, October 14, 2012

Input string was not in a correct format.Couldn't store <>

I got this error after simply adding a column to the SQL Server database, then the XSD Dataset, and lastly a Datagridview.

Here is how I fixed it (it was simple, and Visual Studio misled me, causing this error).

 I added the column here, and clicked FinishDO NOT DO THAT.  Instead click Next, then Next

Only when you see this screen is the column correctly added.


...

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.

Thursday, September 27, 2012

Microsoft Lies

Ok, I joined the Microsoft Partner network so I could get Visual Studio 2010 (back when that was a new thing).  It was a good deal, but before I signed I called in and asked specifically if I could continue to use the software if I decided not to renew my partner network membership.

The answer was yes.  My memory is not fuzzy on this because that was the key upon which my decision to join depended.  So I signed up.

Fast forward to this morning.  I need to move my Visual Studio to a new machine.  The machine is up and running and all I need is...
  1. a link to re-download the installer
  2. my product key (which I may have somewhere in my 2010 tax box).
You'd think all this info would be readily available in my records.  So I log into my msdn account, click "My Product Keys" and...

So I called in again and got a really helpful guy named Ozzie.  And as it turns out, Ozzie will not help me move my Visual Studio to my new machine unless I shell out for a new partner membership membership for $149 or something - I couldn't quite hear the price over the call center chatter.

Now those of you who know me know that I am struggling mightily in this economy.  And now apparently - even though I got a valid microsoft product without cheating, they want MONEY just to move it to a new machine!  Apparently, they want money just for me to continue using the software at all!  So apparently, when you buy something, it is not really yours!  It's theirs, you're just renting it!  They should stop calling it sales, and call it what it is.  Rentals!  They are not GM, they are Netflix!  You don't get to keep what you buy!

So now I am faced with an ethical dilemma.  Do I (apparently illegally) keep using "my" software to try and eke out a living?  Or (even though I was lied to twice) should I deactivate it and basically starve to death?

I have about -$32,000 right now, and am struggling to keep food on the table.  I CANNOT just pay them and take the hit.  Especially after they misled me!

Please, if you are considering signing up for the Microsoft Partner Network to get your hands on discount software, RECONSIDER!  That or understand exactly what you will be DENIED access to after your subscription ends.


...

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.

Sunday, September 23, 2012

Getting the Identity Of Inserted Row in Visual C#

This works to return the identity of an inserted row.  The highlighted portions are the important parts.

 private int insertnew()
        {
            int newtix = 0;
            string SQL1 = "insert into delivery (date) values (GETDATE()); SELECT CAST(scope_identity() AS int)";
            SqlCommand myCommand = new SqlCommand(SQL1, myConnection);

            try
            {
                newtix = Convert.ToInt32(myCommand.ExecuteScalar());
            }
            finally
            {
                myReader.Close();
            }

            return newtix;
        }
Note that
  1. there are 2 SQL commands in the one SQL string.
  2. I had to cast the Scope_Identity as an int in the SQL or it would not read.
  3. ExecuteScalar reads the FIRST column in the FIRST row only, but if there were a compound key, or multiple rows inserted, ExecuteReader can be used instead.



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.

Sunday, September 16, 2012

Scope Creep: How to Go Out of Business.

Scope Creep.

It is the nature of our business that the fine details of what we need to accomplish are not fully known until we are writing the code.  Now and then, there is some element of what we are doing that we thought would take a few minutes (i.e. creating a scheduling screen) end up taking longer (because Microsoft decided that their DateTimePicker control cannot handle nulls, and instead displays today's date - bogus data).  Seriously - that one ate 2 days of coding time for me.

But the problem we encounter is that the client wants the entire project quoted up front, is not willing to dive into the details, and only reads the bottom line, and in their head, they come to understand only that "finished software" will cost $N.

So we work hard, trying to meet our deadline, and then we release the software only to receive a list of 200 items that they want changed.  Their expectation is that all these changes should be covered in the original cost you quoted them.  Even if you told them that the quote was an estimate and you were working hourly.  Even though none of the 200 items were anywhere in the specification that you painstakingly wrote and that they never read.

"You quoted me $8,000". They treat you like you're trying to scam them.  You know that if you let this get away from you, that you'll be working for free for the rest of your life every time they get an error message.  

This is called scope creep, and left unchecked, it will cause all your software projects to take forever, never release, and eventually die.  This is awful in a corporate environment, where a business unit just keeps drawing out the project forever because they are afraid that they have left something out, but it's even worse as an indy developer, as the client never accepts the release and will always refuse to pay.  Months of development time and now you're fighting them about what is and is not in scope.  Meanwhile you have bills to pay and you've put off other potential work to focus on this client. 

NO FIXED BIDS

Never quote any job, unless it will take less than a day as a fixed bid.  You're setting yourself up for a disaster.  They WILL try to keep you working forever under the terms of the original agreement.  You will become a slave.

WRITE EXACT SPECIFICATIONS

If your project spec says "Make an order entry application, 100 hours, $N", you are going to get the kitchen sink thrown into that project.

"Of course you can't have order entry without an entire ecommerce website, and inventory control and contact management, and integration with Quickbooks, and connected to the Weather Channel and an auto dialer and ...  That's just common sense!"
Your spec should say exactly what screens,web pages, databases, and all the rest that you plan to create.  It should specify what platform the app will run on.  If new equipment is needed, specify what kind of equipment and who will pay for it.  Make sure and state what is included in the estimate.  Later, when they come back and say "Hey, we thought it was going to do X", ask them what line item in the quote led them to think that.  Then offer to add that to version 2.

I once had a meeting with a guy that took 4 hours and was talking about a Visual Studio app, taking down his requirements to replace a DOS app from the 1970's.   We shook hands, I promised him a quote, and as we were leaving the room, added "of course this will run on Windows, Apple, Linux, smart phones, Ipads, and everything, right?"  When he got my quote back for all the time it would take to engineer for every possible platform, he had an aneurism.  But it would have worked brilliantly on his smart toaster.

GET IT IN WRITING

Write up - in mind-numbing detail - what you plan to do.  Make them sign it.  Go over it item by item - before they sign - and if they think you've left something out, add it.  Makes sure it says that this is the entire scope of the project, and once these items are met the terms of the project are satisfied.   Make sure it says that you are working hourly and the prices quoted are estimates only.  Make sure they understand that whatever changes they want afterwards will become a new project.


ARRANGE PAYMENT UP FRONT
Set a schedule for payment.  I know you're a coder and you don't like telling a client that they need to hand you a giant wad of cash, but you need to grow a pair and get comfortable saying it.  DO NOT be a nice guy and tell them, oh, it's ok, you can pay me whenever.  Say things like "We require $600 non-refundable up-front, and the rest is due 30 days after completion.  Just say it like "That's our policy.  We can't start a project without that."

KEEP THEM IN THE LOOP
Ok, so you're falling behind, because something you estimated has become a nightmare.  You're all over the internet trying to resolve some issue.  You promised them delivery - or a demo and the damned thing just refuses to coalesce.  You're working later and later into the night and now you're getting very late.  So you stop calling the client, because you are tired of telling him you're having problems and you are going to miss the schedule, and you think that just one more day and you can work it out.  Now it's been a week and you're still struggling.

The longer you put off telling them, the worse it gets.  You need to call them as soon as you realize there is a problem.  I know you want them to think that you are loaded with mad programming skillz, and this is like calling them to volunteer for a firing squad, but IT WILL ONLY GET WORSE THE LONGER YOU WAIT.  There. You have been warned.

UNDERSTAND THEIR PERSPECTIVE
When business clients outsource to a small contractor, they are worried if they can trust them to deliver on time and on budget.  They have to swallow that worry every time they  enter into a contract like this. They hand you their corporate data, and in faith they engage you to make their business better.  They have to quell that nagging worry that you are going to overcharge them or miss all your deliverables.   You need to work with that understanding.  Keep their expectations realistic.  Tell them they need to be involved in the design process.  Only by working together can you avoid the never-ending time eating path of perpetual scope creep.


...

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.

Wednesday, August 29, 2012

Sucky Docking in Visual Studio

Delphi always did this right.  Even back in 1990.  But Some dipshit at Microsoft thought this would be the perfect way to make controls dock.  When you dock a binding navigator to the top of your panel or form, and then a Datagridview to "fill", Microsoft thought this is what you'd have in mind.


 Even the little box that pops up seems to show it correctly.

Whatever the case, I must now use a splitter - an object with tons of functionality that I don't want - to complete my layout.

Microsoft, you SUCK.

...

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.

Wednesday, August 1, 2012

C#: Quickly Strip out all the Spaces from a String

This line of code will remove all spaces from a string.

string s="Some string with spaces";
string s2 = string.Join("",s.Split(' '));

...

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, July 24, 2012

Search Engine Optimization

Not a week goes by that I don't get a call at the office from some robot claiming that they can "put my website on the front page of Google search".   This is of course, pure bullshit.  My page is indexed by its content, and trying to trick Google into thinking it's something it isn't is a one-way ticket to the blacklist.  Which leads me to a question.

Look: like all of you, I want my site to get noticed. I try to use words and phrases that clearly state what services I offer so that someone searching for what I offer may see it.

What is SEO?
Ideally, SEO is the art of creating a concise, well-stated description of your product or service.  But years ago, some sites started including - what's the technical word - CRAP - in the meta tags to draw more hits in the hopes of creating traffic.  They loaded their meta tags with stuff that was not relevant to their site, but that was currently trending in searches.  So you'd have say, a clothing store with meta tags including every porn search word, every celebrity's name, every city, state, province, etc.  And way back then, the Google crawler just indexed it all.

Of course if you were selling shoes, and you got a million hits from people searching for "naked porn stars" or "free money", then you know you never got any sales from those people. Your site just became more and more hated and blacklisted.

But things change.  Google edited the crawler to recognize this and ignore it.  A while back I read that Google doesn't even read a page's meta keywords anymore.

Links
Also, Google ranking depends on links.  How many people from other sites link to your site?  Some sketchy characters found that if you register 1000 domain names, point them all at a single page, and sell people a link, they could boost your ranking.  That's why there were all these sites that were just pages full of nothing but random, disorganized links.  That no longer works, either.  The Google crawler can see these sites a mile away and nothing gets indexed.


Organic SEO
There is some good advice about SEO here, on Google's own site.  You don't need to hire an expensive consulting firm to trick search engines into promoting your page.  The trick, if there is one, is to make your page clear and relevant to your potential clients.  Organic SEO is the practice of making what you say in the text of your site deliver your message clearly, and without subterfuge.

There are reasons to hire an outside firm to help with Organic SEO.  They can help you fix grammar and spelling errors (do you want to be indexed under "women's fashoin", or "web desing"?).  They can look at your text with an outsiders eye and help you make it clear and concise.  They can help you pick a decent domain name and avoid the kind of problems that Experts Exchange had when they registered expertsexchange.com 

But consultants can't guarantee you a #1 ranking and they can't gin up massive traffic that will generate sales. Any firm who claims they can are looking to scam you.


...

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.

Friday, July 20, 2012

Context Formatting, PreferredSize

Row n Column n Context Formatting, PreferredSize... System.Windows.Forms.DataGridViewDataErrorEventArgs 

I was working in Visual Studio 2010 on a desktop app and it started throwing this error hundreds of times.  This app is using SQL Server database, with a DataGridView on a form.

A little investigation showed  that the columns referenced were always ComboBox columns.  In my case they were comboboxes where the list info is being populated at runtime, and not from the dataset. 

how to fix it:

Simply make sure that the tableadapter.fill() is not executed until the combobox lists are already populated.



...

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.

Thursday, July 19, 2012

Determine a Shipper from the Tracking Number


    //from http://www.beginnercode.com/2010/11/09/regex-for-common-courier-tracking-numbers/
    ///****
    //[ UPS ]
    //9 digits, or 1Z+whatever digits
    //The quick brown fox 1Z9999W99999999999 jumps over the lazy dog.
    //*/
    //$ups = '/(\b\d{9}\b)|(\b1Z\d+\b)/';

    ///****
    //[ Fedex ]
    //12 digits, 15 digits, or '96'+ 20 digits
    //The quick brown fox 999999999999 jumps over the lazy dog.
    //*/
    //$fedex = '/(\b96\d{20}\b)|(\b\d{15}\b)|(\b\d{12}\b)/';

    ///***
    //[ USPS ]
    //30 digits, '91'+20 digits, 20 digits (untested)
    //< TOTALLY UNTESTED BY ME AT THIS TIME >
    //*/
    //$usps = '/(\b\d{30}\b)|(\b91\d+\b)|(\b\d{20}\b)/';

    enum shipper { FedEx, UPS, USPS, unknown }


    class ShippingClass
    {
        //[ UPS ]
        //9 digits, or 1Z+whatever digits
        const string UPS_REGEX = @"(^1Z\w+)|(\b\d{9}\b)";
        //[ Fedex ]
        //12 digits, 15 digits, or '96'+ 20 digits
        const string FEDEX_REGEX = @"(\b\d{12}\b)|(\b\d{15}\b)|(^96\d+)";

        //[ USPS ]
        //30 digits, '91'+20 digits, 20 digits (untested)
        const string USPS_REGEX = @"(\b\d{30}\b)|(\b91\d+\b)|(\b\d{20}\b)";

        public static shipper getShipper(string TrackingNumber)
        {
            shipper answer = shipper.unknown;
            if (Regex.IsMatch(TrackingNumber, USPS_REGEX)) { answer = shipper.USPS; }

            if (Regex.IsMatch(TrackingNumber, FEDEX_REGEX)) { answer = shipper.FedEx; }

            if (Regex.IsMatch(TrackingNumber, UPS_REGEX)) { answer = shipper.UPS; }
            return answer;
        }
    }


...

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, July 17, 2012

Agile Methodology

I had to attend an Agile presentation at a company I once loved.  I got worried around frame 2 when I learned that it was designed by a couple psychologists who had never even attempted to complete a software project in their dreams.   I gave up about frame 12 when they listed the assumption that developers don't do anything until 2 days before a deadline.  So, given a 4 week project, they assume we all just browse for porn for 26 days and then bash the thing out real quick.

Somewhere later, while I was dozing, they said that having a 2 hour daily group meeting would really help speed up development.

Given that I had been working 10 hour days for about 6 years, I took a little issue with the whole ball of string.  It was like trying to teach me Keynesian economics.  Basically, if you make a big pile of generic and mostly false assumptions, then you can draw a firm, but absurd course of action.

6 months later, they laid off 2,000 people.  10 months later that company was out of business.  Just sayin'.
...

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.

Sunday, July 8, 2012

I Don't Trust Sourcegear Vault

Sorry to have to say this, but in my short time trying to use it I have had numerous issues like:
  • Don't use the latest version, use this special old release.  It's the only one that works for us (version compatibility issues).
  • You check in a project and when you check back out it's in the wrong place.
    • This makes it so when you browse your tree, all the files show as missing, which makes me panic every single time.
    • If you try to fix it incorrectly, it adds your whole project to a folder inside your project like root/myProject/myProject/...all my folders - again
  • It locks all your files when you told it not to.
  • At least once in every project, things have gotten so banjaxed that I have had to delete and re-upload the entire source set, meaning all previous revisions are lost.
Bottom line, Vault has lost my confidence. Now only on the rarest occasion, when all the pressure is completely off to get my work done, will I dare to try a check-in.  Because about 65% of the time, trying to check in my source means losing a significant part of a workday trying to sort out some stupid problem.

Incidentally, I have used Subversion many times, and NEVER had any of these problems, or really had to dig through manuals for answers.

...

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.

Friday, July 6, 2012

MSSQL: Find the next available number in a sequence

 So I have this data table, with a non-key column, and I need to write a SQL Query to find the next unused number in the sequence that is greater than a certain starting number.

It's like this:
A long time ago, the table started somewhere around 1000.  This is not a key column, and some of the older records have been archived out of the table.  So I have a weird number that will serve as my minimum.   BUT! there are also some wacky big numbers in the system and I don't want to do a max(n)+1.

Here's a beakdown:

0-35956these numbers are there, with lots of missing numbers and gaps due to archiving.
35957-1904121900a few of these are there, I want to find the next available number in this region.
1904121901 and upI want to ignore these numbers, and not use them (until all the numbers leading up to here are filled).

My Goal here is to create a SQL Query I can run once to find the next available number (meaning the lowest number above 35957 that does not exist in the column).

Here is what worked.

select min(Item_No)+1 as NextID
from Data_Table DT
where not exists
(select 1 from Data_Table DT2 where DT2.Item_No=DT.Item_No+1)
and Item_No > 35956

...

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, May 22, 2012

Avoiding SQL Injection Attacks

Take a simple SQL Query.

Select account_number from users where username='USER' and password='PASSWORD' This might be the kind of query you'd use to see if a user entered his username and password correctly.  If you get an account_id then the user is logged in.

So, you put a couple of text boxes on a login page and try to pass the values from the text boxes to the query, like this.

string USER=tbUser.text;
string PASSWORD=tbPassword.text;
string SQL="Select account_number from users where username='" + USER + "' and password='" + PASSWORD + "'"
That way if the user types in "Bob" and "MyPassword" the query executed looks like this.

Select account_number from users where username='Bob' and password='MyPassword'  Perfect.  This works great, and was a pretty standard way of doing this kind of query for a very long time, dating back to before the world wide web.  But what if a SQL-savvy hacker wants to see what kind of mischief he can cause, and tries playing with your SQL's head.  What if he entered something like this...

Username: ' or ''='
Password: ' or ''='

What does that do to our query?

Select account_number from users where username='' or ''='' and password='' or ''='' There is a really good chance that this query will bring back all the records in your database, and then think that this user is correctly logged into the very first one.  Note that login queries are not the only kind that can be hacked this way, but they are the easiest targets.  It could just as easily be your help ticket system, or anything else exposed to the web.

This is called a SQL injection attack. It's very common, but luckily it's very easy to thwart.  But there are some programmers who go about it the wrong way.

How not to protect yourself.
  1. Don't think your site is too small to get noticed and attacked.
  2. Don't  rely on Javascript, as it is easily disabled.
  3. Don't rely on Flash, as there are still people who hate it and will not load it on their browsers.
  4. Don't rely on HTML settings like maximum character lengths. They can be dispatched.
How to protect yourself.
An easy way to render SQL Injection attacks ineffective is to use sql parameters.  The not so easy part of that - if you have a massive web site - is that you have to edit ALL of your SQL that is exposed to the web.  in SQL Server, you do that like this:
string SQL="Select account_number from users where username=@USER and password=@PASSWORD"; Then, in your query you use parameters to fill in the values, like this:
myCommand.Parameters.AddWithValue("@USER", username.text);
myCommand.Parameters.AddWithValue("@PASSWORD", password.text);
Now (at least in SQL Server), no matter what they enter, it will be treated as query values.  if there is no user who's name and password are ' or ''=', then they will not be logged in.  I know it's a hassle to recode all your website queries.  But nowhere near as big a hassle as having a massive data breach to deal with.


...

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.

Thursday, March 22, 2012

Sorting a non-SQL Dataset

So I have an ASP.NET web site and the data is coming not from a SQL Server database, but from a web service.  I have executed the Web call and gathered the data into a dataset, and I wish to sort the data inversely by job order number, floating the newest job orders to the top.

As it turns out, there is no Dataset.Sort command, but there is this.

DataRow[] SortedRows = JobsDataTable.Select("isOpen = 1", "jobOrderID desc");

This gives back a sorted (and also filtered) array of DataRows based on the criteria you use. 

Now, you can't databind to a DataRow array, but if you are using databinding, the DataGrid or DataGridView can filter and sort for you, so you wouldn't need to use this technique.



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.

Saturday, March 17, 2012

Making an Image into a Usable Web Background.

 Ok, you have this great image and you want to make it the backdrop for your company web page.  Let's pick this one.
Photo of a building.
Beautiful.  But look what happens when you use it as a web backdrop.

OMG you can't read a thing!  So let's try playing with the contrast...

brightness+contrast
 Not this!  It's washed out all the details!  Maybe the levels...
 Closer, but there must be a better way.  And there is!  The problem is that we're thinking like engineers and not artists.  Try this.

Add a layer of White over the top of the image.  Then set the transparency of that layer to about 85%  This trick also works for dark backgrounds, just fill the new layer with black.



...

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.

Friday, March 16, 2012

C#.NET Finding the First Day of the Week in One Line of Code

while (myDate.DayOfWeek != DayOfWeek.Sunday) { myDate = myDate.AddDays(-1); }


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.

Thursday, March 15, 2012

So, I deleted and recreated a dataset in my Visual Studio Project.

Now, the IDE is all banjaxed, and confused.

Note:

My Solution Explorer
 Here is the shiny new dataset in the Solution Explorer.  Now look in my Data Sources pane.
My Data Sources Pane
...and no amount of refreshing, reloading, or rebooting fixes it.

The Solution
This was an odd one to be sure.  but looking at the properties window in the LotTravellerDataset1.xsd, the name property was set to "Dataset1".  Changing this property fixed the issue.


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.

Using a Stored Procedure that Returns Multiple Results

Microsoft did a brilliant thing in SQL Server.  I applaud them for this.  You can actually pack more than one SQL Query into a stored procedure and have a single procedure return multiple result sets in one pass.  So I used this feature to write a stored procedure that gathers all the data I need for a certain .rdlc report in my app.  Now I can use this one stored procedure to gather all the data in one fell swoop.

I have done this kind of multiple result set stored procedures in the past and it's a simple matter in code to sort out the data tables, as they are returned in order. Here is some sample code for another instance of this method.

/// <summary>
/// gets the dataset from the database.
/// </summary>
public void Load_Data()
{
    SqlCommand myCommand = new SqlCommand("[dbo].[ExtractWebData]", myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;

    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(myCommand);
    ds = new DataSet();
    adapter.Fill(ds);
    ds.Tables[0].TableName = "Customers";
    ds.Tables[1].TableName = "Purchase_Orders";
    ds.Tables[2].TableName = "Purchase_Order_Details";
    ds.Tables[3].TableName = "Inventory";           
}

Now if there was just some way to use that in a report.  It would be great to use a stored procedure to twist all the data into shape before handing it off to a reporting tool!  So you create an xsd. You add a table adapter.
You tell it to use your existing stored procedure.

 You select your stored procedure, and there's the first dataset... But wait.  How do you tell it where the other result sets are?  I want to use all 3 datasets! 



Except that you can't do that.  You see, the .rdlc report requires that you have the data available at design time in order to design the report.  And there is no way to import multiple datasets at once into the .xsd at design time.

From this document: http://msdn.microsoft.com/en-us/library/dd239331.aspx

If multiple result sets are retrieved through a single query, only the first result set is processed, and all other result sets are ignored. For example, when you run the following query in the text-based query designer, only the result set for Production.Product appears in the result pane:
SELECT ProductID FROM Production.Product
GO
SELECT ContactID FROM Person.Contact
I have no idea what the text-based query designer is, but as we saw in SQL Server Management Studio...



In my opinion, this is an EPIC DESIGN FAIL on the part of Microsoft.

We know from the earlier code snippet that Visual Studio can access the data, it just - for some stupid reason - is designed in such a way as to disable this feature in certain cases.  This is completely unacceptable.  But until Microsoft fixes this glaring, stupid, boneheaded omission, we're stuck with it.

Now, I know this blog has no regular readers.  You didn't find this page because you're a fan of Visual Studio Journey.  You found it because you were googling for this problem, and there were no answers anywhere else.  I wish I had better news, but I don't.

Here is the only way I know to work around this issue.  Unravel your stored procedure and execute the whole thing in your client.  Convert each piece into individual queries, and add those to your .xsd.

Alternately, you could split your stored procedure into multiple pieces, like Proc1, Proc2, Proc3... but then you've kind of lost the convenience of the one-stop shopping the stored procedure offers.

Just to be clear: I think that the ability to return multiple result sets from a stored procedure is awesome!  Kudos to the SQL Server development team.  If only the Visual Studio guys would catch some of that brilliance, things would be great.

Addendum:
Just to make myself clear, I think that this feature has awesome potential and multi-table stored procedures are still incredibly useful in Visual Studio.  They just are not usable for rdlc reports (the one thing they would be most ideally suited for in an ideal world).

...

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, March 6, 2012

How to easily compare 2 SQL Server Databases.

NOTE: this is for SQL SERVER databases only.  It will not sync Oracle to Interbase or MySql to Sybase.  However, application of this technique may apply to any two databases of the same type. (i.e. oracle to oracle)

I had lost access to the  production database for a time, and wanted to ensure that I had propagated all my recent changes from Development to production - without spending $895 for a SQL management and database analysis system.

So here's how I proceeded to sync my tables from one database to the other.

Open the Production database (or a current backup of it) in SQL Server Management studio.
use this query:
SELECT
    TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='dbo'
order by 1,2,4
Note: these columns were important to me, feel free to modify the columns as you see fit.  Also I only cared about the 'dbo' schema, so I filtered for that.  Your needs may vary.

This will give you the column info for all tables in the 'dbo' schema.

Now, save the data by right clicking the grid and selecting Save Results As.
Give it a name like Production_Schema and save as .txt.

Note: csv works too, but I find txt easier to compare.

Next do the same with the development database, naming it something like Development_Schema.txt.

Now you need a diff tool like WinMerge.  Compare these 2 files to see where they differ.  The text files cover all columns in all datatables and views in the schema.

Now go through the differences and see what changes have to be made in production so your software doesn't crash.  When I did this, I noted that there were some changes that were not ready for prime-time yet, so I left them unfixed in production.

The only thing left to compare once this is complete are the stored procedures and functions.  I just made a fresh, empty query and did a Script Stored Procedure As > Drop and Create to > Clipboard and pasted every one of them into the query. I saved it as AllFunctions.SQL and ran it in production to sync the functions.


 I hope this helps!

Comments are welcome!
...

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.

Wednesday, February 29, 2012

Using Page Properties in ASP.net

 This tutorial will show you how to create and use a page property in ASP.NET.  In this example, the requirement is to randomly select a flash video and embed it into a web page.

First, we create a page property in the code-behind page (C# file).


public partial class _Default : System.Web.UI.Page
{
    //name of the video we're showing.
    string thisvideo = "";

    //this is the read-only property. In the page it'll be seen as <%=VideoUrl %>
    public string VideoUrl
    {
    //it only has a 'get' no set.
        get
        {
            return "SomeString";
        }
    }

Now we embed the property in the HTML like this...


            <object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" width="400" height="300"
                id="FLVPlayer">
                <param name="movie" value="flv/FLVPlayer_Progressive.swf" />
                <param name="quality" value="high" />
                <param name="wmode" value="opaque" />
                <param name="scale" value="noscale" />
                <param name="salign" value="lt" />
                <param name="FlashVars" value="&amp;MM_ComponentVersion=1&amp;skinName=flv/Clear_Skin_1&amp;streamName=<%=VideoUrl %>&amp;autoPlay=true&amp;autoRewind=true" />
                <param name="swfversion" value="8,0,0,0" />
                <!-- This param tag prompts users with Flash Player 6.0 r65 and higher to download the latest version of Flash Player. Delete it if you don’t want users to see the prompt. -->
                <param name="expressinstall" value="js/expressInstall.swf" />
                <!-- Next object tag is for non-IE browsers. So hide it from IE using IECC. -->
                <!--[if !IE]>-->
                <object type="application/x-shockwave-flash" data="flv/FLVPlayer_Progressive.swf"
                    width="400" height="300">
                    <!--<![endif]-->
                    <param name="quality" value="high" />
                    <param name="wmode" value="opaque" />
                    <param name="scale" value="noscale" />
                    <param name="salign" value="lt" />
                    <param name="FlashVars" value="&amp;MM_ComponentVersion=1&amp;skinName=flv/Clear_Skin_1&amp;streamName=<%=VideoUrl %>&amp;autoPlay=true&amp;autoRewind=true" />
                    <param name="swfversion" value="8,0,0,0" />
                    <param name="expressinstall" value="js/expressInstall.swf" />
                    <!-- The browser displays the following alternative content for users with Flash Player 6.0 and older. -->
                    <div>
                        <h4>
                            Content on this page requires a newer version of Adobe Flash Player.</h4>
                        <p>
                            <a href="http://www.adobe.com/go/getflashplayer">
                                <img src="http://www.adobe.com/images/shared/download_buttons/get_flash_player.gif"
                                    alt="Get Adobe Flash player" /></a></p>
                    </div>
                    <!--[if !IE]>-->
                </object>
                <!--<![endif]-->
            </object>
Ok, this seems needlessly complicated, but note the green text.  This is the page property we created in the code behind page...



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.

Share This!

Contact Us

Name

Email *

Message *