strange and irritating Sql problem

[nikeloeleven]

hi there.... im using a very simple Select Statement in Delphi...

I have a table called "TARGET".. the unique field is Admission Number (of pupil) and Target Number....

the database is an interbase database constructed using SQL... I have placed an SQL query such as this to retreive some fields from the Target Table when the data entered into a masked edit box matches the data in AdmissionNumber field....






with DataModMain.queryUpdateTarget do begin
Close;
SQL.Clear;
SQL.Add('select ADMISSIONNUMBER, DESCRIPTION, EXPECTEDEVIDENCE, STRATEGYUSED, SUBJECTNAME, TARGETCATEGORY, TARGETNUMBER from TARGET WHERE ADMISSIONNUMBER = "' + maskAdmissionNumber.text +'" ');
Open;
end;





so for example, if i entered "A11889" into the edit box... then the program would search for all records whose admission number is A11889....

however my problem is this: i get an error message when this sql statement is run...

the error is: the project raised exception class EIBInterbaseError with message "Dynamic SQL Error. SQL Error Code = -206.... Column Unknown A11889".... Process Stopped....


so if i enter A11889 into the maskbox then i get that error message... i don't see what the problem is and iv'e tried everything but i can't seem to press the right button....

please help me solve this problem as it seems minor but i've been stuck on it for a reason i cant seem to figure out... the code doesn't seem wrong to me ...

any suggestions? thnx.....

 

[svanels]

I think you have a '''' problem Interbase is looking for column named A11889

Question: Why you use a textfield with number inteligence build-in? I assume it is a primary field.

Comparing is slower, and you need to build the intelligence in your program (fat client).

If it is an unique number, let Interbase handle it. Autonumbering can be done with generators and triggers. You will have an unique number when inserting a record, regardless if you do it with a delphi program, IBConsole, SQL-explorer or any 3[sup]rd[/sup] party program that access the Server

Steven van Els
SAvanEls@cq-link.sr

 

[nikeloeleven]

Hi svanels... i used a text field with number intelligence because it is a joint primary key (along with target number)

But why is the program looking for a column named A11889 when A11889 is actually the primary field that I am trying to find from the ADMISSION NUMBER column....

I have used a mask on the edit box where >A< is always the first character and the last 5 are digits...

now if i type A11889 into the mask edit box and then run that query... i would have thought that the query searches the Admission Number field and returns all the records that have A11889...

(each pupil can have 3 targets so there will be 3 records with A11889. The key fields are Admission Number and Target Number so it is possible to have more than one record with A11889... you could have 3 records where the joint fields are like this:

A11889 1
A11889 2
A11889 3 <<<< 1 2 and 3 are the target numbers
)

But instead of searching the Admission Number column, it says "unknown column" for A11889 when A11889 is not even a column... how do u think i could solve the " " problem?

I was pretty sure that "' + maskAdmissionNumber.text +'" was right and I thought I had added the + in the right areas...

Any suggestions to how I could solve this?

 

[TNew2Delphi]

Hello NikeLoEleven...Just A question before we go further. Is the SQL query bound to any sort of Data Control. And then within that Data Control did you mistakenly enter one of the fields as A11889?

 

[nikeloeleven]

Well what i've actually done is made a new module where i've added all the Interbase connections for the database and also placed a TIBQuery.... The TIB query is directly connected to the TIBDATABASE component... (Do i need any other SQL component other than the TIBQUERY ???)

i've left the SQL property blank for the query and used the following code in my main program (this code is called when a particular button is pressed)

"
with DataModMain.queryUpdateTarget do begin
Close;
SQL.Clear;
SQL.Add('select ADMISSIONNUMBER, DESCRIPTION, EXPECTEDEVIDENCE, STRATEGYUSED, SUBJECTNAME, TARGETCATEGORY, TARGETNUMBER from TARGET WHERE ADMISSIONNUMBER = "' + maskAdmissionNumber.text +'" ');
Open;
end;
"

I dont think the SQL query is bound to any sort of data control except for the fact that it takes the value of a masked edit box (maskAdmissionNumber.text)....

 

[nikeloeleven]

Hi... i've done a little more testing and ive found out that the mistake probably lies somwhere here :
+ maskAdmissionNumber.text + <<<<<< if i enter another value into the text box then the error message will say that the value entered is an unknown column... therefore... it must probably be something to do with the code pasted above???

what do you think?

 

[svanels]

The error is in your SQL statement, Interbase expects a "text script" to execute the sql command, you try to do an arithmetic operation (+) in your script, the sql script doesn't follow the delphi-rules for strings.

Steven van Els
SAvanEls@cq-link.sr

 

[TNew2Delphi]

I dont use InterBase but the concepts should be the same. Try simplifying the SQL statement and see what happens. What I mean is try..."'select * from TARGET" first...Then add a where clause... then specify each column...Then get info from MaskedEdit. Also You should not Need a Database component. The TIQQuery should connect to the database directly. I dont work with Interbase but the Query component is all you need in ADO. This might be related to you also...

http://www.experts-exchange.com/Databases/Interbase/Q_21142331.html

Good Luck

 

[TNew2Delphi]

I didnt see you previous post. I was typing a reply . If you believe the problem is in the masked edit control then you should be able to hardcode "A11889" into your SQL statement and get a result set. ... like thus... SQL.Add('select ADMISSIONNUMBER, DESCRIPTION, EXPECTEDEVIDENCE, STRATEGYUSED, SUBJECTNAME, TARGETCATEGORY, TARGETNUMBER from TARGET WHERE ADMISSIONNUMBER = "A11889"'); Did You try that?

 

[nikeloeleven]

TNew2Delphi and svanels >>> yes i did try the hardcode "a11889" and it still gave me that error.... If i try ...... "where admissionnumber = 12" or any other number then the program works without any errors... but if i try a value with characters then the program gives an error.........????????

I'm now totally baffled as to why it is giving that error...

it must be something to do with the " quotes and the ' ....

any suggestions? thnx

 

[svanels]

Interbase expects a number, not a string, check your table structure. Masked Editbox? try real data-aware fields.

Steven van Els
SAvanEls@cq-link.sr

 

[TNew2Delphi]

I agree with svanels. Its your database not the query. Again I dont know interbase so I cant help you there.

 

[nikeloeleven]

well i had a look at the datatype for my admission number field and it says Char(6) in interbase.....
surely that must mean that it is searching for a string right???

 

[svanels]

Then it is a string, did you looked at the values that are stored in the table?

Steven van Els
SAvanEls@cq-link.sr

 

[lespaul]

anytime I'm writing SQL statements from Delphi, it doesn't like the " around the strings. Have you tried:

with DataModMain.queryUpdateTarget do begin
  Close;
  SQL.Clear;
  SQL.Add('select ADMISSIONNUMBER, DESCRIPTION, EXPECTEDEVIDENCE, STRATEGYUSED, SUBJECTNAME, TARGETCATEGORY, TARGETNUMBER from TARGET WHERE ADMISSIONNUMBER = ' + QuotedStr(maskAdmissionNumber.text));
  Open;
  end;

Leslie

 

[svanels]

I think you have a '''' problem

Steven van Els
SAvanEls@cq-link.sr