Results 1 to 6 of 6

Thread: Query must have at least one destination field

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2005
    Posts
    35

    Query must have at least one destination field

    Hi,

    I receive the folowing error message in VB
    "Query must have at least one destination field"

    When I try to open a recordset

    The code is:


    Public Function AppendElement(elemento As ELEMENT) As ERROR_CODE
    On Error GoTo err
    'Creates a recordset to write the data to the file DB
    Dim RsData As New ADODB.Recordset
    With RsData
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockOptimistic
    End With
    If DBPresent = False Then
    AppendElement = DB_NOT_OPEN
    Exit Function
    End If

    Debug.Print connFileDB
    Select Case FileType
    Case ftXLS
    Call RsData.Open("SELECT * FROM [A:Q]", connFileDB)
    Case ftCSV
    Call RsData.Open("SELECT * FROM " & FileName, connFileDB)
    Case ftTOTA
    Call RsData.Open("SELECT * FROM " & FileName, connFileDB)
    End Select



    ConnFileDB as the following
    Provider=MSDASQL.1;Extended Properties="DBQ=C:\Documents and Settings\LMCamoes\Desktop\Totta\;DefaultDir=C:\Documents and Settings\LMCamoes\Desktop\Totta;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=asc,csv,tab,txt;MaxBufferSize=2048;PageTimeout=5;"


    Can you help me?

    Thank you

  2. #2
    Lively Member
    Join Date
    Feb 2005
    Location
    California
    Posts
    97

    Re: Query must have at least one destination field

    Where is the variable 'FileName' being set?

    Create a variable for your SQL statement and build it before you open RsData so you can check it before it tries to open.

    When you open you database do RsData.Open [your SQL], connfiledb, adOpenStatic, adLockPessimistic, adCmdText

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2005
    Posts
    35

    Re: Query must have at least one destination field

    I changes slightly the code and I have the same error.

    What I have now is:

    Public Function AppendElement(elemento As ELEMENT) As ERROR_CODE
    On Error GoTo err
    'Creates a recordset to write the data to the file DB
    Dim RsData As New ADODB.Recordset
    With RsData
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockOptimistic
    End With
    If DBPresent = False Then
    AppendElement = DB_NOT_OPEN
    Exit Function
    End If

    Debug.Print connFileDB
    Dim sqlstr As String
    sqlstr = "SELECT * FROM " & FileName
    Debug.Print sqlstr
    Select Case FileType
    Case ftXLS
    Call RsData.Open("SELECT * FROM [A:Q]", connFileDB)
    Case ftCSV
    Call RsData.Open("SELECT * FROM " & FileName, connFileDB)
    Case ftTOTA
    RsData.Open sqlstr, connFileDB, adOpenStatic, adLockPessimistic, adCmdText
    ' Call RsData.Open("SELECT * FROM " & FileName, connFileDB)
    End Select
    RsData.AddNew
    ...

    Where sqlstr is:
    "SELECT * FROM teste.CSV"

    and connFileDB is :

    Provider=MSDASQL.1;Extended Properties="DBQ=C:\Documents and Settings\LMCamoes\Desktop\Totta\;DefaultDir=C:\Documents and Settings\LMCamoes\Desktop\Totta;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=asc,csv,tab,txt;MaxBufferSize=2048;PageTimeout=5;"

    but when it tries to open the recordset gives the error...

    Please help me

  4. #4
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Query must have at least one destination field

    On what specific line does the error occurs?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2005
    Posts
    35

    Re: Query must have at least one destination field

    the error append when I try to open the recodset.

  6. #6
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Query must have at least one destination field

    This error will occur if the file is empty. The header row (first line) in the file is assumed to be the field names.

    I think there is an extended property you can use to indicate there is no header row in the file but I can't remember the syntax. I think its just HDR=NO.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width