Database

Ranoush wrote:

when trying to connect to a DB (MS Access) that has a password using a connection string like this
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"
i used to get that error about a workgroup file missing

SELECT Products.ProductID, 
       SUM(Sales.Sold_Qty) AS T_Sold_Qty, 
       SUM(SalesReturn.Returned_Qty) AS T_Returned_Qty
FROM Products
INNER JOIN Sales ON Sales.ProductID = Products.ProductID
INNER JOIN SalesReturn ON SalesReturn.ProductID = Products.ProductID
GROUP BY Products.ProductID

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

SELECT Products.ProductID, 
       SUM(Sales.Sold_Qty) AS T_Sold_Qty, 
       SUM(SalesReturn.Returned_Qty) AS T_Returned_Qty
FROM Products
INNER JOIN Sales ON Sales.ProductID = Products.ProductID
INNER JOIN SalesReturn ON SalesReturn.ProductID = Products.ProductID
GROUP BY Products.ProductID

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

SELECT A.C1, A.C2, B.C3, C.C4, C.C5, D.C6<br />
FROM A<br />
INNER JOIN B ON A.PKA = B.FKA<br />
INNER JOIN C ON A.PKA = C.FKA<br />
INNER JOIN D ON C.PKC = D.FKC

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

-- modified at 4:03 Thursday 6th October, 2005

CREATE FUNCTION dbo.udfGetSeconds (@Days INT)
	RETURNS INT

AS

BEGIN
	DECLARE @Seconds INT
	
	SET @Seconds = @Days * 24 * 60 * 60
	
	RETURN @Seconds
END

CREATE PROCEDURE sp_UpdateLeaders
AS
DECLARE
-- prep all variables
@YspCalc money, 
@FeeCalc money, 
@Fees money, 
@Amount money, 
@Ysp money,
@Fee1 money, 
@Fee2 money, 
@Fee3 money, 
@Fee4 money, 
@Fee5 money, 
@Fee6 money,
@Number varchar(50), 
@McName varchar(50), 
@Client varchar(50), 
@Officer varchar(50),
@FName varchar(25), 
@LName varchar(25),
@Date datetime,
@Points integer, 
@LoanType integer, 
@Mortgage integer

-- Declare the cursor. This sets up the select statement that contains 
-- the rows of data we want to iterate over. (Note: I've taken out your
-- assignments to the local variables - you'll see later why)
DECLARE bors_cursor CURSOR FOR
SELECT 
TLH_1_68, TLH_1_69, TLH_1_19, 
TLH_1_748, TLH_1_2, TLH_2_58, 
TLH_1_420, TLH_1_869, TLH_1_2,
GRICA_1_2, GRICA_1_4, GRICA_1_6, 
GRICA_1_10, GRICA_1_12, GRICA_1_8,
TLH_1_317, TLH_1_876
FROM MakeDaily53.DBO.bors
WHERE TLH_1_749 Is NULL 
AND Filename NOT LIKE '%.2'
AND TLH_1_1007 IN ('645','644','642','638','636','599','598','592',
    '589','587','586','582','581','580','579','565','112')
-- Now open the cursor
OPEN bors_cursor

-- Collect the first row of data into the variables that will be used
-- Note: You had two different columns being assigned to @Amount, I guess one
-- of these is incorrect.
FETCH NEXT FROM bors_cursor 
INTO @FName, @LName, @LoanType, @Date, @Amount, @McName,
     @Mortgage, @Ysp, @Amount, @Fee1, @Fee2, @Fee3, @Fee4,
     @Fee5, @Fee6, @Officer, @Number

-- @@FETCH_STATUS tells us if the previous fetch operation went okay
-- Note: This is regardless of the number of cursors you have. It always
-- corresponds to the most recent FETCH command that was executed so make
-- sure you check it after each fetch operation. A status of 0 means
-- it went okay. So here, while we keep getting rows then go into the loop.
WHILE @@FETCH_STATUS = 0
BEGIN


-- check for existing loan in leaderboard
IF NOT EXISTS(SELECT * FROM LeaderBoard WHERE LoanNumber = @Number)
BEGIN

-- You'll recognise this code - I copied it unchanged from your post.
Print(@Number)

-- check for nulls
IF @Fee1 = Null
SET @Fee1 = 0
IF @Fee2 = Null
SET @Fee2 = 0
IF @Fee3 = Null
SET @Fee3 = 0
IF @Fee4 = Null
SET @Fee4 = 0
IF @Fee5 = Null
SET @Fee5 = 0
IF @Fee6 = Null
SET @Fee6 = 0

-- concatenate client name
SET @Client = @FName + ' ' + @LName
-- ysp+rsp caluclation
SET @YspCalc = (((@Ysp - 100)/100)*@Amount)
-- additional fees are calulated differently for 1st and 2nd mortgages
IF (@Mortgage = 1)
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-1425)
IF (@Mortgage = 2)
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-275)

-- insert into leaderboard
INSERT INTO LeaderBoard (ClientName, LoanType, LoanNumber, ClosedDate, LoanAmount, YspSrp, Points, AddFees, McName, MortgageType, LoanOfficer)
VALUES (@Client, @LoanType, @Number, @Date, @Amount, @YspCalc, @Points, @FeeCalc, @McName, @Mortgage, @Officer)

END



    -- Note: This fetch statement is exactly like the one just before the
    -- loop starts.
    FETCH NEXT FROM bors_cursor
    INTO @FName, @LName, @LoanType, @Date, @Amount, @McName,
        @Mortgage, @Ysp, @Amount, @Fee1, @Fee2, @Fee3, @Fee4,
        @Fee5, @Fee6, @Officer, @Number
END

-- Clean up. Cursors are terribly messy
CLOSE bors_cursor
DEALLOCATE bors_cursor

-- We're done!

-- go!
GO

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious