Still having trouble with delphi ado

[glubbish]

Hi all,

When I click on the add button I get
"exception class EdatabaseError with message 'DBedit2: Field 'RoomNumber' not found

I am trying to get the max roomnumber to insert a new room max(roomnumber)+1

Its a fundamental hole in my knowledge here, on how to get a result from the database into a pascal variable.

Showroom and update both work as expected.

Table definition
'CREATE TABLE Rooms (roomnumber number, roomname TEXT(100),description memo)';

Procedure TRoomForm.ShowRoom;
begin

adoquery1.close;
adoquery1.SQL.Clear;
ADOQuery1.SQL.Add('Select roomname,description from Rooms where RoomNumber = :p1');
AdoQuery1.Parameters[0].Value:=CurrentRoom;
adoquery1.Open;

adoquery2.close;
adoquery2.SQL.Clear;
ADOQuery2.SQL.Add('Select * from passages where room = :p1');
AdoQuery2.Parameters[0].Value:=CurrentRoom;
adoquery2.Open;
end;

procedure TRoomForm.InitializeClick(Sender: TObject);
begin
initdatabase;
showroom;
end;

procedure TRoomForm.UpdateClick(Sender: TObject);
begin

adocommand1.CommandText := 'update rooms set roomname = :p1, description = :p2 where RoomNumber = :p3';
ADOCommand1.Parameters[0].value := dbedit1.Text;
ADOCommand1.Parameters[1].value := dbmemo1.Text;
ADOCommand1.Parameters[2].value := currentroom;
AdoCommand1.Execute;
end;

procedure TRoomForm.AddRoomClick(Sender: TObject);
begin
currentroom := 12; // so the result of the query does not match the current value
adoquery3.close;
adoquery3.SQL.Clear;
ADOQuery3.SQL.Add('Select max(roomnumber) from Rooms');
adoquery3.Open;
showmessage(inttostr(currentroom));
end;

procedure TRoomForm.FormCreate(Sender: TObject);
begin
database := 'adventure.mdb';
ADOConnection1.KeepConnection := false;
adoconnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+database+';Persist Security Info=False';
adoconnection1.LoginPrompt := false;
adoconnection1.Mode := cmShareDenyNone;
datasource1.DataSet := adoquery1;
datasource2.DataSet := adoquery2;
datasource3.DataSet := adoquery3;
dbmemo1.DataSource := datasource1;
dbedit1.DataSource := datasource1;
dbgrid1.DataSource := datasource2;
dbedit2.DataSource := datasource3;
adoquery1.connection := adoconnection1;
adoquery2.connection := adoconnection1;
adoquery3.Connection := adoconnection1;
DbMemo1.DataField := 'description';
DbEdit1.DataField := 'roomname';
DbEdit2.DataField := 'RoomNumber';
CurrentRoom := 0;
if fileexists(database) then ShowRoom;
end;

Thanks for any help.

[Ionelul]

hi there,

Change your add method as following:

procedure TRoomForm.AddRoomClick(Sender: TObject);
begin
     //currentroom := 12; not needed
     if adoquery3.Active then adoquery3.close;
     adoquery3.SQL.Clear;
     ADOQuery3.SQL.Add('Select max(roomnumber) + 1 as RoomNumber from Rooms');
     adoquery3.Open;
     currentroom := adoquery3.FieldByName('RoomNumber').asInteger;
     showmessage(inttostr(currentroom));
end;

Imagine a dataset(table, query) as a matrix. You have access to any field in dataset through FieldByName('columnname').asInteger (or asString, or asBoolean depending on the field type in database). FieldByName will get the item for the current record(row), so for more processing you can loop through dataset (for this use query.First, query.Next).

Good luck,
Ionut

[glubbish]

Thanks Ionut,

That is going to be very helpful.
I am still getting the error listed. Can you help with this as well?

Thanks Glubbish.

[glubbish]

I got it to work this way:

procedure TRoomForm.AddRoomClick(Sender: TObject);
begin
adoquery1.close;
adoquery1.SQL.Clear;
ADOQuery1.SQL.Add('Select roomnumber,roomname,description from Rooms where roomnumber = (select max(roomnumber) from rooms)');
adoquery1.Open;
currentroom := adoquery1.FieldByName('RoomNumber').asInteger+1;
end;

Why do I have to include the 3 columns in this table in the select?
if I don't I get the listed error.
I think its a fundamental misunderstanding on how this interface works :(

[Ionelul]

If you change the select as

'Select roomnumber from Rooms where roomnumber = (select max(roomnumber) from rooms)'

do you still get the error?

[glubbish]

Hi Ionelul

Yes, that works also.
Its a viable alternative, and I like the code better. Why select columns you do not want.

It now looks like:

adoquery1.close;
adoquery1.SQL.Clear;
ADOQuery1.SQL.Add('Select roomnumber from Rooms where roomnumber = (select max(roomnumber) from rooms)');
adoquery1.Open;
NextRoom := adoquery1.FieldByName('RoomNumber').asInteger+1;

its looks like its the subquery that does the trick

Thanks Glubbish

[Ionelul]

I did some tests myself and the problem is as I thought:
in your initial query you had

'Select max(roomnumber)  from Rooms'

if you put this query in a mssql's query analyzer, you will see that the result of this query is like

(No column name)
aValue

on the form create event, you put

DbEdit2.DataField := 'RoomNumber';

Now, when you open the query, dbedit, being binded with adoquery3 through datasource3, tries to get roomnumber field from dataset, but your field is marked as Column1(or "(No column name)") and the application doesn't find it( and raises error).
the solution for this issue is to put a name for the column in your sql query

select max(columnname) as columnname from table

This applies to all sql aggregate functions.

I suggest to find a way to insert this type of select(with naming the columns) in your application because your current way is kinda slow. you query the table twice in a single statement and that's not a good practice.

Good luck,
Ionut

[glubbish]

Thanks Ionut,

adoquery1.close;
adoquery1.SQL.Clear;
ADOQuery1.SQL.Add('Select max(roomnumber) as roomnumber from rooms');
adoquery1.Open;
NextRoom := adoquery1.FieldByName('RoomNumber').asInteger+1;

works as I had hoped in the first place.
Thanks for the description of why it occurs also, thats usually more valuable than the answer.

Regards Glubbish