Need Help with this one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Help with this one

Author

Topic

isjord
Starting Member

38 Posts

Posted - 2006-01-15 : 19:23:26

This is the code I put in:

-- Create the Section table in Registration
CREATE TABLE Section
(
sectionId VARCHAR NOT NULL ,
courseID VARCHAR(50) NOT NULL,
)
INSERT INTO Section(sectionID,courseID) VALUES (2712, 'ISM3113')
INSERT INTO Section(sectionID,courseID) VALUES (2713, 'ISM3113')
INSERT INTO Section(sectionID,courseID) VALUES (2714, 'ISM4212')
INSERT INTO Section(sectionID,courseID) VALUES (2715, 'ISM4930')

GO

Why aren’t the values showing up. What is this for *

When I open the table, then I see the course Id, but do not see the sectionId I just see the *

albanie

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-15 : 19:40:28

recreate your table and try, I just specified the size of the sectionID

CREATE TABLE Section
(
sectionId VARCHAR(50) NOT NULL ,
courseID VARCHAR(50) NOT NULL,
)

Also, it would be better to use integer value for the sectionID, after all you are inserting integer values into it.

isjord
Starting Member

38 Posts

Posted - 2006-01-15 : 21:22:30

I thank you so much for your help.

How do I get the dates to be entered when I open the table. 9/1998

-- Create the IS_Qualified table in Registration
CREATE TABLE IS_Qualified
(
facultyID VARCHAR (50) NOT NULL ,
courseID VARCHAR (50)NOT NULL,
Datequalified VARCHAR(50) NOT NULL,
)
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (2143,'ISM3112', 9/1988 )
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (2143,'ISM3113',9/1988)
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (3467,'ISM4212', 9/1995)
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (3467,'ISM4930',9/1996)
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (4756,'ISM3113',9/1991)
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (4756,'ISM3112',9/1991)

GO

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 21:34:51

Why Datequalified column is define as VARCHAR ? If Datequalified is to store a Date information, use the correct data type datetime instead.

CREATE TABLE IS_Qualified
(
    facultyID     VARCHAR (50) NOT NULL ,
    courseID      VARCHAR (50) NOT NULL,
    datequalified VARCHAR(50) datetime NOT NULL,
)
INSERT INTO IS_Qualified(facultyID, courseID, datequalified) VALUES (2143,'ISM3112', 9/1988 '1988/09/01' )

-----------------
'KH'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 21:41:18

And as suggested by Afrika, use Integer for your sectionid.

quote:
Originally posted by afrika

Also, it would be better to use integer value for the sectionID, after all you are inserting integer values into it.

-----------------
'KH'

isjord
Starting Member

38 Posts

Posted - 2006-01-15 : 22:04:23

-- Create the IS_Qualified table in Registration
CREATE TABLE IS_Qualified
(
facultyID VARCHAR (50) NOT NULL ,
courseID VARCHAR (50)NOT NULL,
Datequalified datetime NOT NULL,
)
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (2143,'ISM3112','9/1988’)
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (2143,'ISM3113','9/1988')
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (3467,'ISM4212', '9/1995')
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (3467,'ISM4930','9/1996')
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (4756,'ISM3113','9/1991')
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (4756,'ISM3112','9/1991')

GO

Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'ISM3113'.
Server: Msg 105, Level 15, State 1, Line 13
Unclosed quotation mark before the character string ')

'. What is this saying. I am just lost.

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 22:19:51

The last quote of your 1st insert statement is not a single quote. Changed it.

INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (2143,'ISM3112','9/1988’)

If you are going to use datetime as data type, you have to specify all 3 components of the date, Year, Month & Day. If you does not need the day then always set the day to 1. See my previous post.

CREATE TABLE IS_Qualified
(
    facultyID     INT         NOT NULL ,
    courseID      VARCHAR (50)NOT NULL,
    Datequalified datetime    NOT NULL,
)
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (2143,'ISM3112','1988/09/01')
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (2143,'ISM3113','1988/09/01')
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (3467,'ISM4212','1995/09/01')
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (3467,'ISM4930','1996/09/01')
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (4756,'ISM3113','1991/09/01')
INSERT INTO IS_Qualified(facultyID,courseID,datequalified) VALUES (4756,'ISM3112','1991/09/01')

And when specifying datetime, it is better to youse YYYY/MM/DD.

-----------------
'KH'

isjord
Starting Member

38 Posts

Posted - 2006-01-15 : 22:34:22

Thanks, one more question.

INSERT INTO Student (studentID,studentName)
VALUES (65798,'Lopez')

Why do I get this:
erver: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Student'.

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 22:38:56

It means, there isn't a table named 'Student' in your database.
Verify it with this.

select * from INFORMATION_SCHEMA.TABLES

-----------------
'KH'

isjord
Starting Member

38 Posts

Posted - 2006-01-15 : 23:00:50

I have a database named Registration, I have a Student and Faculty under user table, but the rest of my tables are under master, user table. What should I do, and why is this that way.

I know you are sick of me but I am really learning from you and thanks again.

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 23:14:50

Looks like you might have created your tables under other database rather than the Registration. Depending on the default database setup in your user login, the default will be master if it is not changed. So you migh have forgotten to change the database when login and started coding.

When you login to your SQL Server via Query Analyser (QA), you should change the current database to your database (ie. the Registration database). You can do this by clicking on the database DROP DOWN and choose your database or enter in QA

use Registration

and press F5

If you have accidentally created your tables in the master database, you can remove it by using

 DROP TABLE <table_name>

-----------------
'KH'

isjord
Starting Member

38 Posts

Posted - 2006-01-15 : 23:44:03

Thanks, I got it right now. Thanks so much for your help.

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 23:52:40

You are welcome

-----------------
'KH'

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-16 : 02:36:45

quote:
Originally posted by isjord

I know you are sick of me but I am really learning from you and thanks again.

albanie

Nobody is sick of you, you would be amazed how i learnt. One question, do you have a primary on your table ?

Make sure you put a primary key on your table
Afrika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources