|
How about using a trigger? of course you will need to have a column to store the value but I bet that's easiest/simplest way..
Greetings,
:->
|
|
|
|
|
Thanks, actually planning to implement this in future, but for now i just want to check, if possible to can see the date of my previous records when it was inserted or updated,  think theres no other way then.
many thanks to all the response  .
Regards,
Stephen
---------------------
www.islasolutions.net
|
|
|
|
|
Hi,
I have the small doubt,I created a table Author in oracle and i put primary key constraint on authorid field.And i created another table Article and foreign key constaraint placed on this table's authorid.Now iam inserting data into these two tables through front end.
My doubt is why we have to create
Dim pk(1) As DataColumn
pk(0) = ds.Tables(0).Columns("custid")
ds.Tables("customers").PrimaryKey = pk
This is for primary key.
The below is for foreign key:
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("fk", ds.Tables(0).Columns("custid"),
ds.Tables(1).Columns("custid"))
fk.DeleteRule = Rule.Cascade
fk.UpdateRule = Rule.Cascade
ds.Tables(1).Constraints.Add(fk)
ds.EnforceConstraints = True
with out creating this constraints iam getting exception "sys... violated" when iam trying to insert duplicate values.
Then what is the use of above code and when the code will be useful.
Thanks in advance.
|
|
|
|
|
If I understand your situation correctly, it sounds like when you get the error without the contraints THAT error is coming from the actual database. The constraints you are setting up are on the dataset object. The difference being that with the constraints you will throw an error right away when you try to add a duplicate record into the dataset. Without the constraints the error will not be thrown until you try to update the database by using the dataset's update method. At least, this is what it sounds like. I could be wrong.
|
|
|
|
|
Hi Friends,
I have created a stored procedure which copy data from Table1 to Table2 table, its working i am happy.
Now i have to use Table2 in crystal report how can i run that procedure from the crystal report so it copy data from table1 to table2 to use in the crystal report.
if any explaination required pls do not hesitate to ask.
I am stuck help me pls.
Naveed Kamboh
|
|
|
|
|
Hi all.
One question that has foxed me for several years... is there a way to retrieve the output from Stored Procedures using .NET C#?
For example, if I have a stored procedure run through Query Analyzer, the Output tab reveals lots of row counts and the output from any PRINT statements used throughout that SP. It also reveals useful messages whenever errors occur. This is often useful for both debugging stored procedures, but also for outputting warning or status messages.
Obviously it is possible to use RAISERROR but sometimes this is not appropriate if you are checking the ADO.NET errors collection after your execution.
So is there any way to retrieve the non-error output using ADO.NET ??
Thanks.
|
|
|
|
|
Hi,
This is a part of my Stored proc where I am trying to assign NULL values to the date variables
ALTER PROCEDURE [dbo].[USP_SearchVehicleOnDateRange]
@DateFrom DateTime=null
,@DateTo DateTime=null
AS
BEGIN
SET NOCOUNT ON;
IF LEN(@DateFrom)<=0
begin
select @DateFrom = NULL
end
IF LEN(@DateTo)<=0
begin
select @DateTo = NULL
end
.....
...
But when I execute this its throwing the below error.
Msg 241, Level 16, State 1, Procedure USP_SearchVehicleOnDateRange, Line 16
Conversion failed when converting datetime from character string.
Any clue on how assign a NULL value to the data field?
Thanks
-L
|
|
|
|
|
If I'm not mistakem, LEN is used for strings only, so if you want to know if that parameter is NULL, simply ask
if @DateFrom = NULL
begin
...
end
Please check if that works for you
daniero
|
|
|
|
|
SQL Server Books Online
IS [NOT] NULL
Determines whether or not a given expression is NULL.
Syntax
expression IS [ NOT ] NULL
Remarks
To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
First off - your error is on line 16, and you have only showed us 14 lines of the stored procedure.
Secondly, you should use IS NULL and not LEN() to check if a variable contains a null value.
IF @DateFrom IS NULL<br />
BEGIN<br />
'Do something (but why set @DateFrom to NULL, since it defaults to NULL above???)<br />
END
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I had an idea I could be wrong, but I didn't have some place to make sure (developing PC) at the time. Thanks for correcting me.
daniero
|
|
|
|
|
can u tell me what is the difference between open xml update and dbdataadapter.update(dataset)
what is happening inside dbdataadapter.update(dataset)
which one is better why
guide me please
thanks
-
|
|
|
|
|
Hi Friends,
I have a table with fileds ID,code,descripiton, date
where ID is primaryKey also date is a unique while code and description could hame same data.
my problem is i have to fetch last two records for each of the same code and description in the table.
How can i do that please?
Please not that
"select top 2 * from table1 order by id des"c is returning only last 2 recods of the table.
While i need last 2 records for each of the code and description in the table.
thanks
Naveed Kamboh
|
|
|
|
|
Hi,
okay I will try my best, so I hope this will help you. My idea is to use a Stored Procedure or something like this, where you build a loop over all value-pairs of code and description (can be retrieved by a distinct or a group-by). And inside that loop you select the last two records using a where-condition to identify the matching records. Afterwards you can put these selected rows into a temporary table. Perform a select at the end to retrieve the result.
Pseudo-code:
<br />
create temporary table X (must have same scheme like your original table)<br />
<br />
foreach value-pair E of code and description<br />
begin<br />
insert into X <br />
select top 2 * from original_table where code = E.code and description = E.description<br />
end<br />
<br />
select * from X<br />
Hmmm, i hope this helps
Regards Sebastian
-- modified at 9:02 Tuesday 6th June, 2006
|
|
|
|
|
Assuming MS SQL, you could try something like this:
SELECT
t.*
FROM
table1 t
WHERE
t.date = (SELECT MAX(date)
FROM table1
WHERE code = t.code and
description = t.description)
OR
t.date = (SELECT MAX(date)
FROM table1
WHERE code = t.code and
description = t.description AND
date < (SELECT MAX(date)
FROM table1
WHERE code = t.code and
description = t.description))
|
|
|
|
|
That's by far your best option. There's a way to do it with grouping also which may come out faster depending on the DBMS and index structure. Either way, this is a much better solution than using a cursor.
|
|
|
|
|
We had a problem like this, some time ago... and the solution were to use stored procedured with cursors....
( The performance.... we know )
What we did is to build the cursor at runtime, with the Execute () Method... setting here the Order By... after that create the Cursor and Fetch till the Value needed... aslo notice you can specify the cursor to fetch directly a row
Alter Procedure DoLoop
as
Declare curMyLoop Cursor For
Select * from spt_values
Open curMyLoop
Fetch Next from curMyLoop
while @@Fetch_status = 0
Begin
Fetch Next from curMyLoop
End
deallocate curMyLoop
Go
alter procedure pp
as
declare @str varchar(50)
set @str= 'Select * from spt_values'
Execute ( @str )
Go
Regards
Ricardo Casquete
|
|
|
|
|
Thanks to all,
It realy helped me.
Naveed Kamboh
|
|
|
|
|
Hi all,
I am working for information warehouse application. Here database used is Oracle.Here we are loading data from flat file to oracle tables using SQL Loader. Which is getting failed and showing error
ORA 00001 unique constraint violated;
I have checked for the corresponding table by using DESC <table_name>;
It is showing NOT NULL constraint for all the columns. There is no primary key for the table.
Please guide me, what can be the expected reasons for this failure.
|
|
|
|
|
Hi,
hmmm sounds difficult. Maybe Oracle uses all columns as primary key (when no primary key is declared).
Did you tried to insert some rows manually? Did you get the same error? Try to insert two rows with all the same column values.
Hope this helps.
Regards
Sebastian
|
|
|
|
|
Hi,
I don't have right to insert the values manually.
Thanks for ur response.
|
|
|
|
|
It is possible to have constraints other than primary key or null constraints. Have you checked indexes on the table, or other constraints. I'm rusty on my Oracle, but every other database I've used lately has an option to temporarily disable constraint checking for data loads.
|
|
|
|
|
I have checked for the primary key by using DESC <table_name>. Is it the right way to check.
I am new to database can u please tell me how to check for indexes and primary key.
Thanks for ur response.
|
|
|
|
|
Can anyone tell me how to write a sql query to get a comma separated list of values.
Eg.
IF the table ColorTable contains in it's colors field red,blue and green.
The query "Select colors from ColorTable" would return 3 rows with each row containing each color.
I would like to have a query that would return the colors in one row as red,blue,green instead of returning 3 rows
I tried using list(colors) but this function is not recognised.
Kindly help me,
Thanks in advance
|
|
|
|
|
select (<br />
(select description from colortable where description='green') + ',' + <br />
(select description from colortable where description='Blue') + ','+ <br />
(select description from colortable where description='Red')<br />
) as colors from colortable
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
