troubleshooting Question

cfqueryparam

Avatar of halfbaked
halfbaked asked on
Web Servers
2 Comments1 Solution657 ViewsLast Modified:
I've created a file upload that reads a .txt file and inserts the records into an access database.  i'm having a problem that if i set the field types to anything other than 'text' i get a data mismatch error even when i change the cfsqltype="cf_sql_longvarchar" to cfsqltype="cf_sql_numeric" where the field type is set to 'number', i've also tried cfsqltype="cf_sql_integer"

the fields set to number are 'productid', 'cost', 'RRP' and 'saleprice'


<cfif isDefined('form.filename')>
<cfquery datasource="bbd" name="del_ing">
delete from products2 where ingrams = 'Y'
</cfquery>
      <cffile action="upload" destination="C:\Inetpub\wwwroot\bigboxshop.co.uk\datasources\" filefield="filename" nameconflict="overwrite">
      <cffile action="read" file="C:\Inetpub\wwwroot\bigboxshop.co.uk\datasources\#cffile.ServerFile#" variable="fileData">
      <cfloop index="record" list="#fileData#" delimiters="#chr(10)##chr(13)#">
            <cfquery name="qryInsert" datasource="bbd">
            INSERT INTO products2 (productid, man, model, pricerunner, pricegrabber, instock, ingrams, category, categorymain, cost, RRP, manpartno, briefdesc, saleprice, image)
            VALUES (
                  <cfqueryparam value="#listgetat(record,5,chr(9))#" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="#listgetat(record,9,chr(9))#" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="#replace(listgetat(record,1,chr(9)),chr(34),'','all')#" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="No" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="No" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="Yes" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="Y" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="IT Equipment" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="IT Equipment" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="0" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="0" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="#listgetat(record,12,chr(9))#" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="#replace(listgetat(record,2,chr(9)),chr(34),'','all')#" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="#listgetat(record,7,chr(9))#" cfsqltype="cf_sql_longvarchar">,
                  <cfqueryparam value="#listgetat(record,4,chr(9))#" cfsqltype="cf_sql_longvarchar">
                   )    
            </cfquery>
      </cfloop>
      Data inserted into the database!!
<cfelse>
      <form name="frmData" method="post" enctype="multipart/form-data">
            <input type="file" name="filename"><br />
            <input type="submit">
      </form>
</cfif>

any thoughts?
ASKER CERTIFIED SOLUTION
SidFishes

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros