Converting Access to SQL

[Howannoying]

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

[Newsgroup_User]

> 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

[Howannoying]

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

[Newsgroup_User]

> when the error occurs.

What error? What is the error message?

--
Adam

[Kapitaine]

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.

[Howannoying]

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

[Howannoying · Accepted solution]

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

[Newsgroup_User]

> 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