Public Sub UploadGuides()

        Dim LCPdb As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=LC_Pricing;" & _
           "Integrated Security=true;")

        Using LCPdb
            Try
                LCPdb.Open()

                Dim cmd As New System.Data.SqlClient.SqlCommand()
                cmd.Connection = LCPdb
                cmd.CommandType = Data.CommandType.StoredProcedure
                
                If frmMain.chkUpUS.Checked = True Then
                    cmd.CommandText = "Upload_US_LC"
                End If
                If frmMain.chkUpSysco.Checked = True Then
                    cmd.CommandText = "Upload_Sysco_LC"
                End If
                If frmMain.chkUpPFG.Checked = True Then
                    cmd.CommandText = "Upload_PFG_LC"
                End If

                Using reader As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
                    
                End Using
                
            Catch ex As Exception
                GeneralError("UploadGuides", ex)

            Finally
                LCPdb.Close()
                LCPdb = Nothing

            End Try

        End Using
        
    End Sub

USE [LC_Pricing]
GO

/****** Object:  StoredProcedure [dbo].[Upload_US_LC]    Script Date: 09/16/2009 09:58:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Upload_US_LC] 
	
	
AS
BEGIN
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USFoodsTemp]')
AND type in (N'U'))
DROP TABLE [dbo].[USFoodsTemp]

CREATE TABLE [dbo].[USFoodsTemp] (
    [Product #] Int,
    Description VARCHAR(255),
    Brand VARCHAR(255),
    Price Money,
    Store TinyInt,
    Selected Bit
)

INSERT INTO [dbo].[USFoodsTemp] ( [Product #], [Description], [Brand], [Price], Store, Selected)
SELECT [Product Number], [Product Desc], [Product Brand], [Product Price], 1, 0
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Shopping List 2009.xls;HDR=YES',
                'SELECT * FROM [Shopping List 2009$]')
                
MERGE INTO USFoods AS Target
USING USFoodsTemp AS Source
ON Target.[Product #]=Source.[Product #]
WHEN MATCHED AND Target.Description <> Source.Description OR Target.Price <> Source.Price THEN 
UPDATE SET Target.Description = Source.Description, Target.Last_Price = Target.Price,
Target.Price=Source.Price, Target.Updated = GetDate()
WHEN NOT MATCHED BY Target THEN
INSERT ([Product #], [Description], [Brand], [Price], Store, Selected, Updated) 
VALUES (Source.[Product #],Source.[Description],Source.[Brand], Source.[Price], 1, 0, GETDATE())
WHEN NOT MATCHED BY Source AND Target.[Product #] <> 0 THEN
DELETE
 OUTPUT $action, 
DELETED.[Product #] AS Product#, 
DELETED.[Description] AS ProductName, 
DELETED.[Price] AS Price, 
INSERTED.[Product #] AS Product#, 
INSERTED.[Description] AS ProductName, 
INSERTED.[Price] AS Price;
SELECT @@ROWCOUNT;
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USFoodsTemp]')
AND type in (N'U'))
DROP TABLE [dbo].[USFoodsTemp]
END


GO<br/><br/>