RE: escaping character
I am really lost here - I have read through the livedocs article on cfqueryparam and it seems that it is meant to go to the right of a where clause in a cfquery statement. I am really not sure how/why to use it. You simply use it anywhere you would otherwise use a CF variable within your SQL statement. For example: WHERE MyID = #Form.MyID# becomes: WHERE MyID = cfqueryparam cfsqltype=cf_sql_integer value=#Form.MyID# Note that you don't use single quotes around CFQUERYPARAM, even when working with string or date values. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
I am really lost here - I have read through the livedocs article on cfqueryparam and it seems that it is meant to go to the right of a where clause in a cfquery statement. I am really not sure how/why to use it. Use CFQUERYPARAM !! put the #qData.xxx# in the value attribute and use the appropriate cfsqltype Pascal -Original Message- From: Brant Winter [mailto:[EMAIL PROTECTED] Sent: 06 September 2004 13:26 To: CF-Talk Subject: RE: escaping character What am i doing wrong here ? Sorry about the huge post, but I just cant see what I am missing now ? The error in line 57 refers to a different field than the exception message showing an address Syntax error (missing operator) in query _expression_ '2 HOLBOURN CRES'. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
VALUES ( #qData.SRXNO#, #qData.SDISPDATE#, #qData.PTITLE#,. Becomes something like VALUES ( cfqueryparam cfsqltype=CF_SQL_INTEGER name=#qData.SRXNO#, cfqueryparam cfsqltype=CF_SQL_TIMESTAMP name=#qData. SDISPDATE #, cfqueryparam cfsqltype=CF_SQL_VARCHAR name=#qData. PTITLE #,. Looks a bit complex huh? But you get used to it. Watch for curly quotes in there. _ From: Brant Winter [mailto:[EMAIL PROTECTED] Sent: Wednesday, 8 September 2004 3:56 p.m. To: CF-Talk Subject: RE: escaping character I am really lost here - I have read through the livedocs article on cfqueryparam and it seems that it is meant to go to the right of a where clause in a cfquery statement. I am really not sure how/why to use it. Use CFQUERYPARAM !! put the #qData.xxx# in the value attribute and use the appropriate cfsqltype Pascal -Original Message- From: Brant Winter [mailto:[EMAIL PROTECTED] Sent: 06 September 2004 13:26 To: CF-Talk Subject: RE: escaping character What am i doing wrong here ? Sorry about the huge post, but I just cant see what I am missing now ? The error in line 57 refers to a different field than the exception message showing an address Syntax error (missing operator) in query _expression_ '2 HOLBOURN CRES'. _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
OK great - I will give this a go, it seems a bit painstaking though Kind Regards, Brant Winter _ From: Matthew Walker [mailto:[EMAIL PROTECTED] Sent: Wednesday, 8 September 2004 1:54 PM To: CF-Talk Subject: RE: escaping character VALUES ( #qData.SRXNO#, #qData.SDISPDATE#, #qData.PTITLE#,. Becomes something like VALUES ( cfqueryparam cfsqltype=CF_SQL_INTEGER name=#qData.SRXNO#, cfqueryparam cfsqltype=CF_SQL_TIMESTAMP name=#qData. SDISPDATE #, cfqueryparam cfsqltype=CF_SQL_VARCHAR name=#qData. PTITLE #,. Looks a bit complex huh? But you get used to it. Watch for curly quotes in there. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. It is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. Please carry out such virus and other checks, as you consider appropriate. To the fullest extent allowed by law, no responsibility is accepted by Haematology Oncology Clinics of Australasia for any virus damage caused by this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
Worked beautifully ! Had to change name= to value= though :) _ From: Matthew Walker [mailto:[EMAIL PROTECTED] Sent: Wednesday, 8 September 2004 1:54 PM To: CF-Talk Subject: RE: escaping character VALUES ( #qData.SRXNO#, #qData.SDISPDATE#, #qData.PTITLE#,. Becomes something like VALUES ( cfqueryparam cfsqltype=CF_SQL_INTEGER name=#qData.SRXNO#, cfqueryparam cfsqltype=CF_SQL_TIMESTAMP name=#qData. SDISPDATE #, cfqueryparam cfsqltype=CF_SQL_VARCHAR name=#qData. PTITLE #,. Looks a bit complex huh? But you get used to it. Watch for curly quotes in there. _ From: Brant Winter [mailto:[EMAIL PROTECTED] Sent: Wednesday, 8 September 2004 3:56 p.m. To: CF-Talk Subject: RE: escaping character I am really lost here - I have read through the livedocs article on cfqueryparam and it seems that it is meant to go to the right of a where clause in a cfquery statement. I am really not sure how/why to use it. Use CFQUERYPARAM !! put the #qData.xxx# in the value attribute and use the appropriate cfsqltype Pascal -Original Message- From: Brant Winter [mailto:[EMAIL PROTECTED] Sent: 06 September 2004 13:26 To: CF-Talk Subject: RE: escaping character What am i doing wrong here ? Sorry about the huge post, but I just cant see what I am missing now ? The error in line 57 refers to a different field than the exception message showing an address Syntax error (missing operator) in query _expression_ '2 HOLBOURN CRES'. _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
Yeah it is! In case you haven't seen it. http://www.macromedia.com/devnet/mx/coldfusion/articles/ben_forta_faster.htm l _ From: Brant Winter [mailto:[EMAIL PROTECTED] Sent: Wednesday, 8 September 2004 4:26 p.m. To: CF-Talk Subject: RE: escaping character OK great - I will give this a go, it seems a bit painstaking though Kind Regards, Brant Winter _ From: Matthew Walker [mailto:[EMAIL PROTECTED] Sent: Wednesday, 8 September 2004 1:54 PM To: CF-Talk Subject: RE: escaping character VALUES ( #qData.SRXNO#, #qData.SDISPDATE#, #qData.PTITLE#,. Becomes something like VALUES ( cfqueryparam cfsqltype=CF_SQL_INTEGER name=#qData.SRXNO#, cfqueryparam cfsqltype=CF_SQL_TIMESTAMP name=#qData. SDISPDATE #, cfqueryparam cfsqltype=CF_SQL_VARCHAR name=#qData. PTITLE #,. Looks a bit complex huh? But you get used to it. Watch for curly quotes in there. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. It is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. Please carry out such virus and other checks, as you consider appropriate. To the fullest extent allowed by law, no responsibility is accepted by Haematology Oncology Clinics of Australasia for any virus damage caused by this message. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
Your problem is with how cf treats lists. The delimiter can't be present
in a value, or it will be treated as separate elements. You will need to
use a proper csv parser that allows a text qualifier (). Have you
considered CFHTTP to read the csv into a query object? In a recent
tread, someone suggested a Java csv parser, but I don't remember which
one.
Pascal
-Original Message-
From: Brant Winter [mailto:[EMAIL PROTECTED]
Sent: 06 September 2004 05:55
To: CF-Talk
Subject: RE: escaping character
Actually - I have just found the issue. I am using the listfix udf :-
http://www.cflib.org/udf.cfm/listfix
The problem is that, the string I was trying to insert actually has a
single
comma in the csv file:-
BLEOMYCIN (BL) INJ 15,000 i.u.
Is there anyway to exclude listfix from picking this up as a separate
field
??
My code is:
cffile action="" file=#ExpandPath('/pharma/uploads')#/Book1.csv
variable=csvData
!--- cfset csvData = Replace(csvData, ,,, , ,, ALL) ---
cfset csvData = #listfix(csvData)#
Should I be using the commented code instead
Thanks again
The information transmitted is intended only for the person or entity
to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of,
or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you
received
this in error, please contact the sender and delete the material from
any
computer. It is the responsibility of the recipient to ensure that the
onward transmission, opening or use of this message and any
attachments
will not adversely affect its systems or data. Please carry out such
virus
and other checks, as you consider appropriate. To the fullest extent
allowed by law, no responsibility is accepted by Haematology
Oncology
Clinics of Australasia for any virus damage caused by this message.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: escaping character
I am not too sure what a proper csv parser is ? I assumed that the code:
cffile action="" file=#ExpandPath('/pharma/uploads')#/Book1.csv
variable=csvData
was properly parsing csv data ? I am also a bit shady on exactly what is a
text qualifier. I will look into CFHTTP but I don't know what you mean by
reading the csv as a query object ?
I will Google on !
_
From: Pascal Peters [mailto:[EMAIL PROTECTED]
Sent: Monday, 6 September 2004 6:08 PM
To: CF-Talk
Subject: RE: escaping character
Your problem is with how cf treats lists. The delimiter can't be present
in a value, or it will be treated as separate elements. You will need to
use a proper csv parser that allows a text qualifier (). Have you
considered CFHTTP to read the csv into a query object? In a recent
tread, someone suggested a Java csv parser, but I don't remember which
one.
Pascal
-Original Message-
From: Brant Winter [mailto:[EMAIL PROTECTED]
Sent: 06 September 2004 05:55
To: CF-Talk
Subject: RE: escaping character
Actually - I have just found the issue. I am using the listfix udf :-
http://www.cflib.org/udf.cfm/listfix
The problem is that, the string I was trying to insert actually has a
single
comma in the csv file:-
BLEOMYCIN (BL) INJ 15,000 i.u.
Is there anyway to exclude listfix from picking this up as a separate
field
??
My code is:
cffile action="" file=#ExpandPath('/pharma/uploads')#/Book1.csv
variable=csvData
!--- cfset csvData = Replace(csvData, ,,, , ,, ALL) ---
cfset csvData = #listfix(csvData)#
Should I be using the commented code instead
Thanks again
The information transmitted is intended only for the person or entity
to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of,
or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you
received
this in error, please contact the sender and delete the material from
any
computer. It is the responsibility of the recipient to ensure that the
onward transmission, opening or use of this message and any
attachments
will not adversely affect its systems or data. Please carry out such
virus
and other checks, as you consider appropriate. To the fullest extent
allowed by law, no responsibility is accepted by Haematology
Oncology
Clinics of Australasia for any virus damage caused by this message.
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: escaping character
The cffile just reads the file, it doesn't parse anything.
A text qualifier is a character you place around text in a csv (usually
). When you have a comma in the text (surrounded by quotes), it means
it is just a comma, not a delimiter.
A,B,C,D == 4 elements ABCD
A,B,C,D == 3 elements AB,CD
A query object in cf is what you would get as a result from a cfquery.
Pascal
-Original Message-
From: Brant Winter [mailto:[EMAIL PROTECTED]
Sent: 06 September 2004 10:42
To: CF-Talk
Subject: RE: escaping character
I am not too sure what a proper csv parser is ? I assumed that the
code:
cffile action="" file=#ExpandPath('/pharma/uploads')#/Book1.csv
variable=csvData
was properly parsing csv data ? I am also a bit shady on exactly what
is a
text qualifier. I will look into CFHTTP but I don't know what you mean
by
reading the csv as a query object ?
I will Google on !
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: escaping character
Hi Brant,
Is this a one-off import or will this be a regular part of the system?
- Original Message -
From: Brant Winter [EMAIL PROTECTED]
Date: Mon, 6 Sep 2004 18:41:36 +1000
Subject: RE: escaping character
To: CF-Talk [EMAIL PROTECTED]
I am not too sure what a proper csv parser is ? I assumed that the code:
cffile action="" file=#ExpandPath('/pharma/uploads')#/Book1.csv
variable=csvData
was properly parsing csv data ? I am also a bit shady on exactly what is a
text qualifier. I will look into CFHTTP but I don't know what you mean by
reading the csv as a query object ?
I will Google on !
_
From: Pascal Peters [mailto:[EMAIL PROTECTED]
Sent: Monday, 6 September 2004 6:08 PM
To: CF-Talk
Subject: RE: escaping character
Your problem is with how cf treats lists. The delimiter can't be present
in a value, or it will be treated as separate elements. You will need to
use a proper csv parser that allows a text qualifier (). Have you
considered CFHTTP to read the csv into a query object? In a recent
tread, someone suggested a Java csv parser, but I don't remember which
one.
Pascal
-Original Message-
From: Brant Winter [mailto:[EMAIL PROTECTED]
Sent: 06 September 2004 05:55
To: CF-Talk
Subject: RE: escaping character
Actually - I have just found the issue. I am using the listfix udf :-
http://www.cflib.org/udf.cfm/listfix
The problem is that, the string I was trying to insert actually has a
single
comma in the csv file:-
BLEOMYCIN (BL) INJ 15,000 i.u.
Is there anyway to exclude listfix from picking this up as a separate
field
??
My code is:
cffile action="" file=#ExpandPath('/pharma/uploads')#/Book1.csv
variable=csvData
!--- cfset csvData = Replace(csvData, ,,, , ,, ALL) ---
cfset csvData = #listfix(csvData)#
Should I be using the commented code instead
Thanks again
The information transmitted is intended only for the person or entity
to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of,
or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you
received
this in error, please contact the sender and delete the material from
any
computer. It is the responsibility of the recipient to ensure that the
onward transmission, opening or use of this message and any
attachments
will not adversely affect its systems or data. Please carry out such
virus
and other checks, as you consider appropriate. To the fullest extent
allowed by law, no responsibility is accepted by Haematology
Oncology
Clinics of Australasia for any virus damage caused by this message.
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: escaping character
So when you say to read the csv as a query object what exactly do you mean ?Do you mean treat the data as if it came straight out of a cfquery statement ? I am confused ! The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. It is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. Please carry out such virus and other checks, as you consider appropriate. To the fullest extent allowed by law, no responsibility is accepted by Haematology Oncology Clinics of Australasia for any virus damage caused by this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: escaping character
Brant Winter wrote: So when you say to read the csv as a query object what exactly do you mean ?Do you mean treat the data as if it came straight out of a cfquery statement ? I am confused ! Pascal made a minor typo. A query object in cf is what you would get as a result from a cfquery. should read A query object in cf is what you would get as a result from a cfhttp. CFFILE will read your file into a variable in one big block. If you use CFHTTP to read your file in, it is possible to put your CSV data straight into a cfquery object.You can then loop through your query object and insert the data into the database.You should find this easier than trying to parse your CSV file manually. Have a read up on CFHTTP and you should see what Pascal is trying to say to you. Stephen [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
It will be a regular transfer, almost on a daily basis. I am not sure if I am going to be able to get the other party to supply the data with double quotes and commas as in a text qualifier. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. It is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. Please carry out such virus and other checks, as you consider appropriate. To the fullest extent allowed by law, no responsibility is accepted by Haematology Oncology Clinics of Australasia for any virus damage caused by this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
Yes, it would read the data as if it came from a database table using cfquery. cfhttp url="" name=qData columns=columns here if first line not column names textqualifier= delimiter=, resolveurl=no timeout=20 charset=charset of the page/cfhttp cfdump var=#qData# Replace the values with your own and test to see the result. The csv file must be accessible from the web (using a fully qualified url). Pascal -Original Message- From: Brant Winter [mailto:[EMAIL PROTECTED] Sent: 06 September 2004 11:20 To: CF-Talk Subject: RE: escaping character So when you say to read the csv as a query object what exactly do you mean ?Do you mean treat the data as if it came straight out of a cfquery statement ? I am confused ! [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: escaping character
A simple solution would be to see if your data supplier can use a different delimiter. Instead of a comma, if they can use a '|' (pipe symbol found to left of 'Z' on keyboard) or a ~ (tilda) character then these tend not to crop up in 'ordinary text'. You could 'parse' the data yourself.That is look for the text qualified using the find function, mark this character position as the start of a text field and look for the closing , mark this as the end, now for the characters within this region strip out any delimiter characters.Repeat for rest of string. - Original Message - From: Brant Winter [EMAIL PROTECTED] Date: Mon, 6 Sep 2004 19:31:09 +1000 Subject: RE: escaping character To: CF-Talk [EMAIL PROTECTED] It will be a regular transfer, almost on a daily basis. I am not sure if I am going to be able to get the other party to supply the data with double quotes and commas as in a text qualifier. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. It is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. Please carry out such virus and other checks, as you consider appropriate. To the fullest extent allowed by law, no responsibility is accepted by Haematology Oncology Clinics of Australasia for any virus damage caused by this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
Wo - why does that work ? I specify the delimiter only ( as the data didn't have a text qualifier ) and the query output is spot on now ??? cfhttp url="" name=qData delimiter=, resolveurl=no timeout=20/cfhttp The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. It is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. Please carry out such virus and other checks, as you consider appropriate. To the fullest extent allowed by law, no responsibility is accepted by Haematology Oncology Clinics of Australasia for any virus damage caused by this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
I think is the default qualifier. You can even drop the delimiter (I think , is the default). Pascal -Original Message- From: Brant Winter [mailto:[EMAIL PROTECTED] Sent: 06 September 2004 12:53 To: CF-Talk Subject: RE: escaping character Wo - why does that work ? I specify the delimiter only ( as the data didn't have a text qualifier ) and the query output is spot on now ??? cfhttp url="" name=qData delimiter=, resolveurl=no timeout=20/cfhttp The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. It is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. Please carry out such virus and other checks, as you consider appropriate. To the fullest extent allowed by law, no responsibility is accepted by Haematology Oncology Clinics of Australasia for any virus damage caused by this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
What am i doing wrong here ? Sorry about the huge post, but I just cant see what I am missing now ? The error in line 57 refers to a different field than the exception message showing an address Syntax error (missing operator) in query _expression_ '2 HOLBOURN CRES'. Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query _expression_ '2 HOLBOURN CRES'. The error occurred in C:\Inetpub\wwwroot\HOCACF\pharma\test.cfm: line 57 55 : #qData.SAUTHORITY#, 56 : #qData.SSIGS#, 57 : #qData.SWARDNO# ) 58 : 59 : /cfquery cfhttp url="" name=qData delimiter=, resolveurl=no timeout=20/cfhttp !--- cfdump var=#qData# --- cfloop query=qData cfquery datasource=obscript INSERT INTO scripts ( SRXNO, SDISPDATE, PTITLE, PFIRSTNAME, PSURNAME, PADDRESS, PSUBURB, PPOSTCODE, PMEDICARE, PMEDICDATE, PCONCTYPE, PCONCNO, PCONCVDATE, PSAFENTNO, PREPATNO, SDOCPRESNO, SDOCNAME, SDRUGDESC, SALTDESC, SQTY, SAUTHORITY, SSIGS, SWARDNO ) VALUES ( #qData.SRXNO#, #qData.SDISPDATE#, #qData.PTITLE#, #qData.PFIRSTNAME#, #qData.PSURNAME#, #qData.PADDRESS#, #qData.PSUBURB#, #qData.PPOSTCODE#, #qData.PMEDICARE#, #qData.PMEDICDATE#, #qData.PCONCTYPE#, #qData.PCONCNO#, #qData.PCONCVDATE#, #qData.PSAFENTNO#, #qData.PREPATNO#, #qData.SDOCPRESNO#, #qData.SDOCNAME#, #qData.SDRUGDESC#, #qData.SALTDESC#, #qData.SQTY#, #qData.SAUTHORITY#, #qData.SSIGS#, #qData.SWARDNO# ) /cfquery /cfloop The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. It is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. Please carry out such virus and other checks, as you consider appropriate. To the fullest extent allowed by law, no responsibility is accepted by Haematology Oncology Clinics of Australasia for any virus damage caused by this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: escaping character
Brant Winter wrote: What am i doing wrong here ? Sorry about the huge post, but I just cant see what I am missing now ? The error in line 57 refers to a different field than the exception message showing an address Syntax error (missing operator) in query _expression_ '2 HOLBOURN CRES'. cfhttp url="" name=qData delimiter=, resolveurl=no timeout=20/cfhttp !--- cfdump var=#qData# --- cfloop query=qData cfquery datasource=obscript INSERT INTO scripts ( SRXNO, SDISPDATE, PTITLE, PFIRSTNAME, PSURNAME, PADDRESS, PSUBURB, PPOSTCODE, PMEDICARE, PMEDICDATE, PCONCTYPE, PCONCNO, PCONCVDATE, PSAFENTNO, PREPATNO, SDOCPRESNO, SDOCNAME, SDRUGDESC, SALTDESC, SQTY, SAUTHORITY, SSIGS, SWARDNO ) VALUES ( #qData.SRXNO#, #qData.SDISPDATE#, #qData.PTITLE#, #qData.PFIRSTNAME#, #qData.PSURNAME#, #qData.PADDRESS#, #qData.PSUBURB#, #qData.PPOSTCODE#, #qData.PMEDICARE#, #qData.PMEDICDATE#, #qData.PCONCTYPE#, #qData.PCONCNO#, #qData.PCONCVDATE#, #qData.PSAFENTNO#, #qData.PREPATNO#, #qData.SDOCPRESNO#, #qData.SDOCNAME#, #qData.SDRUGDESC#, #qData.SALTDESC#, #qData.SQTY#, #qData.SAUTHORITY#, #qData.SSIGS#, #qData.SWARDNO# ) /cfquery /cfloop You need single quotes around your text and dates must in an appropriate format for your database. You should look into using CFQueryParam and possibly CreateODBCDateTime et al. Stephen [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
Just for testing I made a simple access db and made all the fields text fields. I tried putting single quotes around all of the #qData.xxx# and all I ended up with was qData.xxx in the resulting error message. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. It is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. Please carry out such virus and other checks, as you consider appropriate. To the fullest extent allowed by law, no responsibility is accepted by Haematology Oncology Clinics of Australasia for any virus damage caused by this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
Use CFQUERYPARAM !! put the #qData.xxx# in the value attribute and use the appropriate cfsqltype Pascal -Original Message- From: Brant Winter [mailto:[EMAIL PROTECTED] Sent: 06 September 2004 13:26 To: CF-Talk Subject: RE: escaping character What am i doing wrong here ? Sorry about the huge post, but I just cant see what I am missing now ? The error in line 57 refers to a different field than the exception message showing an address Syntax error (missing operator) in query _expression_ '2 HOLBOURN CRES'. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: escaping character
Brant Winter wrote: Just for testing I made a simple access db and made all the fields text fields. I tried putting single quotes around all of the #qData.xxx# and all I ended up with was qData.xxx in the resulting error message. If you choose to go the way of the single quotes, you should only add them around text fields, not around numbers and dates. However, it is *highly* recommended to use cfqueryparam. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: escaping character
With regards to this thread, I am about to embark on doing alittle functionality to a site where people will be able to upload CSV files. There will be different CSV types for different data and I wondered if a) there are some good CSV functions out there for parsing into db or query (different delimiters, text qualifiers etc) b) if anyone has done a template to check a CSV against? What I mean is this CSV should have x number of columns and they are x, y, z etc Regards Mark Drew On Mon, 06 Sep 2004 13:47:39 +0200, Jochem van Dieten [EMAIL PROTECTED] wrote: Brant Winter wrote: Just for testing I made a simple access db and made all the fields text fields. I tried putting single quotes around all of the #qData.xxx# and all I ended up with was qData.xxx in the resulting error message. If you choose to go the way of the single quotes, you should only add them around text fields, not around numbers and dates. However, it is *highly* recommended to use cfqueryparam. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: escaping character
Mark, Don't quote me on this, you'll have to double check, but in reference to item 'a' of your list I think I remember seeing a UDF function library at cflib.org for handling CSV files. If that wasn't it then I may have seen a CFC somewhere (cfczone.org maybe). There are resources out there... Cutter Mark Drew wrote: With regards to this thread, I am about to embark on doing alittle functionality to a site where people will be able to upload CSV files. There will be different CSV types for different data and I wondered if a) there are some good CSV functions out there for parsing into db or query (different delimiters, text qualifiers etc) b) if anyone has done a template to check a CSV against? What I mean is this CSV should have x number of columns and they are x, y, z etc Regards Mark Drew On Mon, 06 Sep 2004 13:47:39 +0200, Jochem van Dieten [EMAIL PROTECTED] wrote: Brant Winter wrote: Just for testing I made a simple access db and made all the fields text fields. I tried putting single quotes around all of the #qData.xxx# and all I ended up with was qData.xxx in the resulting error message. If you choose to go the way of the single quotes, you should only add them around text fields, not around numbers and dates. However, it is *highly* recommended to use cfqueryparam. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: escaping character
Actually - I have just found the issue. I am using the listfix udf :-
http://www.cflib.org/udf.cfm/listfix
The problem is that, the string I was trying to insert actually has a single
comma in the csv file:-
BLEOMYCIN (BL) INJ 15,000 i.u.
Is there anyway to exclude listfix from picking this up as a separate field
??
My code is:
cffile action="" file=#ExpandPath('/pharma/uploads')#/Book1.csv
variable=csvData
!--- cfset csvData = Replace(csvData, ,,, , ,, ALL) ---
cfset csvData = #listfix(csvData)#
Should I be using the commented code instead
Thanks again
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. It is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. Please carry out such virus and other checks, as you consider appropriate. To the fullest extent allowed by law, no responsibility is accepted by Haematology Oncology Clinics of Australasia for any virus damage caused by this message.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
