working with the SELECT...FROM stuff

[tracksomething]

Hi, I`m trying to open an MSHFlexGrid showing only the rows in an Adodc that I want it to. The code I`m using:
Adodc1.RecordSource = "SELECT * FROM Estudiantes WHERE Profesor LIKE '%" & Str(numero) & "'%;"
Adodc1.Refresh
flexdata.Refresh
It keeps telling me that there´s an error with the SELECT...FROM line and then of course another ont with the Adodc line.
Could you help?

[Bonker Gudd]

Hi,
It looks like you need to move the last single quote...

"SELECT * FROM Estudiantes WHERE Profesor LIKE '%" & Str(numero) & "%';"

[tracksomething]

Thanks. I changed the line, but it keeps telling me that there´s an error going on with the SELECT...FROM code. I don´t have a clue about it, the error says exactly:
[Microsoft][ODBC Microsoft Acces Controler] Syntaxis error on the FROM clause.
I think it´s pretty much the exact text in english, `cuz I`m using another language.
Thanks in advance.

[NateBrei]

Since the message specifically states that the error is in the FROM clause, check the name of your table to ensure that it is the correct name (as it appears in your database).

Also, I think Bonker's suggestion is still valid. It just hasn't given you an error on it yet because it has to get past the FROM clause first before it can process the WHERE clause. But, if you don't fix that, it will eventually error out as well.

Nate

[tracksomething]

yup, it´s the right name. Estudiantes it is. I´ll keep looking anyway but if you find something else that may be wrong please let me know.
Thanks a lot

[DerFarm]

I had exactly the same problem

http://209.120.143.185/editpost.php?...postid=1078081

[Ecniv]

If you are using access - try * instead of %


Vince

[tracksomething]

Couldn`t you solve the problem DerFarm? Ecniv, I used an * everywhere there was a %, but it stills the same, it didn´t changed anything a bit. I`m just about to give up... nah... not yet, and besides, I actually need to fix that.

Thanks to all.

[tracksomething]

Couldn`t you solve the problem DerFarm? Ecniv, I used an * everywhere there was a %, but it stills the same, it didn´t changed anything a bit. I`m just about to give up... nah... not yet, and besides, I actually need to fix that.

Thanks to all.

[Ecniv]

"SELECT * FROM Estudiantes WHERE Profesor LIKE '%" & Str(numero) & "%';"

Ok...

Try this first :
"SELECT * FROM Estudiantes"

If that works then try :
"SELECT * FROM Estudiantes WHERE Profesor LIKE '%<ab>%';"
<ab> is two characters in the middle of one of the professors names.
Change the % to * if you use Access

Lastly - is field professor text? or a number ?? (Str(numero)) cause to me looks like you are confusing them... possibly.


Post up what happens ok ?

Vince

[tracksomething]

Thanks Ecniv. I changed the line so that it would be just "SELECT * FROM Estudiantes", and it seems that the problem is right there. The name Estudiantes it´s right, but it still gives me that message that says there´s an error in the FROM clause and then, with the Adodc1.Refresh method. If I quit the Refresh line, then the program runs but it shows me the whole database (this is writing everything and not just "SELECT * FROM Estudiantes"). And by the way yes, I´m working with acces.

Thanks again, and if you have any idea please let me know.

[NateBrei]

Here may be an option. Let VB create the SQL for you so you can copy it to your code.

Temporarily add a Data Environment to your project.
Right-Click on the default D.E. Connection object & select properties.
Set the Connection to your Access database.
Right-Click on the Connection object & add a Command
Right-Click on the Command & select properties.
In the 'Source of Data' section, choose SQL Statement & choose the SQL Builder button.
A Data View window should appear from which you can see your list of DB objects (Tables).
Another window should appear behind the Data View window.
Drag your table from the Data View window into the top portion of the Design Command window (the one behind it).
Close the Data View window & build your SQL.
The Command Design window has 4 sections (top - holds the tables, 2nd - holds the selected fields & allows criteria entry & sorting options, 3rd - displays the SQL string it is building, bottom - allows you to right-click & choose Run to view the results.
When the result set is what you want, copy the SQL from the 3rd window & put it into your code.
You should then be able to close out of the Builder, & remove the D.E.

Hope this helps.
Nate

[tracksomething]

Thanks NateBrei. I tried what you told me and I thought it could be just what I needed, `till I tried runing the program. The same thing. Someone told me to use like instead of LIKE and to put % instead of the last 2 * I used. I also changed that, and again... it didn´t work. Please let me know if you have any more ideas. Thanks a lot.

[Ecniv]

Originally posted by tracksomething
Thanks Ecniv. I changed the line so that it would be just "SELECT * FROM Estudiantes", and it seems that the problem is right there. The name Estudiantes it´s right, but it still gives me that message that says there´s an error in the FROM clause and then, with the Adodc1.Refresh method. If I quit the Refresh line, then the program runs but it shows me the whole database (this is writing everything and not just "SELECT * FROM Estudiantes"). And by the way yes, I´m working with acces.

Thanks again, and if you have any idea please let me know.

Hmm tricky.

But I think you are right - the SQL is fine.
Now you need to check that the connections to the database are ok. Have a look at your data control. Check the path is right and that the connection string is correct for Access (Access 97 uses a slightly different connection string to 2000+).

Post up the connection string.

Alternatively, create a new form and data control and see if you can get it to display the table estad....

Good luck

Vince