Pages

Wednesday, December 21, 2011

Just a Little Question

Does Microsoft purposely update their sunsetting OS's (like WinXP) with patches that will make them bog down and frustrate people - hoping that they will get so angry that they will run out and buy a new machine?  

From what I can see, they certainly do!


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 20, 2011

Incrementing a Non Numeric Index in SQL Server

So I have this client, and all his sales invoices in the old system are either numeric, or they are numeric (1000) with a single character prefix (C1000).   We want to auto increment the index automatically, but the autoincrement stuff is not going to work here.

So I wrote a function based on this SQL.

select 'C' + isnull(cast(1+max( cast(SUBSTRING(Invoice_no,2,99) as int)) as varchar),'1000')
from Sales where Invoice_No like 'C%'
Let's work from the inside out.
  1. Invoice_no is the column to increment.  ('C1234')
  2. first we substring the first character off with SUBSTRING(Invoice_no,2,99) ('1234')
  3. we use cast to find the integer of it (1234)
  4. we use 1+max to aggregate (find the max value of this integer) and add one. (1235)
  5. we cast the result back to a varchar ('1235')
  6. then - if isnull gives us a nulll, we use the hard-coded value of '1000'
  7. we prepend the 'C' back on  ('C1235')

Now we make this into a function so we can use it like "GetDate()" in the default value of the column.

ALTER FUNCTION [dbo].[NextSalesID] ()

RETURNS varchar(10)
AS
BEGIN
    DECLARE @Answer varchar(10)   
    select @Answer= 'C' + isnull(cast(1+max( cast(SUBSTRING(Invoice_no,2,99) as int)) as varchar),'1000')
    from Sales where Invoice_No like 'C%'
    RETURN @Answer
END
 Now we have to add it as the default value for our column.  This is accomplished by editing the table in SQL Server Management Studio, selecting the column, and...
Binding a scalar Function to a Column Default
MAKE SURE that management studio doesn't add quotes '' around your function name.





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 6, 2011

A Generic Error Logging Object

 There are certain tasks that you find yourself doing over and over in multiple projects - and at some point it makes sense to just create a gizmo and re-use it.  Here is my logging object that can log messages (errors or just messages) to files and to a database.
Enjoy.

 If you want to log to a database, you must create the data table.



Next, add this object to your project (Project-Add New Item-C# Class)
Name the new object "log.cs"


Now, all you need to do to from your app is add a call to log. ...

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 *