#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0

    Select from help


    My apologies for what mus be a very simple query but I can't find an answer as I'm not sure how to frame the qestion.
    But in a program I have:

    inputFile="c:\test\test.asc"

    I want to put this variable into a select query:

    "select * from inputFile"
    but I can't get the syntax right.
    if I put "select * from test.asc"
    there no problem.

    Can someone put me straight pleasse?
    Thanks
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    10
    Rep Power
    0
    I'm not sure if I understand your problem entirely, but here are my thoughts:

    FROM should be the table name

    e.g.

    SELECT column_name(s)
    FROM table_name

    SELECT * FROM (table name goes here)
    WHERE (column name here)=inputFile

    Hope that helps!
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by blueflash999
    But in a program I have:

    inputFile="c:\test\test.asc"
    what is this? a file or a table?

    and what language are you using? php?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    Sorry, I should have given more information. I am using vb6 and here is the relevant piece of code

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cm As ADODB.Command

    Set conn = New ADODB.Connection
    conn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & App.Path & ";" & _
    "Extended Properties=""text;HDR=No;FMT=Delimited( )"""
    inputFile = "C:\test data\test data.asc"
    Set cm = New ADODB.Command
    cm.ActiveConnection = conn
    cm.CommandType = adCmdText
    cm.CommandText = "SELECT * FROM inputFile "
    Set rs = New ADODB.Recordset

    This code gives and error at the "set rs...." line because the object inputFile.txt cannot be found.

    This code does not give an error

    cm.CommandText = "SELECT * FROM C:\test data\test data.asc "


    When the code is finished, the user will input the path to inputFile via a textbox on a form so I don't want to have to put the specific filename and path in the SELECT command.

    Thanks for your help
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    thread moved to visual basic forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Code:
    cm.CommandText = "SELECT * FROM inputFile "
    ->

    Code:
    cm.CommandText = "SELECT * FROM " & inputFile
    medialint.com

    Today you are You, that is truer than true. There is no one alive who is Youer than You. - Dr. Seuss
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    You may find the following sub routine useful. vKeys contains the SQL command string. The routine returns True if successful, and False if not with vExceptions containing the error. The recordset is opened, updated, and then closed.

    J.A. Coutts
    Code:
    Public Function PutLocalData(vKeys As Variant, _
       vExceptions As Variant) As Boolean
    ' Purpose:
    '   Passed SQL parameters in vKeys, save results.
    ' =====================================================
        Dim sErr As String
        Dim sSQL As String
        Dim cmdSQL As ADODB.Command
        Dim SnapData As New ADODB.Recordset
        Const sProc As String = "PutLocalData"
        On Error GoTo putDataErr
        Set cmdSQL = New ADODB.Command
        Set cmdSQL.ActiveConnection = adoConn1
        cmdSQL.CommandText = vKeys
        cmdSQL.Execute
        PutLocalData = True
    Exit Function
    
    putDataErr:
        sErr = msModule & gsDelimiter & sProc _
            & gsDelimiter & err.Number & gsDelimiter & err.Description
        Call LogError(sErr)
        vExceptions = sErr
    End Function
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    If I use
    cm.CommandText = "SELECT * FROM " & inputFile

    I get a 'Syntax error in FROM clause'

    when I try to execute the statement

    rs.Open cm, , adOpenKeyset, adLockOptimistic

    which follows the

    Set rs = New ADODB.Recordset

    statement
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    I have found the cause of the problem I think.
    If the variable inputFile contains a path or filename with no spaces in it such as
    "C:\testdata\testdata.asc"
    everything works fine but if there are embedded spaces such as

    "C:\test data\test data.asc"

    then I get the Syntax in FROM statement error.

    So is there any way of including the inputFile string which contains embedded spaces in the SELECT statement?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    Originally Posted by blueflash999
    I have found the cause of the problem I think.
    If the variable inputFile contains a path or filename with no spaces in it such as
    "C:\testdata\testdata.asc"
    everything works fine but if there are embedded spaces such as

    "C:\test data\test data.asc"

    then I get the Syntax in FROM statement error.

    So is there any way of including the inputFile string which contains embedded spaces in the SELECT statement?
    Spaces are often considered a separator in windows. Simply look at the links to any of the programs in the "Program Files" directory, and you will see they are enclosed in quotes. You can try single quotes:
    cm.CommandText = "SELECT * FROM '" & inputFile & "'"

    If that doesn't work, you can try quad quotes """".

    J.A. Coutts
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    I am at a loss here.
    I have been trying to get the select statement right by inputting the path directly:
    "SELECT * FROM "c:\test data\test data.asc""

    and varying the number of double quotes and single quotes around the path.
    I either get a "syntax error in FROM clause" or
    "Expected end of statement"
    depending on the number and combination of quotes and spaces.
  22. #12
  23. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    You're using the Jet drivers which are for Access databases. In Access SQL (and pretty much all sql) you don't select anything from a file name, you select records from tables or views.

    Spend some time with the Jet ADO documentation it's somewhere in the msdn library online. There are numerous example codes that might help you understand how things are supposed to work.

    Comments on this post

    • medialint agrees
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester

IMN logo majestic logo threadwatch logo seochat tools logo