SQL Server: Friendly Time Differences

Ok, so I always like the Facebook smart date displays, like

posted by Bob Loblaw about 12 minutes ago

So I set about recreating this on my own sites in SQL Server.  Here is a fairly easy way to produce this kind of friendly time differences.

Select *
,iif(
datediff(DAY, errdate, getdate()) >0 ,
cast(datediff(DAY, errdate, getdate()) as varchar(90))+' days  ',
''
)+
iif(
datediff(HOUR, errdate, getdate()) >0 ,
cast(datediff(HOUR, errdate, getdate()) % 24 as varchar(90))+' hours  ',
''
)+
iif(
datediff(MINUTE, errdate, getdate()) >0 ,
cast(datediff(MINUTE, errdate, getdate()) % 60 as varchar(90))+' minutes  ',
''
)+
iif(
datediff(SECOND, errdate, getdate()) >0 ,
cast(datediff(SECOND, errdate, getdate()) % 60 as varchar(90))+' seconds ago.',
''
)
[friendlytime]
from errorlog
--where errdate > DATEADD(HOUR, -1, GETDATE())
order by errdate

This produces a nice, friendly time difference that is better than a list of raw dates for human interpretation.

errdate	page	errmessage	friendlytime
1961-06-17 11:07:21.820	TEST	THIS IS A TEST ERROR MESSAGE	18993 days  0 hours  20 minutes  36 seconds ago.
2010-06-17 11:06:05.470	TEST	THIS IS A TEST ERROR MESSAGE	1096 days  0 hours  21 minutes  52 seconds ago.
2012-06-17 11:04:41.123	TEST	THIS IS A TEST ERRORMESSAGE	365 days  0 hours  23 minutes  16 seconds ago.
2013-06-17 11:07:59.197	TEST	THIS IS A TEST ERROR MESSAGE	20 minutes  58 seconds ago.

Alternately, if you don't care to display all the way down to seconds when the time span is many days, you can nest it like this to only show the most relevant time gap.

iif(
datediff(DAY, errdate, getdate()) >0 ,
cast(datediff(DAY, errdate, getdate()) as varchar(90))+' days ago',
iif(
datediff(HOUR, errdate, getdate()) >0 ,
cast(datediff(HOUR, errdate, getdate()) as varchar(90))+' hours ago',
iif(
datediff(MINUTE, errdate, getdate()) >0 ,
cast(datediff(MINUTE, errdate, getdate()) as varchar(90))+' minutes ago',
iif(
datediff(SECOND, errdate, getdate()) >0 ,
cast(datediff(SECOND, errdate, getdate()) as varchar(90))+' seconds ago.',
'now'
)
)
)
) [friendlytime2]
This produces a result as follows:

18993 days ago
1096 days ago
365 days ago
38 minutes ago

...

---

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.

Best SQL Server Backup Script Evah

-- Back Up All Databases
-- by Bryan Valencia

--create temp table
declare @temp table(commands varchar(500), completed bit)

--load it with backup commands
insert into @temp (commands, completed)
(select
    'BACKUP DATABASE ['+name+
    '] TO  DISK = N''J:\Backups\'+name+
    '.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'''+name+
    '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10',
    0
from
    master.sys.databases
where
    owner_sid <> 0x01 and state_desc='ONLINE'
)

--variable for the current command
declare @thisCommand varchar(500);

--loop through the table
while (select count(1) from @temp where completed=0)>0
begin
    --find the first row that has not already been executed
    select top 1 @thisCommand = commands from @temp where completed=0

    --show the command in the "mesage" output window.
    print @thisCommand

    --execute the command
    EXEC (@thisCommand);

    --flag this row as completed.
    update @temp set completed=1 where commands=@thisCommand
end

--show the user the rows that have been found.
select * from @temp

---

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.

Min (n) must be less than or equal to max (-1) in a Range object.

This happened to me when I would read an XML file into a Dataset, as follows:

