16 replies to this topic

[Ilikestring]

I am getting the error "Too many fields defined" with this piece of SQL:

For Count := 1 to StrToInt(NumofAppsPerTeach[1, 2]) do

          begin

            NumofSlots := NumofSlots + 1;

           ADOQuery1.SQL.Clear;

[b]            ADOQuery1.SQL.Add('ALTER TABLE Timetable');

            ADOQuery1.SQL.Add('ADD COLUMN TimeSlot' + IntToStr(NumofSlots) + ' Integer;'); 

            ADOQuery1.ExecSQL; [/b]

          end

Normally I wouldn't ask for help with SQL related topics, but the strange thing with this error is that this piece of code has run perfectly dozens of times before but yet it is giving me an error now.
Is there anything wrong with my SQL?
Here is a screenshot of what the query looks like at run time:



Thanks in advance for any help.

[WingedPanther]

It depends a little on the type of database you're running against. Also, does the column TimeSlot1 already exist?

[Ilikestring]

I'm using an Access 2002-2003 database and I'm using Delphi 7. They're being connected by Microsoft Jet engine if that makes a difference. The column also definitely doesn't exist already.

[WingedPanther]

http://office.micros...0322071033.aspx

You really need to use something other than Access for this. Firebird or MySQL are both free and light.

[Firebird_38]

I don't want to be annoying, but if you have a whole bunch of "Timeslots" assigned to your records, and that number varies (increases, decreases, or both), then you should really make a new table called "TimeSlots". Make sure your Timetable table has a unique ID per record (add autoinc field if necessary). Then make the table TimeSlots with 2 fields: TableID and Slot. You may also add another ID field on there. Make sure TableID has an index. Then you can use JOINselects to get all the separate pieces together. This is much more flexible and way faster than using "ADD COLUMN" to add info. Also, this info is then added to all records, even those that don't need them.

Just a consideration. Also, you can then add as many timeslots as your pc can store on the HD.

Good luck.

[Ilikestring]

I have to use Access. I have nearly finished my system and it's not worth changing to something new now.
I also do not have time to change the structure of my database, unfortunately.
There is not at a set number of a columns, otherwise I would have inserted them manually. The number of columns varies during use.
Is there anything wrong with the SQL I have written?

[Ilikestring]

The problem has been solved. Thank you for your time.

[Ilikestring]

Oh, but I still don't know the SQL syntax for editing a column name. If anyone knows it would be great help. Thanks :)
Sorry for the triple post!

[Firebird_38]

select cow as newcolumnname from pasture where bell=1;

[Ilikestring]

So, Pasture = Table, Cow = Column and Bell = Field?
I don't really understand the 'where' bit of that.

I've tried these and they don't work:
alter table TeacherAssign rename column Student5 to Student6; (Syntax error)
Alter table TeacherAssign change Student5 Student6 varchar(10); (Syntax error)
ALTER TABLE "TeacherAssign" Change "Student5" "Student6" ["Integer"] (Syntax error)
sp_rename ''TeacherAssign.Student5'' ''Student6''; (Invalid SQL statement)

[Firebird_38]

Yes. But field is column. That would be the same thing in SQL. The important part is the "as newname" part.

Select ID as OrderID,cust as CustomerName, ordertotal as totalprice from orders where ordertotal>100;

Maybe this works better for you :).
Table "Orders" layout:
ID autoinc
cust varchar(255)
ordertotal double


the select statement will return:
OrderID autoinc
CustomerName varchar(255)
TotalPrice double

You may or may not be able to use spaces in your names, using [] or "" to delimit them. Spaces are not usually reccomended, though.

Depending on your sql version, thew above table may or may not work, but who cares, the illustrated point is "as newname", and that'll work.

[Firebird_38]

I just realized that this may not be what you're looking for. You may be wanting to change the name of the column in the table...

I'm not sure if this is possible, but you could:
-add a field, same type as oldname field, named newname.
-copy the contents of the old field
update tablename set newname=oldname;

this will copy the field for all records

-drop the oldname field

That'll work, but it's slow (each record must be visited 3 times: one to add a new field, one to copy the value, and one to delete the old field.

It's generally ill advised to be modifying your database as part of regular use of it. This takes forever, especially when using indexes on fields that your're changing. Some DBs don't even care if the field you're changing is part of the index and they'll reindex the whole table. You're supposed to make your DB schema in such a way that it never needs changing. In your case, this would mean making a table called TimeSlots or something like that, as I already explained. Now, I know you don't feel like messing with your layout, but not doing so is going to create a lot of trouble for you down the line, starting with a while ago (the reason why you posted this topic).

Good luck.