|
Seema,
Try to have a stored procedure, pass the same values to the stored procedure and then check your validations like this:
IF EXISTS(SELECT * FROM YourTableName WHERE YourCondition)
begin
insert query.....
end
else
begin
record exists
end
All you have to do is to display your error msg accordingly once the valdation and insertion is done in your database
Gautham
|
|
|
|
|
Is there any way to do this natively, or will I need to concoct a kluge to enforce this?
eg a using a string with this sort of format "keya124keyb464keyc45" as the primary key in link tables?
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
|
In the designer, use Ctrl+Click to select multiple columns, then right-click to choose "Primary Key".
|
|
|
|
|
hi all,
i have a sql table which contains multiple entries like 3 duplicate records.
now i have to delete 2 records out of 3 duplicates and keep only one unique record(last inserted).
can anybody pls help me in writing such a delete query...?
thanx in advance.
|
|
|
|
|
Which database product? For SQL-Server, I would use something like:
delete MyTable
from (
select DuplicateColName, Max(InsertDate) MaxInsertDate
from MyTable
group by DuplicateColName
having count(*) > 1
) as A
where MyTable.DuplicateColName = A.DuplicateColName
and MyTable.InsertDate <> A.MaxInsertDate The inline select statement identifies the duplicate records that need to be kept.
PS: You probably want to try this on a test database first.
Regards
Andy
|
|
|
|
|
|
Hi All,
What is the maximum no. rows in MS-Access2000 and MS-Access XP
Thanx
|
|
|
|
|
The max size of a single MDB file is 2GB. There is no maximum number of rows.
If you're getting into data this large, Access is not your best choice for a database. SQL Server, even the Express Edition, will handle this far more efficiently than Access could ever hope to.
|
|
|
|
|
hi,
i have a coloumn in my database which display the data as '27/01/2007'(nvarchar) . now i wanna conver this in to a Date type in SQL please tell me how to do this.
Regards
ruwandi
rkherath
|
|
|
|
|
cast(column as datetime)
Wout Louwers
|
|
|
|
|
hi,
thax for replying but the date is not typed in the proper format
user types ------> 27/05/2007 (dd/mm/yyyy)
datetime ------> 05/27/2007 (mm/dd/yyyy)
when try to do above error comes
Arithmetic overflow error converting expression to data type datetime.
plz help
regards
rkherath
|
|
|
|
|
Do you want to convert nvarchar data to datetime or vice versa?
Gautham
|
|
|
|
|
in the sql table column is nvarchar , but the text goes like 15/05/2007. but i wanna compare and filter data accoarding to the current date. current date is like 05/17/2007. so i have a problem in writing the sql SP.
basically i wanna filter all the data in the table to see only the records before 2 day from today. but table contains a nvarchar type date entered.
plz tell me a solution
regards
ruwandi
rkherath
|
|
|
|
|
as far as i understand you have a column of type nvarchar with dates in it and you want to display records with dates between today and 2 days before???
then try this:
select * from Your_TableName WHERE Your_ColumnName
BETWEEN CONVERT(NVARCHAR, GETDATE(), 103)
AND
CONVERT(NVRCHAR, GETDATE()-2, 103)
Gautham
|
|
|
|
|
wow thax!
I got it.
million thax!
brilliont idea!
thanks Gautham
regards
Ruwandi
rkherath
|
|
|
|
|
hi again,
nother small thing. is there a way that i can format the 17/05/2007(dd/mm/yyyy) in to 05/17/2007(mm/dd/yyyy)
simply what i am asking is format the date
regards
ruwandi
rkherath
|
|
|
|
|
convert(nvarchar, YourColumnName, 101)..
To know more about these formats just go to sql books online and search for Convert..
Gautham
|
|
|
|
|
i tried but the same reult comes
CONVERT(NVARCHAR,TFRTTV.OUT_DATES,101)
regards
ruwandi
rkherath
|
|
|
|
|
set dateformat 'dmy'
select convert(varchar(10),cast(columnName as datetime),101)
Regards,
Arun Kumar.A
|
|
|
|
|
No problem! You got to help me when iam stuck some where too!!
Gautham
|
|
|
|
|
Sure!
U can count on me
Regards
Ruwandi
rkherath
|
|
|
|
|
Gautham
|
|
|
|
|
Thisis when i execute the procedure , it gives me nothing but one nullvalue and the second one show me more than a 1000 records. what is the difference between these two.
"Does not show results"
declare @test varchar(15)
exec SearchGEOCode '0196','2',@test
select 'Geocode is'= @test
"Showw Resuls"
select Geocode from property
where Num_key = '0196' and Extension = 2
"the code for Procedure"
Create Procedure SearchGEOCode (@Num_key varchar(10),@Extension int,@GeoCode varchar(15) Output)
with Recompile
as
select @GeoCode= Geocode from Property
where Num_key = @num_key and Extension = @Extension
Help
Vuyiswa
|
|
|
|
|
Vuyiswamb wrote: select 'Geocode is'= @test
You are attempting to assign the value of @test to a literal. Try
SELECT 'Geocode is ' + @test
|
|
|
|
|
ok i have change like this
declare @test varchar(15)
exec SearchGEOCode '0196','2',@test
SELECT 'Geocode is ' + @test output
Results are:
--------------------------
NULL
(1 row(s) affected)
Here is the Select that returns Good results, but still i want only one Row, but even many rows come i doent matter. check this out
select Geocode from property
where Num_key = '0196' and Extension = 2
Results are:
Geocode
---------------
ETSX02
ETSX02
ETSX02
they are many, this is what i want to see in that stored Procedure. here is the code Below again
Create Procedure SearchGEOCode (@Num_key varchar(10),@Extension int,@GeoCode varchar(15) Output)
with Recompile
as
select @GeoCode= Geocode from Property
where Num_key = @num_key and Extension = @Extension
Vuyiswa
|
|
|
|