Posted on 2009-12-16
Last Modified: 2013-12-24
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'
      <cffile action="upload" destination="C:\Inetpub\wwwroot\\datasources\" filefield="filename" nameconflict="overwrite">
      <cffile action="read" file="C:\Inetpub\wwwroot\\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">
      Data inserted into the database!!
      <form name="frmData" method="post" enctype="multipart/form-data">
            <input type="file" name="filename"><br />
            <input type="submit">

any thoughts?
Question by:halfbaked
    LVL 36

    Accepted Solution

    access is even really supported any more but you can have a look at the datatype matrix here

    (jet being the appropriate column)

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
    Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now