|
Hello All,
Iam trying to create a table T1 which has a column called 'col1'.
I create a enumeration table called 'e1' like
CREATE TABLE e1<br />
( id INT NOT NULL,<br />
name VARCHAR(20) NOT NULL,<br />
description VARCHAR(20) NOT NULL,<br />
enabled BOOLEAN NOT NULL<br />
);
The id is set to 1,2,3,4,5
The 'col1' column in table T1 will hold any of these enumerations from table e1.
How should col1 be created (what datatype ?)
How is a value assigned to col1 from e1 ?
Is my table e1 declaration/creation correct?
Thanks in advance.
|
|
|
|
|
So you want col1 to only be able to have a value equivalent to one of the values used in the e1 column called id? This sounds like a look-up table.
One way to do this would be to use foreign keys. You specify col1 as a foreign key to table e1. I have called col1 e1ID and made it an int.
So create the e1 table using a primary key:
CREATE TABLE e1
( id INT NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(20) NOT NULL,
enabled BIT NOT NULL,
CONSTRAINT [PK_e1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Create the T1 table with a foreign key relationship to e1:
CREATE TABLE [T1](
[T1ID] [int] NOT NULL,
[E1ID] [int] NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(20) NOT NULL,
enabled BIT NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [T1] WITH NOCHECK ADD CONSTRAINT [FK_e1] FOREIGN KEY([E1ID])
REFERENCES E1 ([ID])
GO
Insert some rows into e1 for testing:
INSERT INTO [e1] ([id],[name],[description],[enabled])
VALUES (1, 'Test1','Desc1',1)
INSERT INTO [e1] ([id],[name],[description],[enabled])
VALUES (2, 'Test2','Desc2',1)
INSERT INTO [e1] ([id],[name],[description],[enabled])
VALUES (3, 'Test3','Desc3',1)
Test inserting into T1. First insert will work, second will fail:
INSERT INTO T1 ([T1ID],[E1ID],[name],[description],[enabled])
VALUES(1,2,'TTest1','TDesc1',1)
INSERT INTO T1 ([T1ID],[E1ID],[name],[description],[enabled])
VALUES(1,8,'TTest8','TDesc8',1)
|
|
|
|
|
Hi, I'm new with SQL2005.
I restored my DB (from SQL2000) to my new server but cannot access my
diagrams, this is the error it gives me.
Database diagram support objects cannot be installed because
this database does not have a valid owner. To continue, first use the
Files page of the Database Properties dialog box or the
ALTER AUTHORIZATION statement to set the database owner to a valid
login, then add the database diagram support objects.
I tried changing the DB owner
but no luck. What is the criteria for a DB owner, I must be doing
something wrong.
you can't forget something you never knew...
"Watching Migthy Joe Young made me hate my life..................................I want a gorilla!" A. Havemann
|
|
|
|
|
|
Hi all,
I have a sql statement that occur error when I run in sql server 2005....
"The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes."
update
TMP_TABLE_1
set
CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO)
from TMP_TABLE_1 A,
TMP_TABLE_2 B
where
A.CLASS_NO *= B.STUDENT_NO
and A.REQUEST_ID = @requestID
But How can I change the sql to Right outer join or left outer join.
Please kindly help.
Thanks
|
|
|
|
|
update
TMP_TABLE_1
set
CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO)
from TMP_TABLE_1 A LEFT JOIN
TMP_TABLE_2 B
ON A.CLASS_NO = B.STUDENT_NO
where
<s>A.CLASS_NO *= B.STUDENT_NO
and</s> A.REQUEST_ID = @requestID
|
|
|
|
|
|
Hi,
Every time i retrieve information from a large scale database from asp source, i get the information in 4 Sec. and with a lot of dataTable exception in the debugger mode.
If i use the query directly in the sql mode i get it all after 10 Sec.
I tried to change the connection string timeout but it made no difference.
can someone help me please?
|
|
|
|
|
What do you mean by "debugger mode" versus "sql mode"?
Lior S wrote: can someone help me please?
Show us your code, it will help us determine what exactly it is that you are doing.
|
|
|
|
|
Lior S wrote: lot of dataTable exception
are u using DataReader.Read or DataAdapter.Fill ?
ThaScorpion
|
|
|
|
|
Hello,
I was wondering if anyone is having this problem and knows the how and why of it. I have a Microsoft Access table, where one column is in the Date/Time format (format set to general). When I try to update this table (i.e. adding a new row) using C#, I get a datatype mismatch error. Is there anyway to directly pass this information to Access? I am using oledb.
I've found a work around by converting my date/time in c# to a string and storing it in my database as a string. But if possible, I'd like to keep it as a date/time format in my database.
Thanks in advance!
 over-worked, under-paid, glorified secretary 
|
|
|
|
|
It would help if you could post a sample of the code that is not working, along with a description of the table structure (is the field a date/time type, or a text datatype with display format set to general date?).
|
|
|
|
|
Hehe, oops, here is some of the trouble code:
userDS.UserSessionInfo.AddUserSessionInfoRow(CurrentUser,myDate,false,false,"",false,"");
when myDate is:
DateType myDate = DateTime.Now;
and the table structure for UserSessionInfo is:
UserSessionPkey --> int (autoincrement row, primary key)
UserPkey --> int (foreign key)
SessionDate --> dateType
ErrorsGen --> boolean
Training --> boolean
TrainingType --> string
Testing --> boolean
TestingType --> string
The above doesn't work and generates a datetype mismatch type error. But when I change it to the following, it works fine:
string myDate = DateTime.Now.ToString();
and the table structure for UserSessionInfo is:
UserSessionPkey --> int (autoincrement row, primary key)
UserPkey --> int (foreign key)
SessionDate --> string
ErrorsGen --> boolean
Training --> boolean
TrainingType --> string
Testing --> boolean
TestingType --> string
In my MS Access database, the column for SessionDate is of type Date/Time, with no formatting specified. When I change myDate to be a string, it works fine, even though I did not change the type in the Access database.
I know I've got a work around, by using strings, but I'm curious if anyone knows why this is happening.
TIA!
-- modified at 17:17 Sunday 19th November, 2006
~~~~~~~~~~~~
Over-worked, under-paid, glorified "secretary"
|
|
|
|
|
Okay...and now its doing the same thing with a boolean type. If I change my .xsd column from boolean to string, it works.
What's going on?? I found this website, and I thought I was doing what it lists...argh!
http://support.microsoft.com/default.aspx?scid=kb;en-us;320435
~~~~~~~~~~~~
Over-worked, under-paid, glorified "secretary"
|
|
|
|
|
I believe Access stores dates as real numbers representing the number of days since December 20, 1899 (or something close to that), so you probably need a conversion function. In VB, we could use CDate(X) to convert a date represented as a string to a value with date/time format. See if C# has a similar conversion function.
Scott
|
|
|
|
|
Ohhhh...now why don't they just say that instead of crazy errors? I don't suppose you have a resource (website, book, etc.) I can look into in case I run into this problem again for other types (i.e. stupid boolean type in another post). I will give this a try, it seems to make crazy enough sense to be right. Thanks so much! ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
~~~~~~~~~~~~
Over-worked, under-paid, glorified "secretary"
|
|
|
|
|
I want to import a txt CSV file into a view in SQL 2000.
that can be done easily using VBA but the problem is that I want to check the validity of data e.g checking the serial no if its numeric , check the date if its in correct format...and I want to use a sql trigger for that so that each time theres an insert , before that it should chk the validity of data , etc . .
plz hlp.
imran
|
|
|
|
|
Dear all;
I have a datetime field that stores data like "15/11/2006 10:12:23 AM"
how can I extract the date part only without concatenating month and day and year inside the Sql statement as i have LOTs of data to be extracted. I tried to cast to char(10) and then convert to date but it was too slow.
Thanx
Scorpion
|
|
|
|
|
ThaScorpion wrote: how can I extract the date part only
Use the following in stored procedure,
convert(varchar,checkindate,101) as checkindate
This will extract only 15/11/2006 only.
For ur reference:
checkindate --> Field in the table.
Hope u will understand it.
Janani
|
|
|
|
|
I mean extracting as date not just a string !
As converting to varchar ordering as date will fail !!
If I will say "Order By convert(varchar,checkindate,101) as checkindate"
the sort order will not be correct.
Is there anything in SQL server that solves that problem ??
hope u understand it.
Thank u
Scorpion
|
|
|
|
|
use dateadd(day, datediff(day, 0, checkindate), 0) as checkindate to truncate the time value
|
|
|
|
|
Thank you indianet for your reply
but i think i will face the same problem of speed.
i didnt try it yet, but do u think it will not be slow ?
There is a function in Access that do the solution in one step like :
"Order by DateValue(CheckInDate)" and thats it !
there is nothing like that in Sql Server ?!
thanx
Scorpion
|
|
|
|
|
select convert(varchar,Reminder_date,101) as Reminder, Reminder_From, Reminder_Until, Reminder_AlarmTime, Create_dt, Update_dt from dbo.TBL_Reminder order by Reminder
It was possible to order by date.
Janani
|
|
|
|
|
Thanx alot till i Try it
ThaScorpion
|
|
|
|
|
Have U order it by date?
Janani
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
