|
A trigger runs once at the end of any statement it's involved in, NOT once per row. The inserted virtual table contains every row touched.
I suggest something like:
ALTER TRIGGER CheckInOut_ValidatedTrigger
ON dbo.CheckInOut
FOR UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
IF NOT UPDATE( Validated ) RETURN
UPDATE Schedule
SET
CompletedDate = OD.CompletedDate,
CompletedOrderMainStatus =
CASE dbo.IsOrderCompleted( I.OrderNumber )
WHEN 0 THEN 3
ELSE 5
END
FROM
inserted I
INNER JOIN
OrderDetail OD
ON I.OrderNumber = OD.OrderNumber
WHERE
I.Validated = 1 AND
Schedule.OrderNumber = I.OrderNumberI've assumed that you have an OrderNumber column on your Schedule table.
|
|
|
|
|
heres how I solved it but I will change to use set method. A cursor was my meantime hack.
<small><br />
SET NOCOUNT ON<br />
<br />
<br />
DECLARE @OrderNumber varchar(10)<br />
DECLARE @ScheduleID bigint<br />
DECLARE @DispatcherID smallint<br />
DECLARE @ScheduleComplete bit<br />
<br />
DECLARE OrderCursor CURSOR FOR<br />
SELECT OrderNumber, ScheduleID, CheckOutDispatcherID, ScheduleComplete FROM inserted WHERE Validated = 1<br />
<br />
OPEN OrderCursor<br />
FETCH NEXT FROM OrderCursor INTO @OrderNumber, @ScheduleID, @DispatcherID, @ScheduleComplete<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
IF ( @ScheduleComplete IS NULL OR @ScheduleComplete = 0 )<br />
BEGIN<br />
UPDATE Schedule<br />
SET Attempts = Attempts + 1<br />
WHERE Schedule.ID = @ScheduleID<br />
END<br />
ELSE<br />
BEGIN<br />
UPDATE Schedule<br />
SET CompletedDate = GETDATE(),<br />
CompletedOrderMainStatus = ( CASE [Convert].IsOrderCompleted(@OrderNumber) WHEN 0 THEN 3 ELSE 5 END ),<br />
CompletedOrderStatus = ( CASE [Convert].IsOrderCompleted(@OrderNumber) WHEN 0 THEN 3 ELSE 8 END ),<br />
CompletedDispatcherID = (@DispatcherID)<br />
WHERE Schedule.ID = @ScheduleID<br />
END<br />
FETCH NEXT FROM OrderCursor INTO @OrderNumber, @ScheduleID, @DispatcherID, @ScheduleComplete<br />
END<br />
<br />
CLOSE OrderCursor<br />
DEALLOCATE OrderCursor<br />
<br />
SET NOCOUNT OFF<br />
I'm not an expert yet, but I play one at work. Yeah and here too.
|
|
|
|
|
 HELP!!!
