|
This works for me and is way more efficient! Give'r a try, but remember to back up first and test the results to make sure you what you are expecting happens!
DELETE FROM [Table with Duplicates]
WHERE [Primary Key Field] IN
(
SELECT a.[Primary Key Field]
FROM [Table with Duplicates] a,
[Table with Duplicates] b
WHERE a.[Primary Key Field]!= b.[Primary Key Field] -- i.e. Userkey
AND a.[Value to check]= b.[Value to Check] -- i.e. Lastname
AND a.[Second Value to Check] = b.[Second Value to Check] -- i.e. Firstname
AND a.[Primary Key Field] < b.[Primary Key Field] -- i.e. Userkey
)
Rocky
|
|
|
|
|
hi
I create the Stored procedure for the user login. In the New user Creation i check the user number in the other table of user personal detail
whether the coding is not work correct.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PrCreatePassword]
@Employee_Number varchar(6),
@UserName varchar(15),
@Password varchar(10)
AS
DECLARE @EmpNo varchar(6)
SELECT @EmpNo=Employee_Number FROM dbo.Employee_Personal WHERE Employee_Number=@Employee_Number
IF @EmpNo=NULL
BEGIN
INSERT INTO Password
(
Employee_Number,
UserName,
Password
)
VALUES
(
NULL,NULL,NULL
)
END
ELSE
BEGIN
INSERT INTO Password
(
Employee_Number,
UserName,
Password
)
VALUES
(
@EmpNo,
@UserName,
@Password
)
END
give me a idea or new method
|
|
|
|
|
when you check for null values: @var1 is null, it should not be @var1 = null
now, as far as i understood you wnat to check whether that employee number exists or not, if it is not then you want to insert a new record.
Am i right!
Gautham
|
|
|
|
|
Yes Ur Right
At the time new user in login creation he must be in employee table.
|
|
|
|
|
ARE YOU FOLLOWING THE SAME WAY OR DID YOU GET ANY NEW SOLUTION?
Gautham
|
|
|
|
|
how can we insert into field arabic number(hindi format)
|
|
|
|
|
What is the SQL 2000 stored procedure to retrieve a user's permissions on a table.
God Bless,
Jason
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
I'm using SQL 2005 Express, and VC#2005 Express. Using a SQLCommand.ExecuteNonQuery() with a CREATE TABLE statement, I can make a table in my database with the columns I need, determined at runtime. I've verified the table is made in the database correctly, via SQL server Management Studio Express. In the process of making the CREATE TABLE statement, I also make the table in my local DataSet, same name, same columns, should be identical, along with a DataAdapter for the table. However, when I try to DataAdapter.Fill() the table, it tells me the table name is invalid, like the table isn't in the database on disk. I've tried setting the MissingSchema property to PassThrough, and then the .Fill() makes a new table, same columns, keys, etc., but the name of the new table is "Table", not the table name I create it with. Any help on this, either the table isn't being made right, or the DataAdapter is screwing up somehow, I can't figure it out.
My CREATE TABLE statement
CREATE TABLE finished (Listing NOT NULL PRIMARY KEY IDENTITY(1000,1),[More data columns, shouldn't be the problem, just data])
The SELECT statement used in DataAdapter
SELECT Listing,[More columns as above] FROM finished
I can post the code if needed
A soft glow comes from the pit in the darkness.
The clicking noise become faster - and louder.
A wind begins to stir up from the pit, as the
creature flexes it's wings, preparing for flight.
You stare into the pit, and hear a voice say
in your mind, "If you survive the encounter,
declare it to the world."
The Code Demon Rises.
|
|
|
|
|
Replying to my own post... In case anyone else has this problem, here's how I figured this out. Simple really. I just needed to add a TableMapping to the DataAdapter, and give it the right names. One line of code fixed a 3 day problem.
myDataAdapter.TableMappings.Add( "Table", myTable.TableName );
Have Fun!
A soft glow comes from the pit in the darkness.
The clicking noise become faster - and louder.
A wind begins to stir up from the pit, as the
creature flexes it's wings, preparing for flight.
You stare into the pit, and hear a voice say
in your mind, "If you survive the encounter,
declare it to the world."
The Code Demon Rises.
|
|
|
|
|
If you really want to be slick and save some work you could create the table as a datatable first then use SqlServer Management Objects to create the table at run time based on your in memory datatable. This only works with Sql2005 and Express2005, but it really save time in the code and verifies that what you have in memory matches with the database. If you are interested:
http://www.codeproject.com/useritems/DataTableToDataBase.asp[^]
Yes, this is one of my articles( currently my only one as I have been swamped) but SMO has become a pet project of mine as I am fascinated by it. Take a look and see if it helps.
Aaron
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
How can we protect our database in Sql Server 2000
|
|
|
|
|
Bachuli wrote: How can we protect our database in Sql Server 2000
There are numerous ways to protect a SQL Server database.
* Provide passwords for the accounts that are permitted to access it.
* Define appropriate permissions on the databases
* Define appropriate permissions on the objects in the databases
* Run the SQL Server service in a restricted account
* Turn off TCP/IP (if access is not required across a network)
* Set up firewalls to prevent the SQL Server's port being exposed outside the corporate LAN
* Install Service Pack 3a or above (which fixes the bug that allowed the Slammer worm to work)
*
*
*
*
There are too many ways to describe in a forum post. You might want to buy a book on SQL Server Security.
|
|
|
|
|
find the difference between 2 given dates in days using case
Revathi Raj
|
|
|
|
|
|
Hi all,
I want to compare my system time with the time in MS Access table. I want to display those records which are greater than the current time(even the seconds not the milli seconds).
Plz any one suggest me the query.
Thanks in advance
bye
Know is Drop, Unknown is Ocean
|
|
|
|
|
please help me with the sql statement to display the information on the gridview according to the selected dates(stating date and ending date)when you click generate info butto
oga
|
|
|
|
|
select * from tblName where Date>=startDate and Date<endDate.
|
|
|
|
|
from txtSdate and txtEdate
oga
|
|
|
|
|
select * from tblName where cast(Date as datetime)>=cast(txtStartdate as datetime)
and cast(date as datetime)<=cast(txtEnddate as datetime)
I assume Date column is also in text format.
|
|
|
|
|
How to provide the password protection in SQL Server 2000
|
|
|
|
|
You would normally do this in your calling code. While there is a password function in SQL Server, it is quite a weak implementation.
In the code that was responsible for managing the user details, you would typically want to encrypt the password and a salt string. If you store the salt in the database, it is a simple matter to compare the user entered password + the salt with the encrypted password stored in the database and compare them to see if they match.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
sir
i want to protect my sqlserver database with password.so that no any one can modify my table containt.
as i used database in my asp.net program.
i want to change it only through program not directly database.
is it possible.
then reply me.
ajeet
|
|
|
|
|
I am trying to update a MS ACCESS table that has a column defined as a datetime type. The update looks like:
string updateSQL = "update Perform " +
" set venueCd = ?, " + " performanceDTTM = ?, " +
" performanceDesc = ?, " +
"lotteryPerformance = ?, " +
" lotteryAsgnComplete = ?, " +
" performHandicapLogic = ?, " +
" maxNumOfCompTks = ?, " +
" maxNumOfAdditionalTks = ?, " +
" costOfEachAddTk = ? " +
" where venueCd = ? and " +
" performanceDTTM = ?";
I am using a OleDbType.Date parm to pass in the value of the date time. The value is correct in the parameter and there is a row that it should find and update. Any help would be appreciated.
Thanks,
Patrick
|
|
|
|
|
Even down to the millisecond? (Just a thought - I've been caught out with that in the past)
|
|
|
|
|
that is a good question. I can view the data in ms access and it does not display sectonds or milliseconds. The value I am passing in is actually read from the row that I am trying to update from a prior select.
Colin, is there anything special I should do since I am working with dates with MS Access?
Patrick
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
