|
Ah! I didn't realise that you could specify what columns fired the trigger.
This is the current trigger definition:
CREATE TRIGGER [dbo].[trgProductCategoriesUPDATE]
ON [dbo].[tblProductCategories]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE tblProductCategories
SET [DateModified] = GetDate(),
[AwaitingSync] = 1
WHERE [ID] = (SELECT [ID] FROM INSERTED)
END
Which fires when any column is updated.
Are you saying that it should look something like this:
CREATE TRIGGER [dbo].[trgProductCategoriesUPDATE]
ON [dbo].[tblProductCategories]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF UPDATE(CategoryName)
BEGIN
UPDATE tblProductCategories
SET [DateModified] = GetDate(),
[AwaitingSync] = 1
WHERE [ID] = (SELECT [ID] FROM INSERTED)
END
END
Thanks 
|
|
|
|
|
Yes that is correct. I'm not familiar with the SQL Server flavour of doing things, but when defining a trigger you can specify a list of columns as opposed to the entire table, that the trigger is fired on. Glad that has helped you out.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Is is my first time i touching SQL server.
I'm using sql server 2008 express.
I created simple table using Management studio - and i connected to the table from C# code using dataGridView.
I have small simple question ...
1. How can i define a new SP that can take parameters from C# code and how can i call it from C# code ??
2. I add some new raw from the dataGridView - and i don't know why they are not save in the database ( i try to call 'AcceptChanges' - but still this is not working ) .
Thanks for the help.
modified on Monday, October 25, 2010 8:43 AM
|
|
|
|
|
Yanshof wrote: dataGridView
Yuck.
If all you're doing is seeing that it can be done, then use a DataAdapter's Fill and Update methods to move data between the Grid and the database. But it only works for very simple data and you will soon find that that technique is not appropriate for real applications.
For one thing, Grids are generally not a proper way to get user input -- can you imagine an eCommerce site where you placed orders by filling in columns in a grid? No, you should define a form for entering and editing data. You can use a Grid with a subset of the data to allow the user to locate the record he wishes to edit, but never edit within a Grid.
For actual applications you will need a Data Access Layer. A layered application makes swapping parts in and out easier. For instance, at some future time you may want the client to access the database via a Web Service -- if your client is too-tightly bound to your database, that will be much more difficult.
"A stitch in time saves nine." Learn to use proper data access techniques now, before you really need them.
Here[^]'s an article I wrote on one of my data access techniques.
|
|
|
|
|
PIEBALDconsult wrote: never edit within a Grid.
And I would say to that: Never say "never". Without disputing the general thrust of your post, there are occassions when it makes perfect sennse to edit directly wihtin a grid, particularly if the underlying dataset has only a small number of "simple" fields (strings of limited length, or enum types from which the user has to choose for example.) One can (and should) still separate out the data access code into its own class, or layer, but as far as the user is cnncerned the editing can take place right there in the grid.
It all depends on the exact situation, of course, but solutions should fit the problem; one shouldn't force a problem to fit a pre-determined solution.
Just my tuppenceworth.
|
|
|
|
|
In a couple of places I have allowed editing with a Grid, but only out of laziness to allow a knowledgable administrator to configure certain things, never for an average user to muck around with.
|
|
|
|
|
NeverHeardOfMe wrote: And I would say to that: Never say "never"
I have 1 very rigid rule, if you need to edit more than 1 field you will NEVER get a grid approved - go back and do the job properly. If you have only 1 field to edit I want a 3 page justification for not popping a dialog.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why is there such an objection to (simple) editing within a grid? Suppose you have a CMS used by the client to set up his shop. On one page you allow him to define "sections" - eg, "Toys", "Clothes", "Tools".. whatever. So what would be so wrong in displaying a grid of said sections and allowing him to edit their names there and then, and even add a new section via the footer row? I don't want a dialog for that, and I don't want to load a new page just to do this simple task, nor load the entire "section page" listing its name, sub-sections and/or items within it just to do it either.
The trouble with dialogs in web-apps is that they come at a price - either you need the overhead of fancy javascript to create a modal one that also updates the calling page when closed (and, in the case of adding a new section will maybe need to redirect elsewhere too), or you run the risk of pop-up blockers getting in the way, or it openeing in a new tab and the client wondering what's happened.
I don't see the necessity to take a strict "never edit in a griod" attitude. I quite accept it should be used only in limited cases, but not "never".
|
|
|
|
|
NeverHeardOfMe wrote: The trouble with dialogs in web-apps
I was pretty sure the OP was about a datagridview, and yes web apps have a different set of rules.
My primary problem is that most junior devs want to do everything in a grid, which is fine when there are VERY limited interactions required. As soon as the interaction goes past the trivial they find the limitations of DGV editing.
I am finding with Silverlight I can treat the web much like a winforms app, pop a dialog whenever I need to let the user edit the data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
NeverHeardOfMe wrote: web-apps
Oh, that's your problem -- they're rubbish.
|
|
|
|
|
I have problem inserting datetime values into my SQL Server 2008 R2.
The server is capable of handling ISO 8601 format, but If I send them as parameters, the SQL Server/OLE DB provicer, seems to be less tolerant on the timestamp string format.
I use OLE DB from C++ and would like to make my insert statement parametrized
INSERT INTO BATCH VALUES (?, ?, ?, ?....
When I specify timestamp values on the ISO8600 format, "2010-10-23T19:41:56.002+02:00", I get errors.
When enter the same timestamp format directly in the statement, I don't get errors
INSERT INTO BATCH VALUES ('2010-10-23T19:41:56.002+02:00', ?, ?, ?....
I have tried two different OLE DB providers. "Microsoft SQL Server Native Client 10.0" and "Microsoft OLE DB Provider for SQL Server"
They both behave slightly differently.
When sending the timestamp values as part of the SQL INSET, both providers accepts the following formats
2010-10-23 19:41:58.000
2010-10-23T19:41:57.001
2010-10-23 19:41:56.002+02:00
2010-10-23T19:41:55.003+02:00
When sending timestamp as parameter:
2010-10-21 13:44:59.092 Is accepted by both
2010-10-21T13:44:57.092 Is rejected by Microsoft SQL Server Native Client 10.0 and accepted by the other
2010-10-23 19:41:56.002+02:00 Rejected by both providers
2010-10-23T19:41:55.003+02:00 Rejected by both providers
The both providers also report different errors
Microsoft OLE DB Provider for SQL Server reports "Conversion failed when converting date and/or time from character string" which I may understand.
The "Microsoft SQL Server Native Client 10.0" reports HRESULT=DB_S_ERRORSOCCURRED.
I would be glad if someone could explain these differences, especially why I can send the timestamp stirng litterally in the sql but not as a parameter.
|
|
|
|
|
Try a parameterized query. I do not know how it works with C++, it should be something like:
OleDbCommand cmd = new OleDbCommand("Insert .....");
cmd.Parameters.AddwithValue("ParameterName1", timeStampValue);
Note: you use here the timestamp value as such, you *must not* convert it to a string!
|
|
|
|
|
I am using Visual Studio 2010 Professional.
I have created a new SQL Server 2008 Database Project, locally applied and deployed a simple scheme and stored procedure. Everything looks as it should until I try to create the unit test by right clicking the stored procedure, is this functionality only supported in Premium and Ultimate?
thanks.
dotman1
|
|
|
|
|
hi all,
i develope a project using c# and sql server 2005, the problem is i want to make a column modifiable in sql server database, and another column to unmodifiable.
how i can set column property to modified or not modified in sql server 2005?.
thanks all
|
|
|
|
|
You are attempting to do this in the wrong place. A database is for storing and manipulating data. The state of the column is a UI issue not a database issue.
Change the properties of your list control to make it read only.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi
i know that, but i the column properties in the database to be unmodified,
in another database the property is exist, but in sql server i can't reach the property.
thanks
|
|
|
|
|
zead wrote: i know that
And yet you insist that SQL server supply this UI functionality!
zead wrote: in another database the property is exist
 What is the other database?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi
like pervasive database
|
|
|
|
|
Sorry I had not heard of pervasive. If they supply the functionality you need then use that database, expecting another vendor to match esoteric (and wrong) requirements is not reasonable.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi guys
I detached my database, working for months. after one week I decide to attach it agian. but, I ot the following error:
<br />
<br />
TITLE: Microsoft SQL Server Management Studio<br />
------------------------------<br />
<br />
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)<br />
<br />
For help, click: http:
<br />
------------------------------<br />
ADDITIONAL INFORMATION:<br />
<br />
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)<br />
<br />
------------------------------<br />
<br />
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Hostel.mdf'. (Microsoft SQL Server, Error: 5123)<br />
<br />
For help, click: http:
<br />
------------------------------<br />
BUTTONS:<br />
<br />
OK<br />
------------------------------<br />
<br />
Please help,
I went through many forums on the website but hasn't help me yet.
|
|
|
|
|
I sort out y problem.Righr click and then run as Administrator
|
|
|
|
|
somebody please help me....
i'm using vb.net 2008 and SQL Server 2008...
the case is :
--------------------------------------------------
if i have data in one column like :
ASD654-100-CCCEEE4444-200-
what query can be used to get "CCCEEE4444" ????
|
|
|
|
|
SELECT SUBSTRING('ASD654-100-CCCEEE4444-200-',12,10)
Instead of string value give column name.
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.
www.aktualiteti.com
|
|
|
|
|
it will not work.
coz i'll never know the starts index and the length of string.
coz the data is randomize.
example :
i want to get "ASD654" and "CCCEEE4444" from the data :
"ASD654-100-CCCEEE4444-200-"
or to get "QWEQWEQ" and "JJJLLL" and "PPPBBMMNN" from :
"QWEQWEQ-2000-JJJLLL-20-PPPBBMMNN-600-"
any idea? 
is there any query for looping?
|
|
|
|
|
Well, you have to use - as split char, by writting your own function then you can get result as you need.
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.
www.aktualiteti.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