I am trying to insert a row into an Access table using ADO.Net & C#. But it always give me an exception of "Syntax Error". But I don't understand, I am using the OleDbCommandBuilder to build the sql, why it has syntax error?
Please help me, I am almost crazy!!!!
Here is the table I want to insert records:
table name : Table1
fields:
ID: int, (AutoNumber, primary key)
SaveDate: Date/Time, default value = CDate() // this field is used to save the date&time when this row insert, so I don't want to write it in my code
field1: text(20)
field2: text(20)
field3: text(30)
Here is my code:
public bool Insert()<br />
{<br />
try<br />
{<br />
OleDbDataAdapter da = new OleDbDataAdapter(<br />
"select top 1 * from table1", <br />
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\\test.mdb;User Id=;Password=;Mode=ReadWrite");<br />
<br />
DataSet ds = new DataSet();<br />
da.Fill(ds, "table1");<br />
OleDbCommandBuilder bld = new OleDbCommandBuilder(da);<br />
<br />
DataRow r = ds.Tables[0].NewRow();<br />
for ( int i = 0; Table1[i].Field != null; i++ ) <br />
{<br />
if ( string.Compare(Table1[i].Field, "ID", true) != 0 &&<br />
string.Compare(Table1[i].Field, "SaveDate", true) != 0 ) <br />
{ <br />
r[Table1[i].Field] = Table1[i].Value;<br />
}<br />
}<br />
ds.Tables[0].Rows.Add(r);<br />
int naffected = da.Update(ds, "table1");<br />
da.Dispose();<br />
da = null;<br />
if ( naffected > 0 )<br />
return true;<br />
} <br />
catch ( Exception e ) <br />
{<br />
ShowMessage(e.Message);<br />
}<br />
return false;<br />
}
|
|
|
|
|
I know why now. Because in my table, I use a reserved word as my field name. Thank you for reading.
|
|
|
|
|
I think you can get away with that, but only if you enclose the column name in square brackets when referring to it in code.
Cheers,
Tom Archer
Inside C#,
Extending MFC Applications with the .NET Framework
It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson
|
|
|
|
|
Can anybody inform me of common asp bottlenecks or problems that would slow down a page? My project is starting to time out due to long processes. If anybody knows any common ways to speed up ASP server and the way it communicates with SQL server, please inform me. I have ran multiple SQL server traces on the back end that were captured by accessing the database thru the front end.
thanks
-kaht
|
|
|
|
|
Although I can't help you, when you get this information it would be most helpful if you would post a 10 common asp bottleneck rpt 
|
|
|
|
|
Lessee:
Try to do as much in a single batch as you can. This would involve accumulating changes in a recordset, then using UpdateBatch if your provider supports it, rather than using lots of single Update statements. See also adLockBatchOptimistic.
Try to return as little information as possible in your resultsets, for example, use SELECT Column1, Column2 rather than SELECT *. SELECT * also forces the database to read the syscolumns table every time to find out what the columns on the table are, rather than using a cached execution plan.
If you need many results that don't conform to the same schema, consider using multiple result sets from one batch rather than submitting each statement in its own batch. Use ADO's NextRecordset method to access multiple result sets (this is actually a lot easier in ADO.NET).
Learn to read the execution plans shown by Query Analyzer, which will show where your bottlenecks are in any given procedure. Indexes may help. SQL Profiler's Index Tuning Wizard can assist in suggesting where another index might be beneficial, or where the cost of maintaining an index is hurting performance.
Use the Recordset's CacheSize property to fetch more than one row at a time, or consider using a forward-only or client-side cursor, then disconnecting the recordset by setting its ActiveConnection property to Nothing (forcing ADO to get all the records).
|
|
|
|
|
thanks for the tips
-kaht
|
|
|
|
|
How do I set the format property of a field. ie YES/NO. I am getting errors.
Thanks
|
|
|
|
|
I create every day ACCESS file ,
by copynig from initial, rename and that write,
Every day.
but initial file takes size of 210 kilobytes,
by the year 60 Megabytes. It is much.
How to make smaller initial acces file ?
By excluding system tables?(How to do it?)
Compressing?
By excluding unneed fuctionality?
I use OLE DB templates.
and JET ACCESS 4.0 driver.
Thanks you.
|
|
|
|
|
Hi !
I'm using an Access 2000 database through ODBC, using only standard SQL requests. In my table, I have an autoincrement field. After adding a new record, I need to know the value of the autoincrement field. To do that, I get the record which has the higher number in the autoincrement field (because it is the last added one), and read this field value.
This works fine in a monouser environnement. But, if two records are added almost simultaneously, there is a risk that the last value of the autoincrement field is not the value of the record I just added. To solve this, I tried to use those SQL commands :
BEGIN TRANSACTION
COMMIT TRANSACTION
But it seems that Access does not handle these SQL commands.
How would you solve my problem ?
Thank you for your help
Jerome
|
|
|
|
|
Switch to a proper database (tee hee!)
But seriously - Access doesn't support transactions at all (AFAIK), but can be used as a front end to SQLServer, and therefore MSDE (free SQL Server desktop engine). You may be able to cobble together something using that sort of rig, but will probably dstruggle using Access alone
Of course, my personal favourite would be bin Access completely and use Oracle (or eve MySQL)
Apparently the problem is caused by Access not actually being a relational database, but rather an ISAM db with relational addons
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Actually, it does support transactions - I've written apps which depend on it in my pre-SQLServer days. Don't let your bias for Oracle show quite so much.
I'd ask whether or not stuff like autocommit is turned off (there's an SQLSetConnectAttr setting).
Steve S
|
|
|
|
|
Hello All,
I have some data in the Excel file, and i want to get it into sql table.
How can i do that? Can i use macro ? if Yes than how should i use the macro?
Thanking in advance.
Prasad.
|
|
|
|
|
The easiest way to transfer data accross is to create a DTS package: start SQL Enterprise Manager, go to the Data Transformation Services node, right click on "Local Packages" and select "New package".
By drag and drop, you can create 2 connections objects, 1 Excel object and 1 SQL Server object
(you can create other types of connections as well, including ODBC, csv file, Oracle, Sybase, etc...)
Then drag and drop a "Transform Data Task" object, set its source to Excel and its target to SQLServer.
Right-click on the "Transform Data Task" object and select properties. You can configure how you want the data to be mapped, it even creates a table in SQL Server if you want. It is quite easy to use, virtually no code to write.
Save your package, execute it and you're done. If you want to re-use this package it is quite handy. You can add all sorts of other tasks/macro that run before or after the data pump. You can also specify the order in which each step should be executed.
 Chris
