Also can copy the output text and past it into interdev or mysql and test it. It reports as being ok. ----- Original Message ----- From: "Andy Parry" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Sunday, February 10, 2002 10:29 PM Subject: RE: Help with insert statment into sql 2000
> 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
