David Maybe there are some " or ' quote marks (or comma) in your data, resultin g in this error. Are all these fields defined as text in the table ? a variable such as Pr ice is likely to be numeric and will not like being enclosed in quote marks.
hth, andy > -----Original Message----- > From: David Brown [mailto:[EMAIL PROTECTED]] > Sent: Monday, 11 February 2002 9:31 AM > To: CF-Talk > Subject: Re: Help with insert statment into sql 2000 > > > Insert into dbo.cdm > (FAC,DPT,REV,FIM,F5,SIM,SimDescription,UP,HCPCS,PRICE,OpPrice,PRO, > SUPP,Supp2 > ) > values > ('B', 'BRB', '6005', '600510023', '*', '10023', '7 AV.WEST LAT.PSYCH > OBSERVATION', '0762', '', '75000', '', 'NO', '2', '04/29/2001') > > is now my output and here is my error code > OLEDB Error Code = 109 > There are more columns in the INSERT statement than values > specified in the > VALUES clause. The number of values in the VALUES clause must match the > number of columns specified in the INSERT statement. > > > > > The error occurred while processing an element with a general > identifier of > (CFQUERY), occupying document position (38:2) to (38:60). > > > > Here my code. > > <cffile action="READ" file="\\prmhtstr\dsweb\aaaweb2.txt" > variable="aaaweb"> > <!--- Get Column Names, Data Types from Form Field TableName and > DSN(should > be an hidden form fields ---> > <cfquery name="GetTableInfo"datasource="DSSWeb"> > sp_columns CDM > </cfquery> > > <cfquery name="Delete" datasource="DSSWeb"> > DELETE > FROM dbo.cdm > </cfquery> > > <cfset ColumnHeader = ""> > <cfset Counter = 0> > <cfset ColumnData = ""> > <cfset ColNumb =0> > > > <cfloop query="GETTableInfo"> > <cfset ColumnHeader = ColumnHeader &"," & column_name> > </cfloop> > > <cfset ColumnHeader = Replace(ColumnHeader,Left(ColumnHeader,1),"")> > > <p> > <cfoutput> > <cfloop index="RowInfo" list="#aaaweb#" delimiters="#chr(10)#"> > <cfset RowInfo = Replace(RowInfo,chr(9),"|","ALL")> > <!--- #RowInfo#<br> ---> > <cfloop index="Column" list="#RowInfo#" delimiters="|"> > <cfset ColumnData = Replace(RowInfo,"|","', '","ALL")> > </cfloop> > > <!--- <cfset ColumnData = Replace(columnData,Left(ColumnData,1),"")> ---> > Insert into dbo.cdm<br> > (#ColumnHeader#)<br> > values<br> > ('#RTrim(ColumnData)#')<br> > <cfquery name="Insert" datasource="DSSWeb2" dbtype="OLEDB"> > Insert Into dbo.CDM > (#ColumnHeader#) > Values > ('#RTrim(ColumnData)#')</cfquery> > > <cfset ColumnData = ""> > <cfset Counter = 0> > <cfset ColNumb = 0> > <br> > </cfloop> > > </cfoutput> > </p> > > ----- Original Message ----- > From: "Andy Parry" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Sunday, February 10, 2002 7:56 PM > Subject: RE: Help with insert statment into sql 2000 > > > > Hi david, > > you need commas to separate the values in the value list, and individ ual > > quotation marks around character fields. > > You have a single string as a value, hence the SQL server > thinks that you > > are only submitting a single value, yet your insert statement is sayi ng > that > > you are sending 14 values. > > > > HTH > > andy > > > > > > > -----Original Message----- > > > From: David Brown [mailto:[EMAIL PROTECTED]] > > > Sent: Monday, 11 February 2002 8:49 AM > > > To: CF-Talk > > > Subject: Help with insert statment into sql 2000 > > > > > > > > > Help. > > > I get this error every time I try to insert into my table. > > > OLEDB Error Code = 109 > > > There are more columns in the INSERT statement than values > > > specified in the > > > VALUES clause. The number of values in the VALUES clause must > match the > > > number of columns specified in the INSERT statement. > > > > > > Here the output of my sql statment. > > > > > > Insert into dbo.cdm > > > (FAC, DPT, REV, FIM, F5, SIM, SimDescription, UP, HCPCS, > PRICE, OpPrice, > > > PRO, SUPP, Supp2) > > > values > > > ('B BRB 6005 600510023 * 10023 7 AV_WEST LAT_PSYCH OBSERVATION > > > 0762 75000 NO > > > 2 04/29/2001') > > > > > > > > > ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