|
|
|
|
|
This is beyond me....
Look at the photo of my view please...
https://www.readysecure2.com/users/polajenkocom/images/combine.jpg
I want to get all records with a CreatedDate that is within the past 7 days. Can anyone help me do this?
Thanks
|
|
|
|
|
try adding DateDiff(dd,CreatedDate,GetDate()) <= 7 to your where clause.
onwards and upwards...
|
|
|
|
|
I got it with
SELECT 'Announcement' as ContentType, Title, ItemID, ModuleID, CreatedByUser, CreatedDate, URL,
Users.UserID, Users.FirstName, Users.LastName
FROM Announcements
LEFT OUTER JOIN
Users ON Announcements.CreatedByUser = Users.UserID
WHERE DATEADD(d, 0, DATEDIFF(d, 0, CreatedDate)) >= DATEADD(d, -7, DATEDIFF(d, 0, GETDATE()))
UNION
SELECT 'Document' as ContentType, Title, ItemID, ModuleID, CreatedByUser, CreatedDate , URL,
Users.UserID, Users.FirstName, Users.LastName
FROM Documents
LEFT OUTER JOIN
Users ON Documents.CreatedByUser = Users.UserID
WHERE DATEADD(d, 0, DATEDIFF(d, 0, CreatedDate)) >= DATEADD(d, -7, DATEDIFF(d, 0, GETDATE()))
UNION
SELECT 'Links' as ContentType, Title, ItemID, ModuleID, CreatedByUser, CreatedDate , URL,
Users.UserID, Users.FirstName, Users.LastName
FROM Links
LEFT OUTER JOIN
Users ON Links.CreatedByUser = Users.UserID
WHERE DATEADD(d, 0, DATEDIFF(d, 0, CreatedDate)) >= DATEADD(d, -7, DATEDIFF(d, 0, GETDATE()))
ORDER BY CreatedDate DESC
|
|
|
|
|
Using a DateAdd of 0 is a waste. All you need is one DateDiff. Your where clause should look like this:
where DateDiff(dd,CreatedDate,GetDate()) <= 7
to show rows created in the past 7 days.
onwards and upwards...
|
|
|
|
|
I’m trying to extract the PK’s and FK’s of a table given it’s name.
I can get as far as confirming that a table has a PK by obtaining that PK’s entry in the sysindexes table, but now my problem is that I can’t seem to figure out what column in that table actually represents the PK.
In the sysindexes table it merely specifies that this is a primary key and this is the parent table, but not which column in that table.
What I really want, given a table name is:
myprimaryKeyColumnName[s]
myForeignKeyColumnName[s] & it's associated Table[s]
btw, I know it's in the sys* tables, but I'm just missing something and don't have my systables poster at home. 
Cheers,
Simon
sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
article :: animation mechanics in SVG picture :: my first abstract photo
|
|
|
|
|
Try sp_fkeys (tablename) to display the foriegn keys. Or, if you want to dig it out the hard way, try something like:
select
Object_Name(fkeyid) + '.' + Col_Name(fkeyid,fkey),
keyno
from
sysforeignkeys
where
rkeyid = Object_ID('YourPrimaryTableName')
order by
1, 2
onwards and upwards...
|
|
|
|
|
Try using ADO's Connection.OpenSchema method - haven't checked but think will give you the nec. info
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
|
I am using SQL Server 2000. I ran into a situation where I needed to increment a revision. The revision could be up to two letters in length. The code below works great. This is just for an example to anyone who may need it.
Check This out:
declare @r varchar(2)
declare @MaxRevLetter INT
declare @seriesLetter1 VARCHAR(1)
declare @seriesLetter2 VARCHAR(1)
declare @NewRevision VARCHAR(2)
-- SET Revision Letter(s) to Check
SET @r = 'CX'
SET @MaxRevLetter = ASCII('X')
-- test for length. If the Revision is only one letter then it is easy to handel.
IF LEN(@r) > 0 AND LEN(@r) < 2 BEGIN
-- if the revision letter is a Z then we can not increment it using the following method
-- so set it.
IF (@r != 'Z') BEGIN
IF ASCII(LTRIM(RTRIM(@r))) <> @MaxRevLetter BEGIN
SET @NewRevision = CHAR(ASCII(@r)+1)
END
END
ELSE BEGIN
SET @NewRevision = 'X'
END
END
ELSE BEGIN
-- get the letters in the Revision
SET @seriesLetter1 = SUBSTRING(@r,0,LEN(@r) )
SET @seriesLetter2 = SUBSTRING(@r,2,LEN(@r) )
-- because we know that if there is a Z the letter must be set and can not be
-- incremented. We need to test for a Z.
IF (@seriesLetter1 + @seriesLetter2 != 'ZZ' AND
@seriesLetter1 + @seriesLetter2 != 'ZX' AND
@seriesLetter1 + @seriesLetter2 != 'XZ' AND
@seriesLetter1 + @seriesLetter2 != 'XX') BEGIN
-- Ok if the first letter is NOT an X and the second letter is not an X
-- increment the second letter so AB would be AC
IF (ASCII(@seriesLetter2) <> @MaxRevLetter) BEGIN
SET @seriesLetter2 = CHAR(ASCII(@seriesLetter2)+1)
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
ELSE BEGIN
-- ok the second letter was an X Ex: AX so we need to increment the first letter
-- and set the second letter back. AX would be BA
SET @seriesLetter1 = CHAR(ASCII(@seriesLetter1)+1)
SET @seriesLetter2 = 'A'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
END
ELSE BEGIN
-- ok there is some combo of X and Z in the string. so set the value
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'X'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'X') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'A'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'Z'
SET @seriesLetter2 = 'X'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET NewRevision = 'The Revision XX is the last revision possable.'
END
END
END
enjoy.
Let me know if you have any questions
Will
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
