Hello i am new to Sql and idk why i get this error and also not getting results.

  • CREATE DATABASE dbsubjects;

    CREATE TABLE tblstudent(


    TheName Varchar (100),

    TheAddress VarChar (100),

    Marks Real,

    Comments Varchar (100));

    insert into tblstudent values (1001, 'Imon', 'FRI', 90,'Great');

    Msg 213, Level 16, State 1, Line 9

    Column name or number of supplied values does not match table definition.

  • "tblstudent" already exists -- with a different number of columns -- so the create table statement is failing, but the insert still tries to execute.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • how to fix it? because when i open a new query it says same thing and also when restarting. thank you for responding!

  • Inside that same db, run this command:

    EXEC sys.sp_help tblstudent

    or this:

    SELECT * FROM sys.columns WHERE name = 'tblstudent'

    Either of those should give you info about the table to compare to the CREATE you have above to see what the added/missing column(s) is(are).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Let us try to clean up your code and help you build good habits since you are new to this. First of all, a table must have a key and models a set of entities.

    But a key cannot be NULL by definition! All your columns can be NULL, so you can never have a key and therefore it will never be a table. Now let's go line by line.

    The use of the absurd "tbl-" affix is so bad it has the nickname "tibble" and there's even a little poem about it. Also, do you really have only one student? That's what you said!

    Calling a column "id" is useless. It needs to be student_id (you can read the metadata committee standards or ISO11179 for the rules). But if it is an identifier, and that has to be measured on a nominal scale (have you had a class on the types of scales and measures?) Values on nominal scale have to be character data, since they are not qualities or magnitudes.

    Likewise, the second column has to be the name of something in particular. I will guess it is the student's name. Do you really have students whose names are 100 characters long? If you leave the space for it. You'll get at least one or two of them! I like to use 30 or 35 characters because that's what the international Postal Service uses on address labels. There are also standards for the street address, postal codes and country. But you seem to want to squeeze it all into one line and not break it down to fields; okaygo ahead and stick with that, but it's a bad design.

    Why are you using floating points for marks? This is why we have DECIMAL(s,p) datatypes. I seriously doubt if you've had the two weeks we needed from. I wrote FORTRAN to learn how to handle floating-point math.

    You could put some kind of comments in this table, but I just don't like doing that. SQL was never meant to handle text and comments often lead to little problems with the law. If you need comments secure them in the other table that references the students. Here is revised DDL

    CREATE TABLE Students

    (student_id CHAR(10) NOT NULL PRIMARY KEY,

    student_name CHAR (30) NOT NULL,

    student_address VARCHAR (100) NOT NULL,

    student_mark DECIMAL (5,2) NOT NULL

    CHECK(student_mark >= 0.00));

    INSERT INTO Students

    VALUES ('1000000001', 'Frank Imon', 'Ninth ring of hell ', 90.00);

    Now the column count and data types match the parameters.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Add the use of your database to your script. Chances are you are working in context of master database !


    CREATE DATABASE dbsubjects;

    USE dbsubjects;

    CREATE TABLE tblstudent(...


    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply