Skip to main content
H
Howannoying
Inspiring
February 6, 2009
Answered

Converting Access to SQL

  • February 6, 2009
  • 4 replies
  • 535 views
I am converting an access to sql db. My access in one of the tables, uses the column name current. When it transferred over to sql it made it [current] because its a reserved word in sql.

I am pulling 20+ access dbs into one sql so each of these db's has the table with the word current in it. I dont want to have to go into each db and change each one.

I have tried '#a.[current]#', but receive A CFML variable name cannot end with a "." character.
I tried no quote marks and even double quote marks but to no avail.

Can anyone help?

Thanks in advance.

Sharon
    This topic has been closed for replies.
    Correct answer Howannoying
    I figured it out. I was focusing on the input of the value...not the actual name of the column

    I changed it from current to [current] and it worked.

    Thank you all who replied

    4 replies

    N
    Newsgroup_User
    Inspiring
    February 7, 2009
    > I figured it out.

    Sorry, I asked my last Q re the error msg before seeing this. Ignore.

    Still: when claiming you're getting an error, it always helps to actually
    post what the error is ;-)

    --
    Adam
    H
    HowannoyingAuthorCorrect answer
    Inspiring
    February 6, 2009
    I figured it out. I was focusing on the input of the value...not the actual name of the column

    I changed it from current to [current] and it worked.

    Thank you all who replied
    A
    Anonymous
    February 6, 2009
    Try this:

    #a[current]#

    Without the period. Or maybe if you are using it directly in the SQL query it could be:

    [#a.current#]

    Not too sure.
    H
    HowannoyingAuthor
    Inspiring
    February 6, 2009
    I was hopeful...It seemed like such an easy fix...to no avail :) I tried every variation but oh well. My only option is to create another field and rename it something not reserved, then transfer the info into that field and then transfer to sql.

    Long and tedious but if it must be done.....


    Thanks

    Sharon
    N
    Newsgroup_User
    Inspiring
    February 6, 2009
    > I have tried '#a.[current]#', but receive A CFML variable name cannot end
    > with a "." character.
    > I tried no quote marks and even double quote marks but to no avail.

    I just set up a test rig on my SQL Server 2005 instance, thus:


    /* table */
    CREATE TABLE dbo.tbl_current2(
    id int NOT NULL,
    [current] varchar(50) NULL
    )

    /* data */
    1, "one"
    2, "two"
    3, "three"

    <!--- CF code --->
    <cfquery name="q" datasource="scratch">
    select id, [current]
    from tbl_current
    </cfquery>

    <cfloop query="q">
    <cfoutput>Current: #current# #q.current# #q.current[currentRow]#
    #q["current"][currentRow]#<br /></cfoutput>
    </cfloop>
    <cfdump var="#q#">

    All of those options work, which is made obvious if one looks at the
    contents of the <cfdump>.

    If every you find yourself wondering how to access data (ie, whether touse
    "[current]" or "current") from a complex data structure, the best thing to
    do first is to eyeball it. That makes life a lot easier.

    --
    Adam
    H
    HowannoyingAuthor
    Inspiring
    February 6, 2009
    Thank you for your effort.

    I am about to output the data with no problem. I did double check after your msg.

    I am trying to move the data from an access to sql database.

    I pull the data from access via query
    but when I attempt to input it into the sql database is when the error occurs.

    *******************************************************
    <cfquery name="aa" datasource="smdmain">
    insert into season
    (current,view,previous,next,siteid,sport)
    Values
    (#a.current#','#a.view#','#a.previous#','#a.next#','#foo#','#a.sport#')
    </cfquery>
    ******************************************************

    I tried using variations with [] but again to no avail.

    Thank you

    Sharon
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices