Database

Re: Help necessary in making Query

1-Jul-09 22:19

[Message Deleted]

Mycroft Holmes1-Jul-09 22:48

Mycroft Holmes

1-Jul-09 22:48

I was not arguing with your solution, just your statement that a pivot would not do the job because of the aggregate requirement. You can see it in this article[^]

Never underestimate the power of human stupidity
RAH

Re: Help necessary in making Query

Niladri_Biswas1-Jul-09 20:39

Re: Trigger NOT Fireing...

1-Jul-09 20:39

Try this, assuming your table name is (TBL_COLROW_TRANSPOSE)

SELECT
    CASE FINALRESULT.RECORDNUMBER
        WHEN 1 THEN R1.GLID
        WHEN 2 THEN R1.AMOUNT
    END AS '1ST_COLUMN',

	CASE FINALRESULT.RECORDNUMBER
        WHEN 1 THEN R2.GLID
        WHEN 2 THEN R2.AMOUNT
    END AS '2ND_COLUMN',

    CASE FINALRESULT.RECORDNUMBER
        WHEN 1 THEN R3.GLID
        WHEN 2 THEN R3.AMOUNT
    END AS '3RD_COLUMN'

FROM

    (SELECT AMOUNT, GLID, ROW_NUMBER() OVER(ORDER BY AMOUNT) AS 'RECORDNUMBER' FROM TBL_COLROW_TRANSPOSE) R1

		INNER JOIN

    (SELECT AMOUNT, GLID, ROW_NUMBER() OVER(ORDER BY AMOUNT) AS 'RECORDNUMBER' FROM TBL_COLROW_TRANSPOSE) R2

		ON (R1.RECORDNUMBER = R2.RECORDNUMBER - 1)

		INNER JOIN

    (SELECT AMOUNT, GLID, ROW_NUMBER() OVER(ORDER BY AMOUNT) AS 'RECORDNUMBER' FROM TBL_COLROW_TRANSPOSE) R3

		ON (R2.RECORDNUMBER = R3.RECORDNUMBER - 1)

		CROSS JOIN

    (SELECT 1 AS 'RECORDNUMBER' UNION ALL SELECT 2) FINALRESULT

Hope this helps

Vote me please
Smile | :)

Niladri Biswas

Trigger NOT Fireing...

Elizma1-Jul-09 9:36

Elizma

1-Jul-09 9:36

Hi
I am using SQL 2005. I have a trigger for insert on a table. In the script I read everything from the inserted table and write it into a User Log table. It seems as if the trigger is not fireing when the insert happens, but if I copy the records, delete it from the table and paste them back in, it fires the trigger.

Have anyone experience this problem before, and if you have, what can I do to fix this?

Your help on this will be greatly appreciated.

Kind Regards,

Elizma

Blue_Boy1-Jul-09 11:22

Blue_Boy

1-Jul-09 11:22

Instead using triggers I suggest you doing insert in your Log Table.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: Trigger NOT Fireing...

Jerry Hammond1-Jul-09 12:28

Jerry Hammond

1-Jul-09 12:28

show us your code please.

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Re: Trigger NOT Fireing... [modified]

Elizma1-Jul-09 20:48

Elizma

1-Jul-09 20:48

Hi
Here is my code.

CREATE Trigger [trg_MovementsLog]
ON [dbo].[GtrDetail]
FOR INSERT
AS
INSERT INTO tblMovementsLog(GtrReference,Line,StockCode, QtyMoved, MovedBy,TransactionDate)
SELECT inserted.GtrReference,inserted.Line, inserted.StockCode, inserted.GtrQuantity AS QtyMoved,
SUBSTRING(LotTransactions.Narration,9,Len(LotTransactions.Narration)-8) AS MovedBy, inserted.TransactionDate
FROM inserted INNER JOIN
GtrLot ON inserted.GtrReference = GtrLot.GtrReference AND inserted.Line = GtrLot.Line INNER JOIN
LotTransactions ON GtrLot.Lot = LotTransactions.LotJob AND inserted.GtrReference = LotTransactions.Reference AND
inserted.StockCode = LotTransactions.StockCode
WHERE (inserted.TargetWarehouse = 'HS') AND (NOT (inserted.TransferComplete = 'Y')) AND (LotTransactions.Narration LIKE 'JOB_TRF_%')

I know however that my code does work. The trigger use to work fine until they re-installed the server. Also it runs if I manually insert a record into the GtrDetail table. It just does not run when it's basically inserted via a script (Well, so it seems).

Thanx for your assistance.

Elizma

modified on Thursday, July 2, 2009 2:55 AM

Decimal field format problem

Branislav Vidovic1-Jul-09 3:25

Branislav Vidovic

1-Jul-09 3:25

Hy!
I have problem enetring data from my application to the database datable. Basicly this is the table in MS Access 2003 desgin:
table name: details
table fields:
acountNum (Text, primary key)
itemId (Integer, primary key)
price(Decimal, format: 0,000)
quantity(Long integer)

