Unable to Execute Insert Query using cfquery

[srimtm]

Hi

I am using coldfusion MX and backend MS-Access.I want to import the data from
excel file.so i am saving as Tablimited Text file and Retreving the data.Now i
want this data to be inserted into database. Select Queries are executing but
Insert Queries were not getting executed inside the loop.Can Anybody have an
idea please give me an idea.

Thanks & Regards
srinivas

[mxstu]

Can you post your code and any error messages and maybe someone can help you?

[The ScareCrow]

pasting the whole page like this is not much help

All we need is the code for the select and insert that is not working and any
error(s) that occur.

I did start to scan through the code, then when I had to start srolling
up/down/sideways I gave up, but I did notice

You have a number of these
<cfif not IsDefined("pg")><cfset ............

You can just use

<cfparam name="pg" default="default value">

Ken

[srimtm]

Hi ken

Thank you for ur suggestion. i am giving my code in brief here..can you go
thorough once and try to help me out if possible

<cfloop index="record" list="#String#" delimiters="#chr(13)##chr(10)#">
<cftry>
<cfif #listgetat(record,19,chr(9))# eq 0>
</cfif>
<cfcatch type="expression">
<cfset #number# = #cfcatch.ErrNumber#>
<cfset #message# = #cfcatch.message#>
<cfif #number# eq 0 or #message# eq ''>
<cflocation url="import-customers.cfm?sid=#sid#&pg=4">
</cfif>
</cfcatch>
</cftry>
<cfif #listgetat(record,19,chr(9))# neq 'AMOUNT'>
<cftry><cfset the_inv_date =
CreateODBCDateTime("#listgetat(record,2,chr(9))#") ><cfcatch><cfset the_inv_date
= "NULL"></cfcatch></cftry>
<cfset #the_invoicenum#=REReplace(Trim("#listgetat(record ,3,chr(9))#"),
"[^0-9]", "", "ALL")>
<cfset the_customer = Trim("#listgetat(record,4,chr(9))#")>
<cfset #the_amount#="#listgetat(record,19,chr(9))#">

<!--- ### Proceed? ### --->
<cfif the_customer is not "">

<!--- ### Retrieve Customer Information ### --->
<cfquery name="temp" dbtype="ODBC" datasource="#global.dsn#"
username="#global.dsnusername#" password="#global.dsnpassword#">
SELECT Customers.CustomerID
FROM (Customers INNER JOIN Contacts ON Customers.ContactID =
Contacts.ContactID)
WHERE (Contacts.Organization = '#the_customer#')
</cfquery>

<cfset customerid = temp.CustomerID>
<!--- ### Create Customer ### --->
<cfif temp.RecordCount is 0>
<!--- ### Retrieve ContactID ### --->
<cfquery name="temp" dbtype="ODBC" datasource="#global.dsn#"
username="#global.dsnusername#" password="#global.dsnpassword#">SELECT
Max(ContactID) AS MaxNum FROM Contacts</cfquery>
<cfset contactid = Val(temp.MaxNum) + 1>
<!--- ### Create Record ### --->
<cfquery name="insert" dbtype="ODBC" datasource="#global.dsn#"
username="#global.dsnusername#" password="#global.dsnpassword#">
INSERT INTO Contacts (ContactID, Created, Modified, FirstName, LastName,
Organization, Address1, City, State, Zipcode, Country, Phone1, Email1)
VALUES
(#contactid#,#the_datetime#,#the_datetime#,'#the_f irstname#',
'#the_lastname#','#the_customer#','#the_address#', '#the_city#',
'#the_state#','#the_zipcode#','US','#the_phone#',' #the_email#')
</cfquery>
<cfoutput>#insert.recordcount#..test</cfoutput>..<cfabort>

[The ScareCrow]

First thing is I will need to make some assumptions here, if they are wrong let
me know


If the select returns record(s) you set
<cfset customerid = temp.CustomerID>

If the select does not return a record, you do another query to get the maxid,
then you set
<cfset contactid = Val(temp.MaxNum) + 1>

In the insert query you use #contactid#

So one problem will be if contactid never gets set.

Now the assumptions.
I assume that the "ContactID" field in the table is an autonumber. If this is
correct then you can't set it in the insert query.

I'm also assuming that you have the cfif's and closing cfif's are correct.

Can you post what your trying to achieve, we may be able to suggest a better
logical model.

Ken

[cfmlgirl]

Can you user a build in wizard?