|
hi there
well I'm not the greatest in these things but I think the problem lies with ur procedure bcz u dont return values from it... I think u should use OUTPUT parameters in this. then atthe time u call the procedure u dont provide any value for those but u receive the value from it after the stored proc is called. u need to chg the definition of ur stored proc and declare an output variable @Identity as
@Identity INT OUT[PUT]
and in the end dont use RETURN use set @Identity = @@Identity
Go ahead and try it out, I'm sure it'll solve ur problem though but do let me know
Imran Akram.
|
|
|
|
|
Thanks...this is what I ended up doing to get it to work:
ALTER PROCEDURE dbo.spAddUser(@Name VARCHAR(50),
@LoginName VARCHAR(50),
@LoginPass VARCHAR(50),
@SecLvl INT)
AS
SET NOCOUNT ON
INSERT INTO dbo.tblUser([Name],
LoginName,
LoginPass,
SecLvl)
VALUES(@Name,
@LoginName,
@LoginPass,
@SecLvl)
SELECT IDENT_CURRENT('tblUser')
Pablo
Sometimes I think there's no reason to get out of bed . . . then I feel wet, and I realize there is.
|
|
|
|
|
|
I think my brain fell on the flore for a few minutes LOL, thanks for the help! 
Pablo
Sometimes I think there's no reason to get out of bed . . . then I feel wet, and I realize there is.
|
|
|
|
|
I want to do a query by Select Statement on date BUT
the problem that I want to make this query at 5 days before the date in the table.
For Ex,
I have date 6-1-2006 {with format(dd-MM-yyyy)} THEN,
I want when the query works give me a message before 5 days from this date, THAT MEANS
the query will give me the message at 1-1-2006.
I realy wanna do that,
and maney thanks for help...........
soly
|
|
|
|
|
well I dontknow exactly what ur trying to do but
think abt using this
now()// the function for todays date
if now()+5 = <the date="" from="" database="">print (or whatever) "hi there"
|
|
|
|
|
If you tell us what this is being used for then it might help with the answer. I can think of 2 or 3 ways, but they all have limitations which are acceptable in different scenarios.
|
|
|
|
|
Good day everyone,
I need to update a record into the database Access via table adapter in VB.Net 2005.
The dataset is updated, but the record in the database Access is not.
Could anyone know how to fix this problem?
Please help.I'd appreciate it so much.
Thanks
(Student_Details:table name)
MyDataSet.Student_Details.Rows(2).Item(3) = "new password"
MyDataSet.GetChanges(DataRowState.Modified)
Student_DetailsTableAdapter1.Update(MyDataSet)
MyDataSet.AcceptChanges()
Me.Validate()
Me.Student_DetailsBindingSource.EndEdit()
Me.Student_DetailsTableAdapter1.Update(Me.MyDataSet.Student_Details)
-- modified at 11:11 Saturday 14th October, 2006
eric
|
|
|
|
|
i have DB record named details and excel sheetwhich contains various column(water press,sensitiv press,etc)with values(float with various decimal length..1.12,1.123,1.1232...) that should not be changed as they r standards
.i tried to transform using data transform ,when i transformed the values got added or rounded off as i mentioned float datatype...so how to transform datas
Dhatchinamoorthy.R
9894824838
|
|
|
|
|
Hi,
does anybody knows how to gather the tables and columns of tables in oracle database by SQL Query?
in MySQL, I can gather that informations just to pass a SQL query like "show tables" and get the list of the tables in the certain database as a result.
or I can pass a Query "show databases" and as a result, I get the list of the databases.
but how can I do it in oracle system?
cheers...
|
|
|
|
|
u can try out
select * from tab;
rahul
|
|
|
|
|
thanks, it works!!
maybe you know also how to get the name of the columns in the table?
i try with "describe" command, but seems that oracle doesn't work the way like the MySQL
cheers...
|
|
|
|
|
u can try
select desc from table name;
rahul
|
|
|
|
|
u can try
desc table_name
|
|
|
|
|
I found this:
select table_name, column_name from all_tab_columns where column_name like '%X%'
I am sure there are system tables in Oracle to use to get this done.
SimpleMusings from a Simple Girl
|
|
|
|
|
Hi there!
I have a question regarding the structure of a database. I'm currently designing af table that will end up with around 50 columns of different kinds. Now my question is: Would it be better to split the table into smaller tables?
I have tried this but I keep running into integrity problems, because the database server will have to delete rows in other tables as well.
To clear things up I will give an example:
Consider a factory. The factory pollutes. In order to keep track of the pollution, the factory owner has to fill in some 20 values of different kinds. Normally you would place these values on the factory table, but since there are so many of them, you might be able to put the pollution data in another table called PollutionData. But somehow I can't get this to work properbly. I have the factory referencing the PollutionId in the PollutionData table.
A cascade delete will only work the other way around! Whenever someone deletes the pollution data, the factory is deleted. Which of course is wrong. I want the pollution data deleted if the factory is deleted. And I want the database server to produce an error if someone tries to delete pollution data referenced by a factory.
As I see it - the only way to solve this is to put the pollution columns into the Factories table, but I have this kind "oh noes - this is wrong"-feeling - and that's just because there are so many columns.
Any suggestions would be greatly appreciated!
Best regards
Soeren
|
|
|
|
|
Bad Robot wrote: Would it be better to split the table into smaller tables?
Does the data suggest you should do that?
Bad Robot wrote: Consider a factory. The factory pollutes. In order to keep track of the pollution, the factory owner has to fill in some 20 values of different kinds. Normally you would place these values on the factory table, but since there are so many of them, you might be able to put the pollution data in another table called PollutionData. But somehow I can't get this to work properbly. I have the factory referencing the PollutionId in the PollutionData table.
Is your polution data of the kind like:
COPollution1
CO2Pollution1
SO2Pollution1
COPollution2
CO2Pollution2
SO2Pollution2 etc.
If so then that is an obvious candidate for a one-to-many join as there are repeating fields (with an index marker in the name)
If you have 20 non-repeating columns, are they (as a block) optional? In otherwords: Can a factory exist without these values?
From what you've said already, regardless of the details of how the data relates, it is obvious that the Factory is the parent. A factory pollutes, without the factory you don't need the pollution data. Therefore, the PollutionData should reference the Factory table. The PollutionData table should have a FactoryId column.
Bad Robot wrote: A cascade delete will only work the other way around!
Well, put it the other way around. From your description, it seems that is the correct way to go.
Bad Robot wrote: And I want the database server to produce an error if someone tries to delete pollution data referenced by a factory.
Difficult one this. If you forbid this, then you cannot delete a factory either. I'd say you enforce this by using only stored procedures to access your data. The stored procs can enforce that rule.
Bad Robot wrote: As I see it - the only way to solve this is to put the pollution columns into the Factories table, but I have this kind "oh noes - this is wrong"-feeling - and that's just because there are so many columns.
If there are no repeating columns, and the data must always exist with a factory then it is not wrong. You can have upto 1024 columns in a table in SQL Server. The limit is that high because there are valid reasons to have that many columns.
Does this help?
|
|
|
|
|
Colin Angus Mackay wrote: If you have 20 non-repeating columns, are they (as a block) optional? In otherwords: Can a factory exist without these values?
There are no repeating columns in pollution data. Factories must come up with values for some of the columns. Some factories needs to fill in all the columns and others do not. It's Depending on the type of pollution and size of the factory.
Colin Angus Mackay wrote: From what you've said already, regardless of the details of how the data relates, it is obvious that the Factory is the parent. A factory pollutes, without the factory you don't need the pollution data. Therefore, the PollutionData should reference the Factory table. The PollutionData table should have a FactoryId column.
If I give the PollutionData table a FactoryId this would give me the opportunity to create more than one row of PollutionData referencing the same factory. This is not good since it could give the clients some trouble finding out which one to use.
Best regards
Soeren
|
|
|
|
|
Bad Robot wrote: If I give the PollutionData table a FactoryId this would give me the opportunity to create more than one row of PollutionData referencing the same factory.
Not if you also make FactoryId in the PollutionData table the primary key. Primary keys must be unique - therefore it will only permit you to insert one PollutionData row per Factory.
|
|
|
|
|
Bad Robot wrote: I have the factory referencing the PollutionId in the PollutionData table.
You should have the polution table referencing the factoryid in the factory table.
CREATE TABLE [Factory] (
[FactoryID] [int] IDENTITY (1, 1) NOT NULL ,
[FactoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Factory] PRIMARY KEY CLUSTERED
(
[FactoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Pollution] (
[PollutionID] [int] IDENTITY (1, 1) NOT NULL ,
[FactoryID] [int] NULL ,
[PollutionDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Pollution] PRIMARY KEY CLUSTERED
(
[PollutionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Pollution_Factory] FOREIGN KEY
(
[FactoryID]
) REFERENCES [Factory] (
[FactoryID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO
insert into factory (FactoryName) values ('Foo1')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY(),'Garbage')
insert into factory (FactoryName) values ('Foo2')
declare @nF1ID int
select @nF1ID = SCOPE_IDENTITY( )
insert into Pollution (FactoryID,PollutionDesc) values (@nF1ID,'Toxic Waste')
insert into Pollution (FactoryID,PollutionDesc) values (@nF1ID,'Sewage')
insert into factory (FactoryName) values ('Foo3')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY( ),'Tar')
delete from factory where FactoryID = @nF1ID
GoCascading works fine in the above example.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks for the fine example. Except this gives the factory the possibility to have more than one row of pollutiondata. Unfortunately this is not allowed in the datamodel! 
I created this at first, but it kept getting the system design into trouble.
Best regards
Soeren
|
|
|
|
|
drop table pollution
drop table factory
CREATE TABLE [Factory] (
[FactoryID] [int] IDENTITY (1, 1) NOT NULL ,
[FactoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Factory] PRIMARY KEY CLUSTERED
(
[FactoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Pollution] (
[PollutionID] [int] IDENTITY (1, 1) NOT NULL ,
[FactoryID] [int] <font size="5">NOT </font>NULL ,
[PollutionDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Pollution] PRIMARY KEY CLUSTERED
(
[PollutionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Pollution_Factory] FOREIGN KEY
(
[FactoryID]
) REFERENCES [Factory] (
[FactoryID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO
insert into factory (FactoryName) values ('Foo1')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY(),'Garbage')
insert into factory (FactoryName) values ('Foo2')
declare @nF1ID int
select @nF1ID = SCOPE_IDENTITY( )
insert into Pollution (FactoryID,PollutionDesc) values (@nF1ID,'Toxic Waste')
insert into factory (FactoryName) values ('Foo3')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY( ),'Tar')
delete from factory where FactoryID = @nF1ID
Go
Last modified: 1hr 14mins after originally posted --
WTF was this all about??? (See post below)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
-- modified at 14:31 Thursday 12th October, 2006
|
|
|
|
|
That won't fix that part of the problem. Making FactoryId the primary key or putting a unique constraint on it will.
|
|
|
|
|
Of course! I could do that. But what would be the best way to go? I kind of find the idea of making factoryId the primary key a bad idea. I don't know exactly why, but something tells me... Primary Key in one table, but also primary key in another table - well - I don't know.
UNIQUE constraint could be a way to go.
Please correct me if I'm wrong.
Best Regards
Soeren
|
|
|
|
|
Bad Robot wrote: Primary Key in one table, but also primary key in another table
Is valid for one-to-one or one-to-zero joins.
If you are always going to have one factory row with a single corresponding pollution row then you have a one-to-one relationship.
One-to-One relationships are a bit dodgy (I think) because it really means that the data belongs in the parent table. However, most one-to-one relationships are really one-to-zero relationships. e.g. If there was only ever going to be one pollution row for a corresponding factory row, but the pollution row was optional. So, it exists zero or one times for every factory row.
Bad Robot wrote: UNIQUE constraint could be a way to go
That just creates redundant data because the primary key is always unique anyway. Better to eliminate the redundant data and share the same values for the primary key in both tables.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
