Illegal characters in sql

[kennyaidan]

Hi,
I'm learning sql for the first time, and i created a table called story to simulate a news story database. I set it up as follows

CREATE TABLE story
(
id varchar(11),
headline varchar(100),
author varchar(30),
paragraph text,
story text,
date varchar(8),
genre varchar(20)
);

However when i entered a string into the paragraph and story fields which contains a "'" ie "John's house" , i get an error message back stating

Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ');

Is there any way to create a table that recongises that this character is only part of a string.
thanks
aidan

 

[swampBoogie]

You must double any ' in a string when using it in an SQL statement.

insert into story(id)values('John''s house')

Some comments on your table. You should have a primary key constraint so you can uniquely identify each row. What is paragraph and why do you use the datatype text? A varchar has a maximum length of 8000 characters.
There is a specical type for date and times, i.e. datetime which is preferable to using varchar.

 

[kennyaidan]

Thanks for the help, that seems to have solved that problem. I used the "date varchar(8)" command because i want the date to be in the following format yyyymmdd, will datetime do that for me or will it input it as mm/dd/yyyy??
thanks

 

[swampBoogie]

If you have a datetime value you can use the convert function

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

to specify in which format the data should be displayed.

SQL accepts a number of formats when storing dates. You can set the default format by using the set date format statement.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_052s.asp

 

[swampBoogie]

A correction to the last post.

[color]
SQL accepts
[/color]

should be

[color]
SQL server accepts
[/color]

SQL, i.e. as defined in the ANSI SQL 99 specification only accepts the format date'YYYY-MM-DD'

 

[kennyaidan]

thanks for the tips i'll check it out