string sort = "";
string where = string.Format("userID={0}", EmployeeID);
DataSet ds = new DataSet();
ds.ReadXml(XMLFilename);
DataRow[] rows = ds.Tables[0].Select(where, sort, DataViewRowState.CurrentRows);
The userID column is numeric, yet I found that I could make this error dissappear by single-quoting the parameter like this.

string where = string.Format("userID='{0}'", EmployeeID);

...

---

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.

The "SendUsing" configuration value is invalid.

I am moving a asp classic app from Windows SBS 2003 IIS 7 to Windows Server 2012 IIS 8.
After spending all day figuring out a dozen different things, the site finds itself unable to send emails.  So I constructed a simple email sender to test it out.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
  <title> Email Test </title>
 </head>

 <body>
<%
    Set myMail=CreateObject("CDO.Message")
    myMail.Subject="Sending email with CDO"
    myMail.From="doNotReply@myserver.com"
    myMail.To="myemail@gmail.com"
    myMail.TextBody="This is a message."
    myMail.Send
    set myMail=nothing
%>

The message has been sent.
 </body>
</html>

 That's literally the whole thing.  it gives...

CDO.Message.1 error '80040220'
The "SendUsing" configuration value is invalid.
/cgi-bin/testemail.asp, line 14

So I googled.  First I changed the app pool user from ApplicationPoolIdentity to NetworkService, as many bloggers suggest.

No change.

Then I found some code that looks like this:

schema = "http://schemas.microsoft.com/cdo/configuration/"
  Set objFlds = objConf.Fields
  with objFlds
    .Item(schema & "sendusing") = 2
    .Item(schema & "smtpserver") = "smtp.gmail.com"
    .Item(schema & "smtpserverport") = 465
    .Item(schema & "smtpauthenticate") = 1
    .Item(schema & "sendusername") = "abc@def.com"
    .Item(schema & "sendpassword") = "qwerty"
    .Item(schema & "smtpusessl") = 1
    .Update
  End with

 and... no such luck.  Same Error.




...

---

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.

Setting Up WebDAV in IIS 8 for Site Publishing

This complete tutorial will tell you how to enable WebDav for publishing internet sites on your IIS8 server.

DONT.

Instead download FileZilla Server, install, and add an exception to your firewall.

I took 2 days trying to get WebDav to allow me to remote in and upload files to my site, and could not get past the "you are not authorized" message.

I had FileZilla running in 10 minutes. 

---

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.

Propertybinding Combobox Values

 Ok, you have a search dialog box and you'd like to store the user's last choice for use the next time he launches your program.

So you go into "PropertyBinding" and there is no way to bind this value.

SelectedIndex and SelectedText are not in the list.  Text will not work if your box is a DropDownList, it will only work if your Combobox is a Dropdown.

So here's what you have to do.

First, from your project, Select Project and <projectname> Properties.

Then add a user scoped string setting for your Combobox.

Don't save the SelectedIndex as an integer, because whenever the list changes, you'll be restoring the wrong item in the list.

private void FindDropDownValue(ComboBox ddl, string value, int defaultvalue)
{
    int selectedListItem = ddl.Items.IndexOf(value);
    if (selectedListItem == -1)
    {
        selectedListItem = defaultvalue;
    }
    ddl.SelectedIndex = selectedListItem;
}

private void Load_Dropdowns()
{
//reload user settings
    FindDropDownValue(cbCustomer, Properties.Settings.Default.Quote_Customer, 0);
} This will load the values from the user settings store.   I suggest calling Load_Dropdowns from your FORM_LOAD() method.

Now all we need is to save the settings on Form_Closing() of the form.

Private void SaveUserSettings()
{
    Properties.Settings.Default.Quote_Customer = cbCustomer.Text;
}

private void QuoteForm_FormClosing(object sender, FormClosingEventArgs e)
{
    SaveUserSettings();
}