The problem is when i use my insert method which has a line that goes like this:

komanda.CommandText = "INSERT INTO details(acountNum, itemId, price, quantity) VALUES ('" + par1 + "', " + par2 + ", " + par3 + ", " + par4 + ")";

I use step by stet debuging in studio and check that all the values are correct. But what happens is a problem on a third field of the table. It is formated to represent data in 0,000. That means if you enetr just 3 it wil show like 3,000 in the database. Or if you enetr 2,98 it will show as 2,980 etc..
So let us say that in above comand i pased in these values for variables that store values:
par1 = "98bg", par2 = 12, par3 = 4.7 and part4 = 10. When you execute this command in step by step debuger and check the CommandText property it will look like this:

komanda.CommandText = "INSERT INTO details(acountNum, itemId, price, quantity) VALUES ('98bg', 12, 4,700, 10)";

We see that the command (which has its connection field already set to a opened connection prior to this) immediatly applied the format rule of price field and turned 4.7 into 4,700. Now of course when i execute the DataAdapter.Update method it will thorow an exception because now we have only 4 fields to insert values and actually 5 values ('98bg', 12, 4,700, 10). 4.7 became 4,700. Of course if i remove the format for the price field in my databse the command works perfectly. But in this situation i ned to have this format of numbers for the price.
Does anyone have an idea how to over come this small problem?

Cheers!

Enver Maroshi1-Jul-09 6:58

Enver Maroshi

1-Jul-09 6:58

I think parameters are working with ms access, this would remove all you troubles related to filling inside decimal values, dates, etc... mostly on computers that have different culture set. Parameters protect you also from sql injection.

Or you could use culture info to get decimal seperator and do a replace on string.

And i would write variable names on english, it has many benefits.

More on parameters here[^].

Edit: You can also write decimal number inside this way ...12, '4,700', 10...
But as i wrote, how do you know what is decimal seperator? Different culture, dufferent decimal seperator. Just use parameters.

Branislav Vidovic1-Jul-09 12:19

Branislav Vidovic

1-Jul-09 12:19

Thank you! That was helpfull!

Enver Maroshi1-Jul-09 23:18

Enver Maroshi

1-Jul-09 23:18

Moramo si susjedi međusobno pomagat Smile | :)

Blue_Boy2-Jul-09 3:03

Blue_Boy

2-Jul-09 3:03

Well,this is english portal. Thumbs Up | :thumbsup:

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

MDB opened indication

udikantz1-Jul-09 0:05

udikantz

1-Jul-09 0:05

hi,
i use VB.NET to develop an application which uses local MDB files as databases,
my question is:

is there anyway that i can indicate if a certain mdb file is already opened by another user ( even in access client ).

the goal is if for example a user open 2 instances of my application and connect with both instances to the same mdb file, i would like to display a warning messege on the 2nd connection that there is already 1 connection opened to this particular mdb file.

thanks in advance!

Net

paas1-Jul-09 1:46

paas

1-Jul-09 1:46

What I have done in the past, under a similar Access MDB requirement, is try to open the database exclusively, while trapping for the error if another user already has it opened, and then display the warning message if the error occurs. If the 'already opened' error did not occur, I would simply close the connection and then re-open the MDB shared.

Branislav Vidovic1-Jul-09 3:54

Branislav Vidovic

1-Jul-09 3:54

Well you can improvise. Every time you check if some text file exists in the same directory as your databse. If it does not exists you create it. If it does exists that means that someone already opened the connection and you can implement code that generates warning.
Just don't forget to delete this file when you close connection.
As for the access client, every time you open mdb file with access or any other database managing software (this includes your connection that you create from your application) another file will be created in the same folder as your database. It will have the same name but different extension (.ldb). So you can combine this feature also with the one i described to get more detailed informatiopn.
I will try to code one example here(let us say your database name is "base.mdb"):

connection.Open();
if(File.Exists("C:\\opened.txt") == true && File.Exists("base.ldb") == true)
  MessageBox.Show("Connection is opened by your application");
if(File.Exists("base.ldb") == true)
  MessageBox.Show("Connection is opened by Access client");
Else
  File.CreateText("C:\\opened.txt")

Of course dont forget to delete the text file after closing connection:

connection.Close();
if(File.Exists("C:\\opened.txt") == true)
  File.Delete("C:\\opened.txt")

Any way this is the general idea. In a text file you could write much additional information on who opened the databse, when, etc..

Cheers!

udikantz1-Jul-09 4:45

udikantz

1-Jul-09 4:45

i need the messege to be displayed even if the file is already opened by MS ACCESS.

Net

David Skelly1-Jul-09 6:17

David Skelly

1-Jul-09 6:17

First, this is a race condition waiting to happen.

Second, applications don't always end cleanly. Sooner or later something will go wrong and you will end up with the "opened.txt" file existing even though no one has the database open, or the .ldb file can hang around if Access does not close cleanly.

Splitting Rows in SQL Server 2005

