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

Question

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

imon

SSC Enthusiast

Points: 173
More actions
September 25, 2023 at 4:44 pm

#4293788

CREATE DATABASE dbsubjects;
CREATE TABLE tblstudent(
ID INTEGER PRIMARY KEY,
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.
September 28, 2023 at 4:57 am

This was removed by the editor as SPAM
September 28, 2023 at 10:17 am

This was removed by the editor as SPAM
September 30, 2023 at 10:19 am

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

ScottPletcher SSC Guru Points: 101084 More actions · Answer 1

"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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

imon SSC Enthusiast Points: 173 More actions · Answer 2

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

ScottPletcher SSC Guru Points: 101084 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

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.

Johan Bijnens SSC Guru Points: 135362 More actions · Answer 5

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(...

Johan

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