I 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.
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 RelatedI 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 RelatedHi 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
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
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 uBaba
View 4 Replies View RelatedI'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
I need to create a trigger to meet following conditions.
When parent record is deleted from UI record becomes inactive in table. i need to create a trigger when this happens.
When parent record is deleted child records needs to be inactivated in table.
hi friends.,i have some question regarding sql server 2000. pls help me... 'sa' user in sql server 2000 can be deleted or not ??if i install sql server 2000 with windows authentication; then will 'sa' user be created or not ? while i have my sql server database on some other pc (2000 professional os) and i installed my application on a different pc; then i have to access the pc in which my database resides; from installed pc location . why is that so ? with out accessing the pc where sql server is installed; my application is unalble to access/connect database....pls help methanks in advance//..
View 3 Replies View Relatedcan anybody tell me with an example how to use Inserted and Deleted in Sql Server 2005
View 7 Replies View Related
The permissions granted to user 'xxxxyyy' are insufficient for performing this operation. When a user is deleted and then readded to active directory. Reporting services returns the insufficient permissions granted error. In dbo.users in the sql database there are 2 entries for the deleted user one with UserType 0 and another with UserType 1. How do you clean up reporting services or sql to allow the reciently re-added user to re-connect to reporting services. We use the MY-Reports option of reporting services but cannot delete the home directory for this user either in report Mgr. The same error is returned for this action.
Hi,
Got a question. I have a script that create a table-- I need Col1, Col2 and Col3 to have PrimaryKey. How do u do. Here is the script
CREATE TABLE [dbo].[PROC_DATA] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Col2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Col3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date1] [datetime] NOT NULL ,
[Recod1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [DRI_CHASE_DATA_GRP1]
GO
Josephine
DATETIME AS PrimaryKey?
Hi Dears!
declare @dtpk smalldatetime
select @dtpk=getdate()
print @dtpk
in the above, i m using @dtpk as a primary key value in a
table to keep a track of some events that are occuring with
a difference of one second. the above method gives me value
to store in the Primary key Filed (EventTime smalldatetime)
in the form "Dec 1 2005 7:57PM" where my requirement is
to save it in the form "Dec 1 2005 7:57:12.03PM" so as to
make it unique for the primary key field(EventTime
smalldatetime). how to get required datetime format with
the help of above statements to store as primary key in
EventTime field of type smalldatetime.
THNX IN ADAVNCE
Hello
I've taken over a database where for many tables the postcode field (equivilent to the US Zip Code) is the obvious primary key. [Mapping postcodes to distict or various types of area for statistical analysis]. However the people who set up the database have continually used an auto generated number for the primary key.
There are never any duplicate postcodes in the various tables, so my question is what is the advantage of either using or not using the postcodes as primary keys.
I've a question. I've an old db here with no Primary keys nearly but a lot of identity's set, one for each table (nearly). I'm wondering, could something somehow go wrong with some sp's or anything if I make primary keys of these identities? Question why I'm asking is that we're takinging about doing replication but can't since there are primary keys lacking but we dont dare to change ém into primary keys if something might go wrong since we dont have knowledge about that.
Thanks for help.
I want to know if this primary key behavior is unique to Access or if it is THE WAY that all industry-standard primary keys work. Thus, I hope not to get too messed up on my SQL Server 2005 project--or any other SQL project.
In Access, I set up a table 'tbl_Gen_Admin_sto' to store general administration expenses. This is a budget. So, there can be at most one entry per budget_year / cost_center / account_number / month. If there are two or more entries, the data is messed up, something is wrong.
Therefore, I defined a muliple-field primary key for tbl_Gen_Admin_sto. If I make a programming mistake and enter data multiple times, the primary key feature will stop my program from entering the same data multiple times.
As I often do, I copy the data into another table, in this case tbl_Gen_Admin_tmp for manipulation in a form. (I.e., I connect a form to tbl_Gen_Admin_tmp.) The tmp version is a pivoted version of the sto table. So, in sto, I have one amount field for each month. In tmp, I have no month field, but do have one amount field for every month of the year (12 amount fields).
The transfer of data to tmp went fine. The user adds, changes, and deletes records in tmp through the form. When the user is done and wants to save the changes (and not discard changes), my program deletes the selected records from sto, then inserts the records from tmp.
But lo! Access was not accepting the new records because (error message) "key violations". The delete went fine. But the insert would not take.
I deleted the multiple-field primary key index in sto. Then I created a multiple-field regular index with no duplicates allowed. Now, sto accepts the insert.
I thought that when I deleted the records, the primary key field is gone--zzzzpft! But aparently Access is keeping track of all primary keys. Even though I deleted the record, for all eternity I will never ever be able to re-insert with the same primary key.
Here is where I need your help: Am I going to run into this same primary key behavior in SQL Server 2005? If so, is it because I have encountered the fundamental definition of primary key?
(I hope you don't beat me up too much for abusing primary keys.)
Hi, I having a problem with my query...
I want to copy data from 4 different database to 1 database... but if the destination database have already the same Primary Key the copying stops/terminated and not copying others that is not yet in the destination...
I don't have knowledge in T-SQL like IF...ELSE
my database is SQL Server 2000 but i'm using SQL 2005 Express Management for the query...
What i'm doing is like this:
Use osa (Destination Database)
Go
DELETE FROM tblFaculty (*I'll delete first the datas to avoid duplication)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM cislucena.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM amapn.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM abe.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM aclc.dbo.tMasFaculty)
My problem is if the facultyID (PrimaryKey) which i'm copying is already on the destination which is osa, the copying stops/terminated regardless whether there is more to copy. On the 4 source database, there might data that other database also has. That's why the copying is terminated. All i want to do is to check first each FacultyID if it is already on the destination before copying it to avoid error or duplication of Primary Key so it won't terminate the copying.
How is this possible sir? Anyone care to help? Thanks in advance! More Power!
Best Regards
Hey,
I have a page that inserts into a customers table in the DataBase a new customer account using this function:
Public Function InsertCustomers(ByRef sessionid, ByVal email, ByVal pass, Optional ByVal fname = "", Optional ByVal lname = "", Optional ByVal company = "", Optional ByVal pobox = "", Optional ByVal add1 = "", Optional ByVal add2 = "", Optional ByVal city = "", Optional ByVal state = "", Optional ByVal postalcode = "", Optional ByVal country = 0, Optional ByVal tel = "")
Dim result As New DataSet
Dim tempid As Integer
Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("Conn"))
Dim Adcust As New SqlDataAdapter
Adcust.InsertCommand = New SqlCommand
Adcust.SelectCommand = New SqlCommand
Adcust.InsertCommand.Connection = conn
Adcust.SelectCommand.Connection = conn
sessionExists(email, sessionid, 1)
conn.Open()
If fname = "" Then
Adcust.InsertCommand.CommandText = "Insert Into neelwafu.customers(email,password,sessionid) Values('" & email & "','" & pass & "','" & sessionid & "')"
Else
Dim strsql As String
strsql = "Insert Into neelwafu.customers"
strsql = strsql & "(sessionid,email,password,fname,lname,company,pobox,address,address2,city,state,postalcode,countrycode,tel) values("
strsql = strsql & "'" & sessionid & "','" & email & "','" & pass & "','" & fname & "','" & lname & "','" & company & "','" & pobox & "','" & add1 & "','" & add2 & "','" & city & "','" & state & "','" & postalcode & "', " & country & ",'" & tel & "')"
Adcust.InsertCommand.CommandText = strsql
End If
Adcust.InsertCommand.ExecuteNonQuery()
Adcust.SelectCommand.CommandText = "Select Max(id) from neelwafu.Customers"
tempid = CInt(Adcust.SelectCommand.ExecuteScalar())
conn.Close()
Return tempid
End Function
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Now, I am getting an error:
Violation of PRIMARY KEY constraint 'PK_customers_1'. Cannot insert duplicate key in object 'customers'. The statement has been terminated.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The customers table has as a primary key the 'email'.....
so plz can I know why am I getting this error ????
Thank you in advance
Hiba
Here is my problem - I have a user defined function that needs the FIRST value from a specially sorted result set. When I do my set statement I get the LAST value in my result set. I can't figure out how in a SQL function to only get that first value (since I can't do a cursor or temp table).
Example: My table has a keyid (created by identity value, the value I need)
Also it has a vendorid, a repid, and an inventoryid. These tie out to other tables.
If I sort my result set in a certain descending order, the first record's keyid is the value I need. Problem is that I am getting the last keyid instead of the first. I can't switch my sort though and get the right value, because of some well-placed zeros. Hard to explain.
I guess what I want is something like:
Select @keyid = FIRST keyid from sortedtable order by vendorid desc, inventoryid desc, repid asc
It's the select FIRST that hangs me up. Can't find that function - does it or something like it exist that I can use from within my function? I'm sure it can be done, I'm just sure I don't know how... Thanks for your help. - K
I apologize in advance if this has been posted but if you get the attached error "The record has been changed by another user...", we've discovered that this error can occur if you have a SQL Server Data Type of "Float" and also a "Text" Data Type in the same table. We discovered that it only happens on some records though and I am not sure why this is so. The solution is apparently to add a TimeStamp Data Type to the SQL Server table which does seem to fix the problem. If anyone has any insight on any other solutions (we cannot change the Data Types) or the reasoning behind this error, I would greatly appreciate the feedback.
View 4 Replies View RelatedI need to create a query that will select only one record out of x number of record per user id.
Sample Data
User ID | Date |Status
001 |10/1/2013 |01
001 |10/2/2013 |A1
002 |1/1/2013 |BB
003 |5/8/2013 |EE
003 |4/2/2013 |BB
Expected Result
User ID | Date |Status
001 | 10/2/2013 |A1
002 | 1/1/2013 |BB
003 | 5/8/2013 |EE
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.
I'm trying to determine which user has locked a given record from VB6.I know I can use sp_lock and sp_who, and match up the data to determinewhich users have locked records in my database, however I haven't seen a wayto match the specific user to the specific record. What am I missing here?Thanks!*David*
View 5 Replies View RelatedIm trying to add a new rcord to my db on a button click usign the following code
'data adapter
Dim dAdapt1 As New SqlClient.SqlDataAdapter
'create a command object
Dim objCommand As New SqlClient.SqlCommand
'command builder
Dim builderT As SqlClient.SqlCommandBuilder
'connection string
Dim cnStr As String = "Data Source=ELEARN-FRM-BETA;Initial Catalog=StudentPlayGround;Integrated Security=True"
'dataset
Dim dsT As DataSet
Private Sub connect()
'connection
objCommand.Connection = New SqlClient.SqlConnection(cnStr)
'associating the builder with the data adapter
builderT = New SqlClient.SqlCommandBuilder(dAdapt1)
'opening the connection
objCommand.Connection.Open()
'query string
Dim query As String = "SELECT * from StudentPlayground..Employees"
'setting the select command
dAdapt1.SelectCommand = New SqlClient.SqlCommand(query, objCommand.Connection)
'dataset
dsT = New DataSet("Trainee Listings")
dAdapt1.Fill(dsT, "Employees")
End Sub
Private Sub BindData()
connect()
DataBind()
End Sub
Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click
Dim empID As Integer = CType(FindControl("TextBox8"), TextBox).Text
BindData()
Dim firstName As String = CType(FindControl("TextBox1"), TextBox).Text
BindData()
Dim lastName As String = CType(FindControl("TextBox2"), TextBox).Text
BindData()
Dim location As String = CType(FindControl("TextBox3"), TextBox).Text
BindData()
Dim termDate As Date = CType(FindControl("TextBox4"), TextBox).Text
BindData()
Dim hireDate As Date = CType(FindControl("TextBox7"), TextBox).Text
BindData()
Dim dept As String = CType(FindControl("TextBox5"), TextBox).Text
BindData()
Dim super As String = CType(FindControl("TextBox6"), TextBox).Text
BindData()
Dim newRow As DataRow = dsT.Tables("Employees").NewRow
newRow.BeginEdit()
newRow.Item(0) = empID
newRow.Item(1) = firstName
newRow.Item(2) = lastName
newRow.Item(3) = location
newRow.Item(4) = hireDate
newRow.Item(5) = termDate
newRow.Item(6) = dept
newRow.Item(7) = super
newRow.EndEdit()
'do the update
Dim insertStr As String = "INSERT INTO Employees" + _
"(EmployeeID,FirstName,LatName,Location,HireDate,TerminationDate,Supervisor)" + _
"VALUES (empID,firstName,lastName,location,hireDate,termDate,dept,super)"
Dim insertCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(insertStr, objCommand.Connection)
dAdapt1.InsertCommand() = insertCmd
dAdapt1.Update(dsT, "Employees")
'Dim insertCmd As new SqlClient.SqlCommand = (builderT.GetInsertCommand()).ToString())
'dAdapt1.InsertCommand = New SqlClient.SqlCommand(insertCmd.ToString(), objCommand.Connection)
BindData()
objCommand.Connection.Close()
objCommand.Connection.Dispose()
End Sub
im not sure wats going wrong because the record is not being added. Please help!!
I am getting the following error while trying to update a SQL Server 7.0 table thru' MS Access('97 version). It was working fine until now. This problem started happening after re-linking the table to MS Access as new columns have been added on the SQL Server table. We tried re-starting the MS Access,re-linking the table again,giving all the permissions to the user on the table etc. Also, no other user is accessing the table at the same time..Any help?
Error message in MS Access:
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
thanks,
Mike.
The following error is recieved when updating a record using access 97 and odbc driver to an oracle database.
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
Any help is greatly appriciated.
Thanks in Advance
I want insert automatically records for Range that user definded
For example user define :
from to city
10000 12000 tehran
15000 19000 babol
I should inserted 2000 record for tehran (10000,10001,....,12000) and 4000 record for babol (15000,15001,...,19000)
I was wondering if you guys might give me some advice on how best to handle a particular scenario i'm struggling with.
I have a client that basically wants web-based-update access to their sql server database. Specifically, for a group of users to be able to access a page where they select a record for editing. the caveat is that no two users should be able to pull up the same record at the same time. Originally I would have thought there was some easier record-locking-mechanism I could exploit within sql server or ado.net itself, but I haven't been able to come up with anything.. so this is my current approach:
The page they use starts-out with basically a blank form. there are custom-built paging controls at the bottom of the screen. they click page-forward to begin and a stored procedure is ran to select a record and update a field on that record to indicate "in-process". when they finish editing the record - or page on to the next record without updating - another stored procedure is ran - updating/resetting the status field on the record appropriately.
The entire page is encapsulated within an ajax.net updatepanel.
The entire page has caching disabled. This works well in conjunction with the first page being blank. if they get out of the app and try to get back in by clicking the back button - all they can do is get to the first (blank) page.
A piece of javascript window.onunload clicks a button on the page that releases the record they currently have selected in the event of a re-direct, clicking back, etc.. it appears to work with everything except a window close. in that case, i have a stored procedure running periodically on the server that checks how long a record has been selected - and if it exceeds the time indicated - resets the record as to allow it to be re-selected later.
In the event of session timeout, they are redirected to another page that tells them their session has timed-out (and since the window.onunload fires - it takes care of releasing the record if they have one on the screen).
The concept seemed to be working well until I started multi-user testing. Now it seems as if two users time it perfectly - they are actually able to both select the same record. it happens pretty rarely, but it does seem to happen. I'm guessing this has to do with how my stored procedure is structured - possibly allowing a tiny-enough window between the record being selected for editing - and the update running to actually status the record as in-process (2 separate sql statements within the one stored procedure).
I believe I also have a found a second quirk in my approach where something is causing the window.onunload event to fire twice in some strange situations.. but that's more annoying/confusing from a logging standpoint than anything..
I've read where people say to ensure you dont update a record that's already been updated - that you should compare the fields before you actually perform the update and ensure they haven't changed since you selected it.. but to me that doesn't solve anything.. if two people select the same record and both spend time working on it - the person that tries to update last has just wasted their time.
I've also toyed with the idea of maintaining a separate table in the database to hold the keys to the currently selected records and use that to keep multiple people from selecting the same record - but honestly i dont know if that approach is any better than what i'm doing now.
anyway, I was just curious if you guys had any advice in regards to how you'd handle a request like this.. or if you see any obvious problems/fixes with my current approach..
I would greatly appreciate any info you could provide-
thanks-
I'm got a "folder" structure application which we'll be using as an in-house directory viewer. (In case you're wondering, it doesn't relate to any "real" folders, so using xp_cmdshell is out! )
Each folder and file record can have its own permissions, however these are assumed to inherit from the parent folder if no specific access rules have been set, basically in the same way file systems work. Each file record can only have one parent, and a folder can either have a parent or be at the root level.
Right now I'm having an issue with the inheritance of permissions. Say if I want to grant access to "Folder 1" to "Group A", then "Group B" shouldn't be able to see it. However, if I grant access to "File 1" in "Folder 1" to "Group B", then "Group B" should be able to see "Folder 1", but only see "File 1" and not the rest of the contents.
I thought I could do this with a CTE, but I'm having a bit of difficulty..
Here's the code:
CREATE TABLE #FileSystem (
FSIDINTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY
,ParentFSIDINTEGER NULL
,NameVARCHAR(100)
,RecordTypeVARCHAR(1)-- (F)older, or Fi(L)e
[Code] ....
What I want to do is return a row of data when my query doesn't return a record. I have two tables:
CREATE TABLE dbo.abc(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
) ON PRIMARY
[Code] ....
So when I run the following query:
SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
UNION
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
abc-1-200.00
abc-1-500.00
As you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as
abc-1-200.00
abc-1-500.00
def-0-0.00
Hi,
I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?
Thanks in advance.
ramesh.p