Source: SQL Error message?

[Ron Zellner]

I have a fairly simple instructional stack that has an SQL data submit function on the last (4th) card (submits the contents of several fields when the button is clicked). The submit function works fine. (Database & tables & fields are all correct)
However, whenever I open the stack I get an SQL error statement and I cannot figure out what is causing it. I have no OpenStack or OpenCard scripts. There is no code in the stack script or the first card script. I have searched the stack's scripts for any mention of SQL, data base, or possible table names, etc. and can't find any scripts that I wasn't aware of. Any insights?

Contents of Error Statement (There was only the one quotation mark at the end):

An error resulted for the SQL command in
the database: Query 1 Basic
Revdb error: Incorrect table name "

Is there any other place I should be looking? Is this coming from Revolution itself and not my stack?

[Janschenkel]

Is it possible that you experimented with th Database Query Builder while working on your stack? Open your stack, and then open the Database Query Builder (it's in the Tools menu) - if you see any items in the option menu at the top, delete them using the trash can icon in the topright.

Jan Schenkel.

[Ron Zellner]

Jan,
That's it. I don't remember using the Database Query Builder on this stack (did it last year), but that was exactly the problem. Thanks.

[Ron Zellner]

OK, I'm reinterested in the Database Query Builder. Works well, but I don't know how to link to card resources- for example, I can set the record set to select only a subset of the records with

Code: Select all

SELECT * FROM MainData where student = "Smith"

But how can I have a student name in a field on the card and have the subset restricted to just records having that name in the student field?

Code: Select all

SELECT * FROM MainData where student = field "studentName"

doesn't work, gives error.
I tried putting the name into a global variable, and then:

Code: Select all

global StudentName
SELECT * FROM MainData where student = StudentName

This does not produce an error, but does not select any records.

Are there some examples in the Revolution resources?

[bangkok]

Code: Select all

SELECT * FROM MainData where student = StudentName

The correct syntax should be :

put "SELECT * FROM MainData where student = ' "& field "StudentName"&" ' " into theQuery

That would make :
SELECT * FROM MainData where student ='Smith'

which is a correct SQL syntax.

[Ron Zellner]

That makes sense, so I removed the outer quotation marks and added it-

I don't get an error when I connect, but I also get no data transfer.
So I tried "<>" instead of "=" and that gave me the full data set.

These work and give me the full data set:

Code: Select all

SELECT * FROM MainData where Student <> ' "& first word of field "StudentName"&" ' 
SELECT * FROM MainData where Student <> ' "&  field "StudentName"&" ' 

but this

Code: Select all

SELECT * FROM MainData where Student = ' "&  field "StudentName"&" ' 

results in no data- as if the contents of the card's field do not match the database. but the field contains the name "Smith"
and this

Code: Select all

SELECT * FROM MainData where Student =  "Smith"

still works.
Any thoughts? What am I missing? Are the double & single quotes OK? Do I need to identify which card the field "StudentName" is on?

[Janschenkel]

The SQL statement in the automated database queries is "fixed" - it is not going to analyze the text in the SQL statement and insert the contents of fields or something like that - thatere's no special intelligence in them.

However, you can use the revGetSqlOfQuery function and [revSetSqlOfQuery[/i] command to read and change the SQL of an automated query. You can do that from a mouseUp, a closeField or any other event.

HTH,

Jn Schenkel.

[sturgis]

edit: misread how the select is put together, so changed post a little.
I don't know if this is it or not, but you have

Code: Select all

SELECT * FROM MainData where Student = ' "&  field "StudentName"&" ' 

There is a space between your ' and "

Then later on (at least in your paste) you have " ' with another space. The way its written with the spaces wouldn't it be trying to match
' Smith ' rather than 'Smith' ? And so wouldn't match? The others work and probably include Smith as part of the results cause you're searching for a not. And 'Smith' isn't equal to ' Smith '

That makes sense, so I removed the outer quotation marks and added it-

I don't get an error when I connect, but I also get no data transfer.
So I tried "<>" instead of "=" and that gave me the full data set.

These work and give me the full data set:

Code: Select all

SELECT * FROM MainData where Student <> ' "& first word of field "StudentName"&" ' 
SELECT * FROM MainData where Student <> ' "&  field "StudentName"&" ' 

but this

Code: Select all

SELECT * FROM MainData where Student = ' "&  field "StudentName"&" ' 

results in no data- as if the contents of the card's field do not match the database. but the field contains the name "Smith"
and this

Code: Select all

SELECT * FROM MainData where Student =  "Smith"

still works.
Any thoughts? What am I missing? Are the double & single quotes OK? Do I need to identify which card the field "StudentName" is on?

If it were a need to ident the exact location of the field it seems that you'd be getting an object not found error, so doubt thats the issue.

Also noticed this:

Code: Select all

SELECT * FROM MainData where Student <> ' "& first word of field "StudentName"&" ' 

You're asking for the first word of the field. Is the field structured as
lastname, firstname ?
if so, does the first word = "Smith," rather than just "Smith"? Still new, not sure about this.
If so, then instead of first word, first item should get just Smith.

Either way, the extra space between ' and " would cause a full "NOT" data match due to the extra space at the start and end of the match string that is created.