Hi All
In My application when i want to work with sqldatasource for updation and delteion tasks it is working properly when all the columns consists the data. If any of the column consists the null values its not updating and deleting. Advices are needed.
Thank u
Baba
I've gotten conflicting info about this in the past so I thought I'd try to get clarification.
When a record is deleted, I'm sure it fires the delete trigger. Does it also fire the update trigger?
Thanks
How can we capture the last inserted, updated or deleted ID from the database ?
View 4 Replies View RelatedHi I am using sql server 2005 express and would like to keep all my fields from being both updated and deleted.
In other words, once I create a new record, I would like to have it protected from being deleted and I dont want the field values to be updated/changed from the values initially entered. Is there a way to this without running triggers or changing database permissions and user roles?
I tried making the database read-only, but then of course i cant add new records.
Thanks
HI, I am wondering if it is possible to retreive this information without using row count transform. Can I get the # of rows inserted/updated or deleted by destination from the log?
Thank you,
Ccote
I am having a hard time understanding triggers. My goal is to put a trigger on table x where records are UPDATED or DELETED. When this trigger fires I need to take the record ID and put the ID modified record into table y with the date modified. so basically logging the recordid changed with the getDate()
I don't quite understand how to get the rowid of the modified record.
Been trying to come up with a query to filter-down my sample set intodistinct records. For instance, lets say column1 is a sample set,column2 is the parameter, and column3 is a name and column4 is a type(lets also say there is a fifth column (id) that is an id). What Ineed is one record per type per sample only if type is given, if not,then return that record as well.I've used a subquery to get as close to the desired query is aspossible:select * from table1where id in (select min(id) from table1where column1="A"group by column1, column2)Here's an example of all data for sample "A":1 2 3 4----------A 1 X PA 1 Y PA 1 Z PA 2 WA 3 WA 4 T PA 5 U PA 6 V PA 7 TA 7 UA 7 VI want output :1 2 3-------A 1 X PA 2 WA 3 WA 4 T PA 5 U PA 6 V PA 7 TA 7 UA 7 VExcept the above query will exclude the last two records becausecolumn3 is not 'grouped by'.Basically I need to reduce any 'range' of records per sample (columna) where column4 is not null (ie = 'P'), to only one record andkeeping all others. Thanks in advance:-B
View 6 Replies View RelatedHi,I have an updatable DataGrid linked to a SQLDataSource in a web site developed using VS2005. Update works fine unless a value in the existing row is Null. Only one column in the database allows nulls.Putting a debug stop in the SqlDataSource1_Updating event, I checked the parameter value in the Immediate window and got the following result:?e.Command.Parameters(16){System.Data.SqlClient.SqlParameter}System.Data.SqlClient.SqlParameter: {System.Data.SqlClient.SqlParameter}DbType: Int32 {11}Direction: Input {1}IsNullable: FalseParameterName: "@original_FiresolveJobNo"Size: 0SourceColumn: ""SourceColumnNullMapping: FalseSourceVersion: Current {512}Value: NothingIs there a property I can set to generate a suitable Null check clause for the Update statement?Many Thanks,Keith.
View 3 Replies View Relatedi am getting the above error on my database i have 2 rows with the same info on and another 2 with the same info on. example:
ID username password
1 bob bob
1 bob bob
1 john john
1 john john
I know this is a fault with ms sql 2005 however how do i fix it?
Ive found this link which explains everything but how do i start a query. I tried clicking on new query and copying the code. What is table1 meant to be? the database is dbl.tbl_admin. It wont find my database.
Im not sure how to do it anyway.
I need to change it though as its my admin password and Ive given it out to web design companys
http://geekswithblogs.net/allensb/archive/2006/07/27/86484.aspx
Can some 1 read the above page and give me full instructions, I dont know what im doing thanks
info@uktattoostudios.co.uk
I have not yet succeeded in getting an aswer to this in a previous post, so I'll try again and rephrase the question
I have 2 fields, 1 called 'Created' the other 'Updated'
I would like to create a function, user procedure ,whatever in SQL something along the lines of...
FUNCTION myUID()
RETURN Date(Today)+Time(Now)+USERNAME
END FUNCTION
so that the value returned by the above pseudo code is something like '20080526T21:01:05.620SamL' where the date part is in yyyy0m0d format
I have got this working fine for the default 'Created' field but the calculated (and persisted) field comes up with something about being non-deterministic and refuses to play
Clearly something along these lines is a very straightforward requirement (I have it working fine in my 20+ year old database) , but have spent a week trying to get an answer (even from this forum) without success, so would be grateful for any help that you can provide me with to get round this barrier
Hi -
apologies if this is not the right forum for this - I've searched a bit and this seems to be the best fit.
I have the following problem: I want to update records in a table that fit certain criteria. The way the insert logic works make sure that there will always be only one record that fits the criteria and I'd like to get the ID value of that record once the update went through. So here is what I tried:
1 UPDATE Timeslot
2 SET StartTime = @StartTime, EndTime = @EndTime
3 WHERE (ProfessionalID = @ProfessionalID) AND (ProviderLocationID = @ProviderLocationID) AND (RequestID IS NULL) AND (StartTime > @StartTime) AND
4 (EndTime < @EndTime);
5 SELECT SCOPE_IDENTITY()
My hope was that the select scope_identity would return the Timeslot ID of the row that was affected, but it doesn't. How do I get that row?
Thanks!!!
Oliver
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
View 8 Replies View Relatedi am using sql server 2008 developer edition on in my desktop pc.i created a database in which there's a table that contains a column named "images" with a data type image for storing images in database.And all is working very fine No issue in this.
the problem arise after i copied the .mdf and .ldf files from my desktop to my laptop which also have same sql server 2008 developer edition.
Coming to the main issue "is I am not able to To save ,update images in that table from my laptop.i tried the best of what i could do but unsuccessful. i formatted my entire laptop as i thought of;basically the same thing works flawless in my desktop but not in my laptop.
I have some set of records in my table.
The same set of records will be updated often. Now I have a column as "lastupdated"
While i am displaying the records in a datagrid, The LAST UPDATED record should only be displayed.
Means, the recently updated records should be displayed in datagrid.
Pls give me the sql code / i am also in need of a Stored procedure for this.
I am working in SQL 2005
I want to build an windows application by using a visual C# to Notify the user that his data in the database had been changed ..such like "New Message In Your Mail Box Alert"..So I need to know if there is way that to let the SQL Server send a notify (just like Trigger) ..
View 1 Replies View RelatedDoes anyone know how to get the indentity of the last updated record in a table. I need it for a cascading trigger.
Thank you,
Jody
I am trying to write a trigger that, on an update, will check to see if more than one record is being updated. If more than one record is being updated, rollback the transaction and print a message. If not, then commit the transaction and print an OK message.
My problem is I am having troubles writing this trigger. I will post my SQL trigger code so far.
I am new with Triggers, so any help or advice is appreciated.
Code:
CREATE TRIGGER trgPaperCheck
ON BOOK
AFTER UPDATE
AS
IF UPDATE(Paperback)
BEGIN
DECLARE @count AS INT
SELECT @count = COUNT(Paperback)
FROM Book
IF @count > 1
BEGIN
PRINT('Only one Book record can be updated at a time!')
ROLLBACK TRANSACTION
END
ELSE IF @count =1
BEGIN
PRINT('Paperback Updated')
COMMIT TRANSACTION
END
END
Hi,
I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record,
is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Thanks a lot.
First of all, let me apoligize if this is a stupid question, but just a few days ago I decided to change my FE/BE database from Ms Access to SQL Server Express. Now I have a FE in ACC2002 and a BE in SQL SERVER (the two connected via ODBC). Anyway, this is my question:
When everything was in Access I had a form retreiving a series of records based on a filter - for understanding purpose let's say that a field must have been 'Not Null'. The operator then filled that field with data. With Access everything was static, and the operator could see all the records, the ones already changed and the ones not changed. With SQL now: everytime the operator fills in the field, the record changes to #Deleted. I assume that that is happening because the query - someway - is "alive" and is "filtering data alive"... or is updating records instantly. Just to make things more clear: is the record changed immediately so it is not considered part of the filtered data by the query, thus being change to #Deleted? What could I do to prevent this?
Thanks a lot for your help.
Is there a way to tell who or what process deleted a record in a SQL table.I know you can setup triggers or Profiler - but is there a way to see how arecord was deleted if you DO NOT have a trigger or Profiler already running?This is SQL 2000 Standard, sp3a with default options and configuration.Thanks
View 2 Replies View RelatedHi
I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation.
On the event handler I am usin select ID,Name from my [table];
this will return all record from database.
I just want to get the record which is inserted or updated.
Can u help me in that.
Take care
Bye
I have a table that contains a field LastUpdated. This field should contains the date of the last update of the record.
Is there a way to auto-update this field value to the getdate() value each time the record is "touched" by an UPDATE sql command?
Thanks in advance,
Joannes
I would like to show when leads updated last their records in database. An automated report that tells me when the last date was that the leads updated an entry, only 1 entry per lead.
select Lead,LastUpdated from dbo.KPITbl
I have a table with data that looks like this:
Lead LastUpdated
----------- -----------------------
JOHN SMITH 2008-03-26 08:45:00
JOHN SMITH 2008-03-20 09:33:00
MEG RYAN 2008-02-21 16:16:00
JOHN SMITH 2008-02-21 16:19:00
MEG RYAN 2008-02-21 16:22:00
JOHN SMITH 2008-03-28 16:10:00
JOHN SMITH 2008-03-28 08:49:00
JOHN SMITH 2008-03-23 19:23:00
MARK MCRAE 2008-03-27 03:12:00
MARK MCRAE 2008-03-26 08:48:00
MARK MCRAE 2008-03-26 08:46:00
JOHN SMITH 2008-03-26 08:47:00
JOHN SMITH 2008-03-26 08:48:00
ALLAN WHITE 2008-03-26 08:43:00
ALLAN WHITE 2008-03-26 08:40:00
JOHN SMITH 2008-03-26 08:48:00
Thank you appreciate it.
Hi,
I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record,
is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Thanks a lot.
I am using a drop down list box to select values from an SQL 2000 DB. I have put a record into the table which says"...Select Item"I used the dots so that It will apear at the top of the list and therefore display at startup. I also have a gridview to allow users to edit and delete from the same table.I am worried that a user may accidently delete the "...Select Item" row from the table.Can anyone think of a way round this? Is it possible with SQL Server 2000 to stop a single record from being deleted?Any help would be most appreciated.ThanksPaul
View 4 Replies View RelatedI have a sql server database that she deleted a record with ID as 2873. I would like to try to add this record manually, but the primary key can't be edit. How can i add this record with the same ID as she deleted?
Many thanks.
Hi friends
I have a bit problem here
Just I want to get back all deleted record of database
How do I perform this task?
If It is possible then plz help me out?
Thanks in Advance
Khan
Hi,
i have deleted 5 to 10 records in a table. is there any way to retrieve the deleted records?
please help me in this regards.
Thanks in Advance.
M.ArulMani
I need to create a table that only allows fields to be updated if the field value is NULL. Is there anyway to configure a table in this manner?
In the example below the record will be created with a serial number and verification ready flag set to true. Other processes down stream will update the remaining fields as needed but should not update fields that have already been initialized.
Table Definition
USE [DB_AUTOMATED_PACKAGING_SYSTEM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_LABEL_VERIFICATION](
[SerialNumber] [varchar](50) NOT NULL,
[VerificationReady] [int] NOT NULL,
[VoidLabelImage] [image] NULL,
[VerifiedBy] [varchar](50) NULL,
[VerifiedDate] [datetime] NULL,
[Verified] [int] NULL,
CONSTRAINT [PK_TBL_LABEL_VERIFICATION] PRIMARY KEY CLUSTERED
(
[SerialNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
We are getting data feed from Oracle database in our project. Everyday we will need to track if any rows got inserted/updated/deleted in the source and get that update right into our data warehouse.
Currently we are taking a dump of the required table (as it is) to our staging DB and comparing it with previous day data to track the changes (column by column comparison). This approach is working currently but has performance bottleneck. There is no tracking column (eg. last modified date or time) in source that will give us any idea of what got changed. Also there is no identity key or primary key in the source data.
Is there a way in SQL Server to get that inserted/updated records only instead of comparing column by column to track the changes?
Im using a trigger to check updates on particular table and execute a email. it works but it doesnt show the right record
im looking into one table called SiteInfo.
here is my code
Im using sql 2005, can someone look at my code or the select statement.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER TTSUpdate
ON SiteInfo
FOR UPDATE
AS
declare @SiteID varchar(10)
declare @Body2 varchar(2000)
declare @Sitename varchar(50)
declare @TTSCreate varchar(30)
declare @TTSCreator varchar(50)
declare @Subject2 varchar (100)
SELECT @SiteID = SiteID,@Sitename = AccountName,@TTSCreator = TTSOwner,@TTSCreate = TTSCreatedDate
from SiteInfo
SET @Body2 = 'New TTS site created: ' + @Sitename + ' With TTS Site ID:' + @SiteID + ' TTS was created on: ' + @TTSCreate + ' By:' + @TTSCreator
SET @subject2 = 'New TTS site created: ' + @Sitename
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TTSAdmin',
@recipients = 'email address here',
@subject = @subject2,
@body = @body2
GO
Hi,
I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.
These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.
It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.
What are the options available for me to be able to query and differentiate the 'deleted' records ?
Thank you in advance,
Bertrand Larsy
I have an event:
Private Sub SqlDataSourceIncome_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourceIncome.Deleted
Dim command As SqlClient.SqlCommand
command = e.Command
If command.Parameters("@nReturnCode").Value <> 0 Then
DROPDEAD()
End If
That fires from:
<DeleteParameters>
<asp:Parameter Name="nDeletebyId" Type="Int64" />
<asp:Parameter Name="nOtherId" Type="Int64" />
<asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" />
<asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" />
</DeleteParameters>
End Sub
When I:
GridViewIncome.DeleteRow(GridViewIncome.SelectedRow.RowIndex)
But nReturnCode is ALWAYS NULL... I even did a stored procedure that just:
ALTER PROCEDURE [dbo].[sp_nDeletebyId]
@nReturnCode bigint output,
@nReturnId bigint output AS
SET @nReturnCode = 0
SET @nReturnId = 0
And STILL got nothing but the NULLS... the insert & update stuff works fine, with identical code... it's just the DELETED event that I can't seem to knock. Has anyone seen this before? The above sample stored proc did return 0 when executed one the server...
and, BTW, the row is deleted!
Chip Kigar