• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

cfqueryparam

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?
0
halfbaked
Asked:
halfbaked
1 Solution
 
SidFishesCommented:
access is even really supported any more but you can have a look at the datatype matrix here

http://cfsearching.blogspot.com/2007/12/cfqueryparam-matrix-for-ms-access.html

(jet being the appropriate column)
0
 
halfbakedAuthor Commented:
.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now