SQL problems...

[tag141]

probably a very simple answer. I'm trying to copy an example from a book. Not a good idea really I suppose. Anyway, my SQL is

select * from tblMytable where NAME =:mcode

my button code is:-

query3.active:=false;
query3.ParamByName('mcode').AsString:=Edit1.text;
query3.active:=true;

It is supposed to pull the record that corresponds to the edit1.text. However, when I try and set the SQL active to true, I get an error message that says "ADOTable1:Field mcode not found." mcode is supposed to be a variable and have values passed to it and isn't a field in my db.

Any ideas why or where this is going wrong?

TIA

 

[harebrain]

mcode is supposed to be a variable and have values passed to it and isn't a field in my db.

Exactly. You must query on something your db knows about, namely a field in a table. In this case, 'NAME'.

 

[tag141]

If I change the SQL to

select * from tblMytable where NAME =:NAME

or

select * from tblMytable where NAME =NAME

and change

query3.active:=false;
query3.ParamByName('NAME').AsString:=Edit1.text;
query3.active:=true;

It still throws an error. The first SQL tells me NAME is of an unsupported type. The second simply crashes the app. Is there any way of changing the SQL or code to let me search by a text box?

 

[LucieLastic]

Try this

select * from tblMytable where [NAME] =:NAME

Your other code (ParamByName) looks good.

 

[svanels]

select * from tblMytable where NAME =:mcode

looks good, in the Params property do you actually see something like 0 - mcode ?

Selecting this, the object inspector shouldl show:
Data Type, Name, Param Type etc..

Steven

 

[tag141]

svanels: Yes I get 0 - mcode. The param type is ptUnknown and the data type ftUnknown. The name is mcode.

Using square brackets doesn't make a difference. I just get invalid use of keyword.!

Sorry for the hassle but I made this project in VB. Then lost the project and thought I'd try in Delphi 7.

 

[LucieLastic]

Square brackets are used for reserved words in SQL Server. Try setting the param type and data type properties.

Otherwise, you can do it like this:

query3.close;
query3.sql.text := 'select * from tblMytable where NAME = '+quotedstr(Edit1.text);
query3.open;

In your original message, the error is specifying an ADOTable rather than an ADOQuery. Are you getting confused between these two?

 

[svanels]

Set the param type to input, and adjust the others according to your requirements. And yes we are talking about queries, not tables

Steven

 

[lespaul]

before setting the query to active, show the SQL to make sure you are passing what you expect:

ShowMessage(SQL.Text);

Make sure that the syntax looks correct, that any ' marks are around strings and not numbers, etc.

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design