How to Auto-generate Order Line Item numbers for bulk uploads

 I had a problem where I had 17000 line items to insert into 9000 orders.

The system required line item numbers, preferably numbered 1through N where N could be anything from 1 to 200.

This is a tricky problem.  I found some code that can generate a number sequence without regard to Purchase order number, but not one that would number all the orders from 1 within that PO number.

This solution enabled me to run the inserts once per PO and catch all the orders.

--Go through each unique invoice and calculate the line numbers
declare commands cursor for 
select distinct 
	'declare @ID int; set @ID=0; update UploadedSales set @ID= LineNumber = @ID+1 where pono='''+PoNo+''';'
from UploadedSales

--loop thru the po numbers
declare @cmd varchar(max)


--run updates one by one.
open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
  exec(@cmd)
  fetch next from commands into @cmd
end

close commands
deallocate commands

There are 2 tricky parts to this:

• Executing a list of commands fetched from a table (a list of unique PoNos)
• Calculating an incrementing line number for each item being updated.

The first item is something I picked up off the Internet. How to create a series of commands and execute them one at time. For this I created a cursor for my SELECT DISTINCT command, and then looped through it.

To auto-generate the line numbers is tricky too. Note the part of the commands in the list generated by the first query. 'declare @ID int; set @ID=0; update UploadedSales set @ID= LineNumber = @ID+1 where pono='''+PoNo+''';'. This creates a command that looks like:

declare @ID int; set @ID=0; update UploadedSales set @ID= LineNumber = @ID+1 where pono='PONumber';'

Using this ID and updating with every insert is handled by the set @ID= LineNumber = @ID+1 part, a tricky way to both update the line with the next sequential number and increment it at the same time.

SQL Server: My AutoIncrement Column is Skipping Values (solved)

There is a long explanation about SQL server caching values for autoincrement columns, and losing them when the server is restarted, but the fix is this:

USE [database_name]

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

GO

This does incur a performance hit, so if you're inserting 20 billion records a day, be warned.  But for a normal web or app database, this will make t more likely that your autoincrement values will step up the way you had intended.

...

---

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.

Automatically execute a SQL Command from an icon

You'll need SQL Server command line tools to run this.



Assuming:

• you have a .sql file you want to run, 


• you have a username/password and all that.

Create a batch file.

1. In file manager, right-click somewhere in the folder you want to put it in (like Documents\batch) and New->Text file.


2. Edit the name that comes up and call it [something].bat


3. Windows may warn you about renaming file extensions.  Click OK.


4. Right click the new file in file manager and Edit.  It should open in notepad.


5. Add this text, using your own server, username and password.

sqlcmd -S ".\SQLEXPRESS" -U myUserName -P myPassword -i %1 -d DBName >result.txt
start Notepad.exe result.txt

Save the file.

See that %1 in there?  That means that the filename of  any .sql file you drop onto this bat file's icon will get inserted and the query will run, dropping the results into a text file (>result.txt)  

The bat file then opens notepad and shows the user the results.

...

---

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.

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

In my MVC app, I followed the OLD ASP.net roleManager and MembershipManager setup.

DO NOT DO THIS WITH MVC!

DO NOT RUN ASPNET_REGSQL!

Identity does NOT work that way anymore.
What I had to do was remove the line in my web.config that said:

<roleManager enabled="true" cacheRolesInCookie="false"  />

Once I got rid of that, the error blessedly poofed into a memory.


...

---

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: How to Concatenate Parent Nodes in Irregular Category Trees

Consider this table:

id	ParentCategory	Name
1	NULL	Household
2	1	Furniture
3	1	Appliances
4	2	Chair
5	2	Couch
6	2	Bed
7	3	Refridgerator
8	3	Counter
10	3	Bathroom

We want each node to display all it's parents back to its root.

This SQL helps.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        bryanv
-- Create date: 11/28/2018
-- Description:    gets the whole list from here down
-- =============================================
CREATE FUNCTION dbo.CategoryFullName
(
    @CategoryID int
   
)
RETURNS nvarchar(200)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ans nvarchar(200)
    declare @parent int

    -- Add the T-SQL statements to compute the return value here
    select @ans=name, @parent=ParentCategory from Categories where id=@CategoryID

    if @parent is not null
    BEGIN
        set @ans=dbo.CategoryFullName(@parent)+' - '+@ans
    END


    -- Return the result of the function
    RETURN @ans

END
GO




This will look up the current category text, and prepend all parents recursively until it gets to the root.

 You can then use this in a computed column to autogenerate the entire category tree.

Now you can automatically get the full tree with a simple select...

...

---

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: Return All Dates in a Range

This function will return a list of all dates between a start date and an end date.





SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        bryan valencia
-- Create date: 11/20/2018
-- Description:    returns the dates between start and end (inclusive).
-- =============================================
CREATE FUNCTION DaysBetween(@startdate DATE, @enddate DATE)
RETURNS @calendar TABLE
(
    calendarday DATE PRIMARY KEY
)
AS
BEGIN
        -- Fill the table variable with the rows for your result set

    WITH calendar AS
    (
      SELECT CAST(@startdate AS DATETIME) DateValue
      UNION ALL
      SELECT  DateValue + 1
      FROM    calendar  
      WHERE   DateValue + 1 <= @enddate
    )

    insert into @calendar(calendarday)
    (
    SELECT  cast(DateValue as Date) calendarday
    FROM    calendar
    )
    OPTION (MAXRECURSION 0)
   

    RETURN
END
GO






To call it: SELECT * FROM [dbo].[DaysBetween] ('01 jan 2018', '01 feb 2018')







...

---

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.

How to Copy a MSSQL Server database to a New Webhost without RESTORE

This article explains how to copy a database from one server to another when Backup/Restore is not an option.

• This will work as long as you have SQL Server Management Studio access to the source and destination databases.


• This is very slow, and may take many hours to run on a big database.

1. First, open the database server you want to copy in SSMS.


2. Next, Right-click the database, Tasks -> Generate Scripts.
    


3. In my case I want all the scripts to completely recreate my database, so here are the steps.


4. Next
   


5. Script entire database and all database objects


6. Save to new query window (file is OK too).
   


7. Click Advanced (by default it will write a script to create all objects, but not copy data).
   
   Make sure to select Schema and data.
   


8. Review all the other settings to make sure you get the stuff you want on the destination database.  Click OK, then Next.


9. Review and Next (or previous if you need to change something.)


10. You'll get a checklist screen and then the script is done.  It should pop up in SSMS.

To restore the database...

1. Log SSMS into the destination database.


2. Back up what's there if there is any chance you'll need it.
   


3. In your script, if you need to rename the database to satisfy the new hosting company, do a global replace in the SQL script.  Make sure the FileNames reflect the destination database name not the source database
   


4. Copy the entire script to the clipboard.


5. right click the destination database and do a New Query (if the database hasn't been created yet, you can click the server name).
   


6. Paste the script into the new query editor.


7. If the database already exists, you may need to delete the CREATE statement and all the ALTER DATABASE statements, down to the [USE database] command.


8. Look in the scrollbar for possible problems...  Then fix them.
   


9. Run the script and see what happens.  Get coffee.  It's gonna be a while.  Call your Dad.  He misses you.

You're done.  Just in case you don't have all the same Generate Scripts options, this demo was from a SQLExpress2016 database, using all this stuff...



Microsoft SQL Server Management Studio                        14.0.17199.0

Microsoft Analysis Services Client Tools                        14.0.1008.227

Microsoft Data Access Components (MDAC)                        10.0.16299.15

Microsoft MSXML                        3.0 4.0 6.0

Microsoft Internet Explorer                        9.11.16299.0

Microsoft .NET Framework                        4.0.30319.42000

Operating System                        6.3.16299

Absolutely no Fixed Bid Contracts - Ever!

I got a job from a client and it was a fixed bid.  We pay you x dollars and you finish the work.

BUT...

• The scope was ill-defined




• the "spec" for the project was "replace our existing system", not an objective set of criteria.  Therefore any variation between their old system and the new one could be considered incorrect.


• If there are features that we cant see from the 300 screen shots we have, then we'll have to do them all to be "finished"


• because there is no objective spec, this will end up where all fixed bid contracts end up




• the client will expect us to "finish" the project forever, because if they ever accept the work, then they will have to pay more for changes.


• the client will always have a giant list of 'fixes' whenever we show them the completed work.


• we will continue to push them to go live, and they will have every inclination to manufacture more and more changes, even if they are not in the original project.






• The estimate was based on a very simplified version of the work




• we showed the client a mockup with all the bells and whistles, and gave them an estimate.


• the client liked it, but wanted the work much, much cheaper.


• we offered a simplified approach to lower the cost.


• they didn't really listen and kept expecting everything at the lower cost.

This week I will finish the project.  I will build it to the demo server and the client will look at it.  We have exhausted ALL the hours assigned to the work.  They are going to have a list of things they want "fixed" before going live.  At that point we will want more money, but as far as they are concerned, it's a fixed bid.  They get whatever they want for the agreed price. 



Welcome to slavery.  Working free forever and never getting paid again.



Unless a project is less than 10 hours, NEVER accept a fixed bid contract.  It's a no-win scenario.



...

---

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.

How to Check for any Changes in Table Data.

I am having a problem where some sneak process is changing the wrong


data in my inventory table. I wanted to discover how and when this was


happening, so I created a copy of my inventory table (on my local


machine, like this:



Select * from [dbo].[Inventory] into [dbo].[InventoryBASELINE]



Now I can check for discrepancies, like this...



select * from [dbo].[InventoryBASELINE] IB where CHECKSUM(*) <>


isnull((select CHECKSUM(*) from [dbo].[Inventory] I where IB.ID=I.ID),0)


union all


select * from [dbo].[Inventory] I where CHECKSUM(*) <> isnull((select


CHECKSUM(*) from [dbo].[InventoryBASELINE] IB where IB.ID=I.ID),0)


order by ID



This will show me any rows that are different, missing, or added between


my baseline table and my current table.


If I only cared about a few columns, I could use CHECKSUM(Customer, Qty,


Style) any columns I care about in all 4 checksum functions.

Issuing Direct SQL from MVC5

Recently I needed to add a quick updater that added a value to Table1


when someone copied data from Table2.




Now, the normal way to do this with Entity Framework is to load the


Entity, and change the value, then save the changed entity.




But I am a SQL guy from way back, and rather than fetch the entire


record and write it all back through Entity Framework, I figured it


would be Way more efficient if I just sent the database an update


directly. Turns out that for us old SQL developers, it's terribly easy


to do just that.




Here is my example subroutine, for your dining pleasure.

        /// 

        /// Updates a Toolkit with the number of the Assignment ID Created from it. If you are trying to remove an assignment id from a toolkit, use RemoveAssignmentFromToolkit()
        /// 
        /// The toolkit ID to be updated
        /// The AssignmentID to attach
        public static void UpdateToolkitWithAssignmentID(int ToolkitID, int AssignmentID)
        {
            using (ORM db = new ORM())
            {
                try
                {
                    db.Database.ExecuteSqlCommand(
                        "UPDATE [dbo].[ToolkitRequests] SET[AssignmentID] = @AID Where[UniqueId] = @ID",
                        new SqlParameter("@AID", AssignmentID),
                        new SqlParameter("@ID", ToolkitID)
                        );
                }
                finally
                {
                    db.Dispose();
                }
            }
        }

That's it! It's super fast, and there is no mucking about in Entity


Framework. NOTE that all the EF validity checking is skipped when you


do this, so use it in cases where it's a simple SQL and you can ensure


data integrity yourself. In my case, there is no way to get in this


routine without valid values in both params, so I know I won't get


surprised with a stray NULL.






---


This email has been checked for viruses by Avast antivirus software.


https://www.avast.com/antivirus

Data to MVC in 10 minutes.

Database

Assuming you created your table in SSMS already, It should look something like this:

Now drag select the 3 columns like and control+C copy them to the clipboard:

…and the clipboard…

ID              uniqueidentifier     Unchecked

International   bit                  Unchecked

Name            nvarchar(20)         Unchecked

Description     nvarchar(200)        Checked

BarPercent      int                  Unchecked

IncludesSteps   varchar(MAX)         Unchecked

Model

Go to Visual Studio and in your Solution Explorer, right click Models and Add, then Class

Make it easy on yourself:  Name it the same as your table.

Make the code look like this…

namespace SA.DS._0._2.Models  //this should match your other models, or leave it how Visual Studio created it

{

        using System;

        using System.Collections.Generic;

        using System.ComponentModel.DataAnnotations;

        using System.ComponentModel.DataAnnotations.Schema;

        using System.Data.Entity.Spatial;

        [Table("Lookups.StatusBarTypes")]  //This is the name of your table.  If the schema is left out, it assumes [dbo]

        public partial class StatusBarTypes

        {

                [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]

                //a default constructor

                public StatusBarTypes()

                {

                }

        }

}

Now, we'll paste in the list from the clipboard.

                //a default constructor

                public StatusBarTypes()

                {

                }

                ID      uniqueidentifier        Unchecked

International   bit     Unchecked

Name    nvarchar(20)    Unchecked

Description     nvarchar(200)   Checked

BarPercent      int     Unchecked

IncludesSteps   varchar(MAX)    Unchecked

                Unchecked

        }

}

Then doctor it into object properties.  DO NOT rename any fields!  Make sure anything that says "Checked" is nullable.

Keep tabs on the string lengths and whether the columns are required or not. (not null means Requred)

                [Key]

                public Guid ID { get; set; }

                [Required]

                public bool International { get; set; }

                [Required]

                [StringLength(20)]

                public string Name { get; set; }

                [StringLength(200)]

                public string? Description { get; set; }

                [Required]

                public int BarPercent { get; set; }

                public int IncludesSteps { get; set; }

If you want, you can add display info, like prettier column names.

                [Key]

                public Guid ID { get; set; }

                [Required]

                [Display(Name="Intl")]

                public bool International { get; set; }

                [Required]             

                [StringLength(20)]

                public string Name { get; set; }

                [StringLength(200)]

                public string Description { get; set; }

                [Required]

                [Display(Name="Bar%")]

                public int BarPercent { get; set; }

                [Display(Name="Incl Steps")]

                public int IncludesSteps { get; set; }

OK save it and BUILD.

If all went well, we can…

Controller and View

In Controllers, right click Add, then Controller

Pick MVC5 Controller with Views, Using Entity Framework

Click Add.

Pick your new Model Class.

Make sure it's your correct Database, pick a layout page, and Add.

Note that it autogenerates your controller…

…and your CRUD views.

These Views are ALL WIRED UP and ready to use.  Open Index.cshtml in Visual Studio and run it.

Your empty data table awaits!  Add some records, edit them, delete them, modify them!