|
Rewrite the bloody thing, you are relying on a conversion utility to work perfectly, they almost never do, especially with anything that is even mildly complex.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
So I'm trying to get rid of the DAO classes and references in my MFC app, and there seem to be some good drop in replacement classes here.
But does anyone know of an easy way to re-do "DoFieldExchange" in ADO?
|
|
|
|
|
|
let suppose i have table like this:
id name key
1 sam sam1
2 joy joy2
name and key are inserted by user. Now whenever i am inserting new value, I need to check whelther I have that key exists or not. If yes, do nothing and if no, insert it. I know how to do it in dot net page but can I do this in sql query itself ? If yes, can anyone please help me..
suchita
modified on Wednesday, June 29, 2011 9:32 AM
|
|
|
|
|
Yes. You could try:
IF NOT EXISTS (SELECT DISTINCT keyname FROM <table_name>)
BEGIN
INSERT INTO.....
END
As an aside: this may not be the case because I can only see two rows of sample data, but if the [keyname] value is a concatenation of [name] + [id] and [id] is an identity column, then you should always have a unique value in [keyname].
Hope that helps.
|
|
|
|
|
If uncrush is right then one has to ask why you are creating the KeyName field, it is redundant.
When I have to check for existing keys, I return the record instead of using exists, then test if I got a record, that way I have all the information about the record. I always put this type of work into a stored procedure that returns the record, the UI may not even know if the records was inserted ot not.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Not knowing what database you are using, but you could also put a UNIQUE CONSTRAINT on the keyname column. 
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]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I dislike using these, they tempt the developer into programming by error, try and add the dupe and trap the error. Whereas checking first makes the constraint redundant an does not generate an error.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I disagree, on the same terms that I do not check whether a file is readonly/accesible/existing when I delete it.
Bastard Programmer from Hell 
|
|
|
|
|
Eddy Vluggen wrote: on the same terms that I do not check whether a file is
readonly/accesible/existing when I delete it
If I am coding file I/O I certainly do!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Again depending upon the database and how the constraint operation is implemented, there may also be performance gains by using the constraint instead of coding it into a program. But your mileage may vary extensively.
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]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
SayamiSuchi wrote: keyname ... inserted by user
Then why should it be unique? 
If there's something that needs to be unique, then it should be created/set by the system not the user.
You already have id, what more do you need?
|
|
|
|
|
PIEBALDconsult wrote: If there's something that needs to be unique, then it should be created/set by the system not the user
So next time you signup for an online service your username will be papsd9308skjfd9kdi32.
|
|
|
|
|
Yeah, pretty much, at least I won't have to waste a half hour trying things and trying things always having it tell me it exists.
Think of Animal House -- "Your name is Flounder. Your name is Pinto." Simple, saves the user a lot of needless trouble.
|
|
|
|
|
Take a list of usernames. Usernames should be unique, but there's usually an identity. The identity is for the system, the username for the user.
Same goes for a filename; they need be unique, but you don't let the system choose it.
Bastard Programmer from Hell
|
|
|
|
|
Eddy Vluggen wrote: filename; they need be unique
Not necessarily.
|
|
|
|
|
Your new contract is located on the Z-share, and is called "contract.doc". Aw, there are two more files with the same name in there.
:z
Bastard Programmer from Hell
|
|
|
|
|
That wouldn't be in the database, would it?
|
|
|
|
|
That wouldn't matter, would it?
Some people generally check every option, others trust in a form of exception-handling.
Bastard Programmer from Hell
|
|
|
|
|
|
Why does it matter to you? Do you make a difference between deleting a file from disk and a file from a database, from an exception-handling viewpoint?
It's a personal preference; lots of experienced programmers tend to check specific whether a file (or record) exists and whether they've got the appropriate rights to delete it (from disk or database). I prefer to rely on exception-handling, has always worked quite well for me.
Do you try-except, or do you return a customized boolean that consists of Yes, No and EFileNotFound?
Bastard Programmer from Hell
|
|
|
|
|
I'm pretty sure none of that has anything to do with the original question.
Eddy Vluggen wrote: a difference between deleting a file from disk and a file from a database
Yes. The database will likely return "0 records affected". The file system may simply say "ok, it's gone" even if it didn't exist. Either way, I don't care, just aslong as it's gone.
Eddy Vluggen wrote: whether they've got the appropriate rights to delete it
That's a case where looking first may give inacurate information and you could wind up having to handle an Exception anyway, so I prefer to just try it and see.
|
|
|
|
|
PIEBALDconsult wrote: I'm pretty sure none of that has anything to do with the original question.
True.
Bastard Programmer from Hell
|
|
|
|
|
Hello SayamiSuchi,
This problem can be solved in many ways.
Method 1: As has already been answered by UNCRushFan
IF NOT EXISTS (SELECT DISTINCT keyname FROM <table_name>)
BEGIN
INSERT INTO.....
END
Method 2: Another traditional way [ Count(ColumnName) approach ]
Declare @tblSource table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
insert into @tblSource Select 'Sam', 'Sam1' Union all Select 'Joy','Joy2'
--Select * from @tblSource
Declare @key as varchar(10)
set @key = 'Joy2'
if(( Select count([KEY]) from @tblSource where [key] = @key) = 1)
begin
insert into @tblSource Select 'NewName', 'NewKey'
end
Select * from @tblSource
Method 3: Merge statement of Sql Server 2008
Consider the below statement first
Declare @tblSource table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
insert into @tblSource Select 'Sam', 'Sam1' Union all Select 'Joy','Joy2'
Declare @tblDestination table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);
Select * from @tblDestination
I have a source table and a destination table. I am inserting the record in the destination table by checking whether the Key of the source table exists in the destination or not. In this case no key will be found in the destination and hence the output will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
Now let us insert a record (with an already existing key) in the source table as
insert into @tblSource Select 'Joy','Joy2'
At this time the records in the source table will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
3 Joy Joy2
Now if we run the above Merge statement query
MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);
Select * from @tblDestination
The output in the destination table will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
because the key is already present in the destination table and hence it has been ignored.
But if we have the source table as
insert into @tblSource Select 'NewName','NewKey'
i.e.
ID Name Key
1 Sam Sam1
2 Joy Joy2
3 NewName NewKey
and then execute the above query, the output will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
3 NewName NewKey
Because this time we are inserting a new key altogether.
So the query will be
MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);
N.B.~ This will work in Sql Server 2008 and above
Hope this helps
Thanks
Niladri Biswas
|
|
|
|
|
I'm building a site that will be out on the web that takes job applications. Within the admin portal I am using SSRS 2008 to display the information from the database to the HR users. How do I need to set up SSRS so that I can point to the url that displays the report (I am rendering the report in a PDF format through the url)?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
