Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SQL error

nobbinnobbin Member Posts: 110
Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:

Adodc1

Syntax error in FROM clause


when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-

[code]Private Sub Command1_Click()
Dim SQL As String

SQL = "Select * From Authors"
Adodc1.RecordSource = SQL
Adodc1.Refresh

End Sub[/code]

thanks in advance

Nobbin
«1

Comments

  • dedwinsondedwinson Member Posts: 8
    : Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:
    :
    : Adodc1
    :
    : Syntax error in FROM clause
    :
    :
    : when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-
    :
    : [code]Private Sub Command1_Click()
    : Dim SQL As String
    :
    : SQL = "Select * From Authors"
    : Adodc1.RecordSource = SQL
    : Adodc1.Refresh
    :
    : End Sub[/code]
    :
    : thanks in advance
    :
    : Nobbin
    :
    I have had instances where field names and table names need to be in brackets [] to work properly. I am assuming for the moment you are using Jet or SQL databases.

    At this point though there are several possiblities but without more information I am just stabbing in the dark.

    First - Is the data control already connected to a datasource/database?
    If not, no amount of SQL is ever going to work since it can't figure out the FROM part since there is no database FROM which to look.

    Second - Does Authors actually exist on the database? Once again if not, ..........

    From what I can see, based on your code, there doesn't appear to be anything radically wrong. Generally speaking though I tend not to use the data control. I usually stick with ADO recordsets. For me it seems to be more straightforward. Probably it's just me.


    Dave Edwinson
    Product Development Manager
    ParaMetrica Inc.
    www.parametrica.com

  • nobbinnobbin Member Posts: 110
    : : Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:
    : :
    : : Adodc1
    : :
    : : Syntax error in FROM clause
    : :
    : :
    : : when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-
    : :
    : : [code]Private Sub Command1_Click()
    : : Dim SQL As String
    : :
    : : SQL = "Select * From Authors"
    : : Adodc1.RecordSource = SQL
    : : Adodc1.Refresh
    : :
    : : End Sub[/code]
    : :
    : : thanks in advance
    : :
    : : Nobbin
    : :
    : I have had instances where field names and table names need to be in brackets [] to work properly. I am assuming for the moment you are using Jet or SQL databases.
    :
    : At this point though there are several possiblities but without more information I am just stabbing in the dark.
    :
    : First - Is the data control already connected to a datasource/database?
    : If not, no amount of SQL is ever going to work since it can't figure out the FROM part since there is no database FROM which to look.
    :
    : Second - Does Authors actually exist on the database? Once again if not, ..........
    :
    : From what I can see, based on your code, there doesn't appear to be anything radically wrong. Generally speaking though I tend not to use the data control. I usually stick with ADO recordsets. For me it seems to be more straightforward. Probably it's just me.
    :
    :
    : Dave Edwinson
    : Product Development Manager
    : ParaMetrica Inc.
    : www.parametrica.com
    :

    thanks for your reply.

    the control is connected to the database, as i can link it to textboxes and activly view the records.

    Ive tried the sqaure brackets thing, and still the same error.

    the database i used to test this out on is the biblo.mdb that comes with the MSDN examples (I actually had the problem on one im working on, and decided to try with a proven database), and 'Authers'exsists as a table in this. I am using jet 4.0 to link into the Access 2000 MDB database.

    Any help on this will save a poor man loosing any more hair

    Regards

    Alex.


  • bagles1bagles1 Member Posts: 54
    :
    Try posting the error message... that would help significantly.

    bagles1

    Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:
    :
    : Adodc1
    :
    : Syntax error in FROM clause
    :
    :
    : when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-
    :
    : [code]Private Sub Command1_Click()
    : Dim SQL As String
    :
    : SQL = "Select * From Authors"
    : Adodc1.RecordSource = SQL
    : Adodc1.Refresh
    :
    : End Sub[/code]
    :
    : thanks in advance
    :
    : Nobbin
    :

  • nobbinnobbin Member Posts: 110
    I did, thats the error message down there ("Syntax error in FROM clause")

    : :
    : Try posting the error message... that would help significantly.
    :
    : bagles1
    :
    : Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:
    : :
    : : Adodc1
    : :
    : : Syntax error in FROM clause
    : :
    : :
    : : when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-
    : :
    : : [code]Private Sub Command1_Click()
    : : Dim SQL As String
    : :
    : : SQL = "Select * From Authors"
    : : Adodc1.RecordSource = SQL
    : : Adodc1.Refresh
    : :
    : : End Sub[/code]
    : :
    : : thanks in advance
    : :
    : : Nobbin
    : :
    :
    :

  • dedwinsondedwinson Member Posts: 8
    : Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:
    :
    : Adodc1
    :
    : Syntax error in FROM clause
    :
    :
    : when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-
    :
    : [code]Private Sub Command1_Click()
    : Dim SQL As String
    :
    : SQL = "Select * From Authors"
    : Adodc1.RecordSource = SQL
    : Adodc1.Refresh
    :
    : End Sub[/code]
    :
    : thanks in advance
    :
    : Nobbin
    :

    I just set up a project, used ADODC1 to connect to Biblio.mdb using Jet 4.0.

    I literally cut and pasted your code into the project, and it ran fine.

    The next thing I would try is to further simplify.

    [code]Private Sub Command1_Click()
    Adodc1.RecordSource = "Select * from Authors"
    Adodc1.Refresh

    End Sub[/code]

    One thing I just thought of. Are you initially setting the ADODC1 recordsource to anything? When I set up my project, I initially created a recordsource string that I plugged into the property page window of the ADODC control.

    When I removed the string, I got an error early on in the initialization of the application. What I would do at this point is insert breakpoints for each and every line in your Private Sub Command1_Click event. Let the project run and check to see exactly where the error occurs. It may not even be in the Sub you think it is in. If you still get errors and the event is never called, you need to look elsewhere. Check the recordsource property of the adodc control at initialization.


    Dave Edwinson
    Product Development Manager
    ParaMetrica Inc.
    www.parametrica.com

  • bagles1bagles1 Member Posts: 54
    SQL errors like that generally come from the point where the query is executed. A good way to test this is to run the the query against the database (in access) as long as the table name is actually "Authors" then the rest of the query is fine. Check the "refresh" method as ado also has a feature called "requery" I have never personally used the refresh method so not sure how and when it is supposed to be used. However the requery will execute the query again to "refresh" your recordset. I also believe that the refresh method works against the connection object where the requery is against the recordset object. That could be the problem right there. Let me know if it works or not and good luck!

    Bagels1

    : Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:
    :
    : Adodc1
    :
    : Syntax error in FROM clause
    :
    :
    : when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-
    :
    : [code]Private Sub Command1_Click()
    : Dim SQL As String
    :
    : SQL = "Select * From Authors"
    : Adodc1.RecordSource = SQL
    : Adodc1.Refresh
    :
    : End Sub[/code]
    :
    : thanks in advance
    :
    : Nobbin
    :

  • dedwinsondedwinson Member Posts: 8
    : SQL errors like that generally come from the point where the query is executed. A good way to test this is to run the the query against the database (in access) as long as the table name is actually "Authors" then the rest of the query is fine. Check the "refresh" method as ado also has a feature called "requery" I have never personally used the refresh method so not sure how and when it is supposed to be used. However the requery will execute the query again to "refresh" your recordset. I also believe that the refresh method works against the connection object where the requery is against the recordset object. That could be the problem right there. Let me know if it works or not and good luck!
    :
    : Bagels1
    :
    : : Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:
    : :
    : : Adodc1
    : :
    : : Syntax error in FROM clause
    : :
    : :
    : : when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-
    : :
    : : [code]Private Sub Command1_Click()
    : : Dim SQL As String
    : :
    : : SQL = "Select * From Authors"
    : : Adodc1.RecordSource = SQL
    : : Adodc1.Refresh
    : :
    : : End Sub[/code]
    : :
    : : thanks in advance
    : :
    : : Nobbin
    : :
    :
    :
    Lightbulbs are starting to flash all over now. In your project, check on the properties for the ADODC1 control. Look at the Recordsource property. I am willing to bet you have some bogus SQL code there which is trying to execute at the application starts.
    Dave Edwinson
    Product Development Manager
    ParaMetrica Inc.
    www.parametrica.com

  • nobbinnobbin Member Posts: 110
    : : SQL errors like that generally come from the point where the query is executed. A good way to test this is to run the the query against the database (in access) as long as the table name is actually "Authors" then the rest of the query is fine. Check the "refresh" method as ado also has a feature called "requery" I have never personally used the refresh method so not sure how and when it is supposed to be used. However the requery will execute the query again to "refresh" your recordset. I also believe that the refresh method works against the connection object where the requery is against the recordset object. That could be the problem right there. Let me know if it works or not and good luck!
    : :
    : : Bagels1
    : :
    : : : Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:
    : : :
    : : : Adodc1
    : : :
    : : : Syntax error in FROM clause
    : : :
    : : :
    : : : when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-
    : : :
    : : : [code]Private Sub Command1_Click()
    : : : Dim SQL As String
    : : :
    : : : SQL = "Select * From Authors"
    : : : Adodc1.RecordSource = SQL
    : : : Adodc1.Refresh
    : : :
    : : : End Sub[/code]
    : : :
    : : : thanks in advance
    : : :
    : : : Nobbin
    : : :
    : :
    : :
    : Lightbulbs are starting to flash all over now. In your project, check on the properties for the ADODC1 control. Look at the Recordsource property. I am willing to bet you have some bogus SQL code there which is trying to execute at the application starts.
    : Dave Edwinson
    : Product Development Manager
    : ParaMetrica Inc.
    : www.parametrica.com
    :

    lol, I dont think my lightbulbs are the right wattage...

    ok, heres what ive done

    * open VB start a new project
    * Add the control Adodc1
    * go into custom properties on the general tab
    * 'Use Connect String' press 'Build' Button
    * Select 'Microsoft Jet 4.0 OLE DB Provider' click 'Next'
    * Browse for the Biblo.mdb file, then click 'Ok'once selected
    * Now click on the 'Record Source' tab
    * select '2 - adCmdTable' from 'Command Type'
    * select 'Authors' from 'Table or Stored Procedure Name'
    * Click 'Ok' to return to the form design
    ----------------------------------------------
    * create a button on the form and paste the code (below) into it:-
    [code]Dim SQL As String

    SQL = "Select * From Authors"
    Adodc1.RecordSource = SQL
    Adodc1.Refresh[/code]

    when I run this, and click the button I get the same error... I think i must be doing something wrong when i build the connection string.

    By the way, in the 'RecordSource' property is the value 'Authors', but even if I remove this and run the app it still gives the same error after i press the button.

    I have the feeling this should be an easy thing to fix, and is something stupid that im doing..... i just dont know what.

    Thanks for your time in this by the way.

    Regards

    Nobbin


  • dedwinsondedwinson Member Posts: 8
    : : : SQL errors like that generally come from the point where the query is executed. A good way to test this is to run the the query against the database (in access) as long as the table name is actually "Authors" then the rest of the query is fine. Check the "refresh" method as ado also has a feature called "requery" I have never personally used the refresh method so not sure how and when it is supposed to be used. However the requery will execute the query again to "refresh" your recordset. I also believe that the refresh method works against the connection object where the requery is against the recordset object. That could be the problem right there. Let me know if it works or not and good luck!
    : : :
    : : : Bagels1
    : : :
    : : : : Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:
    : : : :
    : : : : Adodc1
    : : : :
    : : : : Syntax error in FROM clause
    : : : :
    : : : :
    : : : : when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-
    : : : :
    : : : : [code]Private Sub Command1_Click()
    : : : : Dim SQL As String
    : : : :
    : : : : SQL = "Select * From Authors"
    : : : : Adodc1.RecordSource = SQL
    : : : : Adodc1.Refresh
    : : : :
    : : : : End Sub[/code]
    : : : :
    : : : : thanks in advance
    : : : :
    : : : : Nobbin
    : : : :
    : : :
    : : :
    : : Lightbulbs are starting to flash all over now. In your project, check on the properties for the ADODC1 control. Look at the Recordsource property. I am willing to bet you have some bogus SQL code there which is trying to execute at the application starts.
    : : Dave Edwinson
    : : Product Development Manager
    : : ParaMetrica Inc.
    : : www.parametrica.com
    : :
    :
    : lol, I dont think my lightbulbs are the right wattage...
    :
    : ok, heres what ive done
    :
    : * open VB start a new project
    : * Add the control Adodc1
    : * go into custom properties on the general tab
    : * 'Use Connect String' press 'Build' Button
    : * Select 'Microsoft Jet 4.0 OLE DB Provider' click 'Next'
    : * Browse for the Biblo.mdb file, then click 'Ok'once selected
    : * Now click on the 'Record Source' tab
    : * select '2 - adCmdTable' from 'Command Type'
    : * select 'Authors' from 'Table or Stored Procedure Name'
    : * Click 'Ok' to return to the form design
    : ----------------------------------------------
    : * create a button on the form and paste the code (below) into it:-
    : [code]Dim SQL As String
    :
    : SQL = "Select * From Authors"
    : Adodc1.RecordSource = SQL
    : Adodc1.Refresh[/code]
    :
    : when I run this, and click the button I get the same error... I think i must be doing something wrong when i build the connection string.
    :
    : By the way, in the 'RecordSource' property is the value 'Authors', but even if I remove this and run the app it still gives the same error after i press the button.
    :
    : I have the feeling this should be an easy thing to fix, and is something stupid that im doing..... i just dont know what.
    :
    : Thanks for your time in this by the way.
    :
    : Regards
    :
    : Nobbin
    :
    :
    :
    Bingo!

    I got your error. The issue was with the adCmdTable option. You cannot plug SQL code at a recordsource if you use this option. It requires a single table name rather than an SQL statement.

    If you change the option to 8 - adCmdUnknown or 1 - adCmdText, then enter some SQL into the text/SQL area you will be able to use your SQL code above.

    If you want to keep the concept of adCmdTable, you will need to change the SQL you have from "Select * from Authors" to "Authors". This may be more restrictive than you want since it assumes you will open a table or "Query"/"View". You have little control over the composition of the SQL query.

    I guess my preference is to use 8 - adCmdUnknown or 1 - adCmdText which gives you the flexibility of composing dynamic SQL or opening a whole table by "Select * from Authors"

    Dave Edwinson
    Product Development Manager
    ParaMetrica Inc.
    www.parametrica.com

  • lionblionb Member Posts: 1,688 ✭✭
    : : Ok, database programming is not my speciallity, can anyone shed any light on why im getting the follow error:
    : :
    : : Adodc1
    : :
    : : Syntax error in FROM clause
    : :
    : :
    : : when I use the following code, with a command button 'Command1' and an ADO control 'Adodc1':-
    : :
    : : [code]Private Sub Command1_Click()
    : : Dim SQL As String
    : :
    : : SQL = "Select * From Authors"
    : : Adodc1.RecordSource = SQL
    : : Adodc1.Refresh
    : :
    : : End Sub[/code]
    : :
    : : thanks in advance
    : :
    : : Nobbin
    : :
    :
    : I just set up a project, used ADODC1 to connect to Biblio.mdb using Jet 4.0.
    :
    : I literally cut and pasted your code into the project, and it ran fine.
    :
    : The next thing I would try is to further simplify.
    :
    : [code]Private Sub Command1_Click()
    : Adodc1.RecordSource = "Select * from Authors"
    : Adodc1.Refresh
    :
    : End Sub[/code]
    :
    : One thing I just thought of. Are you initially setting the ADODC1 recordsource to anything? When I set up my project, I initially created a recordsource string that I plugged into the property page window of the ADODC control.
    :
    : When I removed the string, I got an error early on in the initialization of the application. What I would do at this point is insert breakpoints for each and every line in your Private Sub Command1_Click event. Let the project run and check to see exactly where the error occurs. It may not even be in the Sub you think it is in. If you still get errors and the event is never called, you need to look elsewhere. Check the recordsource property of the adodc control at initialization.
    :
    :
    : Dave Edwinson
    : Product Development Manager
    : ParaMetrica Inc.
    : www.parametrica.com
    :
    :
    I do not use ADO a lot. I'm ADODB lover that's why I have stupied question. Why do you need Refresh method in this application? According to MSDN ADO refresh method "Updates the objects in a collection to reflect objects available from and specific to the provider." Like Dave, I cut and paste code. This error occurs exactly during refresh method execution. When I commented out line with refresh and run application again, error's gone. This code perfectly works without Refresh method. It also works (with/without refresh), if some control, for instance text box, is bounded to this data source.


«1
Sign In or Register to comment.