---

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.

Temp Tables in SQL Server

We all know you can create variables in SQL Server...
declare @customer varchar(20)
set @customer='a customer'
...but what if there is a need to store more complex data?
As it turns out, there is an easy way to accomplish that as well.

declare @csrlist Table(customer varchar(20), CSR varchar(25), counts int)

--get the counts of customer service reps orders for each customer.
insert into @csrlist (customer, csr, counts)
(
select distinct customer, Csr, COUNT(1) counts
from Purchase_Order
where Csr is not null
group by customer, csr
) 
The resulting in-memory table can be inserted to, deleted from, updated, just like any real data table.
--find the CSR with the most orders for each customer
insert into @csrlist2 (customer, CSR)
    (select customer, Csr from @csrlist A where counts=(select MAX(counts) from @csrlist B where a.customer=b.customer))

---

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.

DropdownList: SelectedValue does nothing.

 I have a ComboBox set as a DropDownList, and the SelectedValue property, which ostensibly will find and select the selected value, does not.

cbSalesman.SelectedValue = salesman;  This is probably because you would need to give it not just a string to look for, but an actual object that is a member of the Items list.  That would require ugly gyrations along the lines of the following.

1. get the string to look for
2. get the index of that string in the dropdown (using Items.IndexOf)
3. grab the item at that index
4. feed it to the "SelectedValue" property.

Instead of all that, why not just do this.

cbSalesman.SelectedIndex = cbSalesman.Items.IndexOf(salesman);


...

---

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.

DBNulls: the Bane of my Existence

So yes, a DataGridViewCell.value is an object.  But it seems there should be a way to tell it "hey, in this case, when I ask for a value and you have a null, just hand me an empty string".

So I wrote this (for use until I figure out how to default a null cell to string.empty).

        /// <summary>
        /// Converts an object value to a string.
        /// (usually from a DataGridViewCell.Value;
        /// </summary>
        /// <param name="value">The value.</param>
        /// <returns></returns>
        public string Val2Str(object value)
        {
            if (value == DBNull.Value)
            {
                return string.Empty;
            }
            return value.ToString();
        }

        /// <summary>
        /// Converts a DataGridViewCell value to a string.
        /// </summary>
        /// <param name="Cell">The cell.</param>
        /// <returns></returns>
        public string Val2Str(DataGridViewCell Cell)
        {
            return Val2Str(Cell.Value);
        }
These overloaded routines can be passed either a DataGridViewCell itself, or just the Value property.  Kinda like this:

string customer = Val2Str(dataGridView1.CurrentRow.Cells["Customer"]);
string customer = Val2Str(dataGridView1.CurrentRow.Cells["Customer"].Value);
...

---

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.

Cannot execute as the database principal.

Cannot execute as the database principal because the principal "username" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I am going to read your mind now.

1. You recently backed up your database copy-only and moved it to another server or development box. 
2. You're attempting to perform an  "Execute As..." command.
3. Your software has been running for some time and this new error just started cropping up after you "refreshed" your copy of the database (from production?).
4. You looked at the server logins, and the database users, and they seem to match (there is a login with the same name as the user).

What happened is that the SIDs (Security IDs) from the server login does not match the database user of the same name.  Remember that LOGINS are stored at the server level and USERS are in the databases.

What you need to do is re-create the user in the database (and reassign any roles and permissions).

USE [myDB]
GO

/****** Object:  User [myUser]    Script Date: 01/14/2013 18:21:22 ******/
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myUser')
DROP USER [myUser]
GO

USE [myDB]
GO

/****** Object:  User [myUser]    Script Date: 01/14/2013 18:21:22 ******/
GO

CREATE USER [myUser] FOR LOGIN [myUser] WITH DEFAULT_SCHEMA=[dbo]
GO

...

---

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.

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.

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.

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.

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.

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 Finish.  DO 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.

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.

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.

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.

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.

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.