riced2-Jul-09 7:09

riced

2-Jul-09 7:09

You don't need to create a text file. Access creates a .ldb file to hold locking information when the .mdb is opened. Just look for that. Smile | :)

There are some circumstances when Access does not create the .ldb file e.g. the directory that the .mdb is in is set to read only. But then Access will only allow one user to open the database so the second attempt will generate an error or exception.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

rajanandal30-Jun-09 19:04

rajanandal

30-Jun-09 19:04

hi,

Please help me..
i ve a table like this,
Sentby SentOn AcceptBy AcceptOn
1 2009-06-15 19:40:36.000 1 2009-06-15 19:40:36.753
32 2009-06-29 13:36:59.450 1 2009-06-30 12:25:45.303
1 2009-06-15 19:40:36.000 1 2009-06-15 19:40:36.000
1 2009-06-16 13:19:34.693 1 2009-06-16 13:19:34.693
1 2009-06-15 19:40:36.000 1 2009-06-15 19:40:36.000
2 2009-06-15 19:40:36.000 1 2009-06-16 19:40:36.000

and i need like this
Sentby SentOn
1 2009-06-15 19:40:36.000
32 2009-06-29 13:36:59.450
1 2009-06-30 12:25:45.303
1 2009-06-15 19:40:36.000
1 2009-06-16 13:19:34.693
1 2009-06-15 19:40:36.000
2 2009-06-15 19:40:36.000
1 2009-06-16 19:40:36.000
here in 2nd & 6th row ,Sentby and Accept by Columns are different so i need that in next row.

Nothing is Impossible. Keep always Smiling... Smile | :)

Re: Splitting Rows in SQL Server 2005 [modified]

Niladri_Biswas30-Jun-09 20:02

Re: Splitting Rows in SQL Server 2005

30-Jun-09 20:02

Try this(Assuming that the original table name is tblSENTBYSENTON)

ALTER PROCEDURE dbo.GETRECORDS
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   -- VARIABLE DECLARATION
	DECLARE @SENTBY INT
	DECLARE @SENTON DATETIME
	DECLARE @ACCEPTBY INT
	DECLARE @ACCEPTON DATETIME

	DECLARE @NEWTBLSENTBYSENTON TABLE 
	(
		SENTBY INT,
		SENTON DATETIME
	)

   -- STEP 1: DECLARE A CURSOR
	DECLARE MYCURSOR CURSOR FOR

		SELECT    SENTBY,SENTON,ACCEPTBY,ACCEPTON
		FROM      TBLSENTBYSENTON

	-- STEP 2: OPEN THE CURSOR
	OPEN MYCURSOR 

	FETCH MYCURSOR INTO @SENTBY,@SENTON,@ACCEPTBY,@ACCEPTON

	-- STEP 3: START THE LOGIC

	WHILE @@Fetch_Status = 0

	BEGIN

		-- STEP 4: INSERT RECORDS INTO TABLE @NEWTBLSENTBYSENTON
		--		   BASED ON THE LOGIC PROVIDED

			IF(@SENTBY = @ACCEPTBY)

			BEGIN
				INSERT INTO @NEWTBLSENTBYSENTON(SENTBY,SENTON)
				VALUES(@SENTBY,@SENTON)
			END

			ELSE

			BEGIN

				INSERT INTO @NEWTBLSENTBYSENTON(SENTBY,SENTON)
				VALUES(@SENTBY,@SENTON)

				INSERT INTO @NEWTBLSENTBYSENTON(SENTBY,SENTON)
				VALUES(@ACCEPTBY,@ACCEPTON)
			END

	
	-- STEP 5: GET THE NEXT RECORD
	FETCH MYCURSOR INTO @SENTBY,@SENTON,@ACCEPTBY,@ACCEPTON           

   END

	--STEP 6: CLOSE THE CURSOR
	CLOSE MYCURSOR

	--STEP 7: DEALLOCATE THE CURSOR
	DEALLOCATE MYCURSOR
	
	SELECT * FROM @NEWTBLSENTBYSENTON
END

With the same input, I got the same output:

Sentby		SentOn 
1	2009-06-15 19:40:36.000	
32	2009-06-29 13:36:59.450	
1	2009-06-30 12:25:45.303
1	2009-06-15 19:40:36.000	
1	2009-06-16 13:19:34.693	
1	2009-06-15 19:40:36.000	
2	2009-06-15 19:40:36.000	
1	2009-06-16 19:40:36.000

Hope this helps.
Smile | :)

Niladri Biswas

modified on Wednesday, July 1, 2009 2:11 AM

rajanandal30-Jun-09 20:32

rajanandal

30-Jun-09 20:32

Thanks for reply....
but i need a same result in select query without using Cursor,temptable,looping etc.,
because superior says it is a time consuming process.

Nothing is Impossible. Keep always Smiling... Smile | :)

Re: Splitting Rows in SQL Server 2005 [modified]

Niladri_Biswas30-Jun-09 20:45