Inserting Table Row

scripter73 · Aug 1, 2001

Hi,

I have an Access database table that I’d like to insert a table row for.
I’ve created an ODBC datasource that verifies in Cold Fusion.

My problem is that with my existing code, my table row is not being inserted into the table.

I apologize in advance for the length of the post.

Below is the table format and following that is my existing code: (please read down one column and then the next column)

Field Name Datatype(Length)
----------- ----------------
ReportNumber Number Long(4)
PolicyNumber Text(13)
Mode Number Long(4)
Lossdate Date/Time(8)
Losstime Date/Time(8)
ReportedBy Text(47)
Status Text(10)
EntryTimeDate Date/Time(8)
Effdate Date/Time(8)
Expdate Date/Time(8)
Incident Text(60)
InsuredLastName Text(20)
InsuredMiddleI Text(1)
InsuredFirstName Text(10)
InsuredAddress Text(30)
InsuredCity Text(30)
InsuredState Text(2)
InsuredZip Number Double(8)
InsuredZip2 Number Double(8)
InsuredHomePhone Number Double(8)
InsuredWorkPhone Number Double(8)
ContactLastName Text(20)
ContactMiddleI Text(1)
ContactFirstName Text(10)
ContactAddress Text(30)
ContactCity Text(30)
ContactState Text(30)
ContactZip Number Double(8)
ContactZip2 Number Double(8)
ContactHomePhoneNumber Number Double(8)
ContactWorkPhoneNumber Number Double(8)
Insured Vehicle Year Number Double(8)
InsuredVin Text(20)
InsuredVehicleMake Text(4)
InsuredVehicleModel Text(10)
DriverLastName Text(20)
DriverMiddleI Text(1)
DriverFirstName Text(10)
DriverAddress Text(30)
DriverCity Text(20)
DriverState Text(2)
DriverZip Number Double(8)
DriverZip2 Number Double(8)
DriverHomePhoneNumber Number Double(8)
DriverWorkPhone Number Number Double(8)
DriverLicenseNumber Number Double(8)
DriverLicenseState Number Double(8)
DriverDOB Date/Time(8)
DriverExcluded Text(1)
ReportTakenBy Text(20)
ClaimStatus Text(10)
ClaimTeamCode Text(10)
ClaimTeamDesc Text(30)
AdjusterCode Text(10)
AdjusterDesc Text(20)
Producer Text(30)
ProducerPhoneNumber Number Double(8)
DriverSSN Number Double(8)
ReceivedDateTime Date/Time(8)
SendTo Text(8)
SendToDateTime Date/Time(8)
Endorsement Text(2)
ClaimNumber Number Double(8)
AffadavitNoLoss Text(34)

Here’s my code:

<body>


<cfset #session.entrytimedate# = "08/01/2001 10:30:00 AM">
<cfset #session.policyholder# = "GEORGE JETSON">
<cfset #session.policynumber# = "CGNCNS7500000">
<cfset #session.reportedby# = "Lois Lane">
<cfset #session.lossdate# = 073101>
<cfset #session.losshour# = "12">
<cfset #session.lossmin# = "00">
<cfset #session.timeofday# = "AM">
<cfset #session.insuredfname# = "George">
<cfset #session.insuredmi# = "M">
<cfset #session.insuredlname# = "Jetson">
<cfset #session.insuredaddr# = "3333 Green St.">
<cfset #session.insuredcity# = "Greenville">
<cfset #session.insuredstate# = "TX">
<cfset #session.insuredzip# = 76401>
<cfset #session.insuredphonearea# = 972>
<cfset #session.insuredphoneprefix# = 596>
<cfset #session.insuredphonesuffix# = 1234>
<cfset #session.insuredworkphonearea# = 972>
<cfset #session.insuredworkphoneprefix# = 571>
<cfset #session.insuredworkphonesuffix# = 5500>
<cfset #session.insuredworkext# = 123>
<cfset #session.insuredemail# = "lois@dailyplanet.com">
<cfset #session.contactfname# = "George">
<cfset #session.contactmi# = "M">
<cfset #session.contactlname# = "Jetson">
<cfset #session.contactaddr# = "3333 Green St.">
<cfset #session.contactcity# = "Greenville">
<cfset #session.contactstate# = "TX">
<cfset #session.contactzip# = 76401>
<cfset #session.contactphonearea# = 972>
<cfset #session.contactphoneprefix# = 596>
<cfset #session.contactphonesuffix# = 1234>
<cfset #session.contactworkphonearea# = 972>
<cfset #session.contactworkphoneprefix# = 571>
<cfset #session.contactworkphonesuffix# = 5500>
<cfset #session.contactworkext# = 123>
<cfset #session.caryear# = 1996>
<cfset #session.carmake# = "NISSAN">
<cfset #sesion.carmodel# = "Sentra">
<cfset #session.vin# = "ABCDEFGH12345678">

<cfset #session.driver# = "ELROY JETSON">
<cfset #session.driveraddr# = "123 Main St.">
<cfset #session.drivercity# = "Dallas">
<cfset #session.driverstate# = "TX">
<cfset #session.driverzip# = 75224>
<cfset #session.driverhomearea# = 214>
<cfset #session.driverhomeprefix# = 456>
<cfset #session.driverhomesuffix# = 1234>
<cfset #session.driverhomephone# = "#session.driverhomearea##session.driverhomeprefix##session.driverhomesuffix#">
<cfset #session.driverworkarea# = 972>
<cfset #session.driverworkprefix# = 555>
<cfset #session.driverworksuffix# = 7894>
<cfset #session.driverworkphone# = "#session.driverworkarea##session.driverworkprefix##session.driverworksuffix#">
<cfset #session.dlnum# = "123123123">
<cfset #session.dlstate# = "TX">
<cfset #session.driverdob# = 01/01/1981>
<cfset #session.driverexcl# = "N">
<cfset #session.driverssn# = 456778974>

<cfset #session.acclocation# = "Beltline/Arapaho">
<cfset #session.acccity# = "Dallas">
<cfset #session.accstate# = "TX">
<cfset #session.policecontact# = "YES">
<cfset #session.policedept# = "Dallas Police Dept.">
<cfset #session.policerpt# = "1234567890">
<cfset #session.accdesc# = "Proceeding North on Beltline Rd., and the light was green, and as I crossed the light, I was hit from behind.">
<cfset #session.witness1fname# = "Buggs">
<cfset #session.witness1lname# = "Bunny">
<cfset #session.witness1area# = "214">
<cfset #session.witness1prefix# = "742">
<cfset #session.witness1suffix# = "7894">
<cfset #session.witness2fname# = "Elmer">
<cfset #session.witness2lname# = "Fudd">
<cfset #session.witness2area# = "610">
<cfset #session.witness2prefix# = "310">
<cfset #session.witness2suffix# = "4561">


<cfset claimstatus = "Pending">
<cfset claimteam = "12">
<cfset claimteamdesc = "claimteam">
<cfset adjustercode = "Adj12">
<cfset adjusterdesc = "Adjuster1">
<cfset producer = "mainagent">
<cfset producerphone = 1234567899>
<cfset recvdate = 08/01/2001>
<cfset sendto = "aclaimadjuster">
<cfset sendtotimedate = "08/01/2001 10:30:00 AM">
<cfset endorsement = "00">
<cfset claimnum = "123456">
<cfset affnoloss = "Affadavit on File">
<cfset incident = "FENDER BENT">
<cfset effdate = 08/01/2001>
<cfset expdate = 08/02/2001>


<cfset #insuredhomephone# = #insuredphonearea##insuredphoneprefix##insuredphonesuffix#>
<cfset #insuredworkphone# = #insuredworkphonearea##insuredworkphoneprefix##insuredworkphonesuffix##insuredworkext#>
<cfset #contacthomephone# = #contactphonearea##contactphoneprefix##contactphonesuffix#>
<cfset #contactworkphone# = #contactworkphonearea##contactworkphoneprefix##contactworkphonesuffix##contactworkext#>
<cfset #drvfname# = ListFirst(session.driver, " &quot

>
<cfset #drvlname# = ListLast(session.driver, " &quot

>
<cfset #losstime# = #session.losshour#:#session.lossmin#>

<cfquery datasource="CCMSLRDTA" name="showclaims">
select policynumber,
insuredlastname,
insuredfirstname
from TBLCLAIMS
</cfquery>

<cfoutput>...Starting insert into TblClaims</cfoutput>
<cfquery datasource="CCMSLRDTA" name="updateclaims">
INSERT INTO TBLCLAIMS VALUES
(4055, '#session.policynumber#', 0, #session.lossdate#, #losstime#, #session.reportedby#, 'UNKNOWN', #session.entrytimedate#, #effdate#, #expdate#, '#incident#', '#session.insuredlname#', #session.insuredmi#, '#session.insuredfname#', '#session.insuredaddr#', '#session.insuredcity#', '#session.insuredstate#', #session.insuredzip#, 1234, #session.insuredhomephone#, #session.insuredworkphone#, '#session.contactlname#', '#contactmi#', '#session.contactfname#', '#session.contactaddr#', '#session.contactcity#', '#session.contactstate#', #session.contactzip#, 1234, #session.contacthomephone#, #session.contactworkphone#, #session.caryear#, '#session.vin#', '#session.carmake#', '#session.carmodel#', '#drvfname#', 'O', '#drvlname#', '#session.driveraddr#', #session.drivercity#', '#session.driverstate#', #session.driverzip#, 1234, #session.driverhomephone#, #session.driverworkphone#, '#session.dlnum#', '#session.dlstate#', #session.driverdob#, '#session.driverexcl#','#session.reportedby#', '#claimstatus#', '#claimteam#', '#claimteamdesc#', '#adjustercode#', '#adjusterdesc#', '#producer#', #producerphone#, #session.dlnum#, #recvdate#, '#sendto#', #sendtotimedate#, '#endorsement#', #claimnum#, '#affnoloss#')
</cfquery>

</body>

When I run this code, not only does it not update, I don’t see any of my text either. I'm guessing that my problem is with the formatting of all of my fields and Access won't accept it?

Also, is there a way to see a “log process” of the attempt to write to the database and why it was rejected. I mean, can I turn on a switch or something in the Cold Fusion Admin section?

Any advice you can give would be very much appreciated.

Thanks in advance,
Scripter73

CFHub · Aug 1, 2001

A quick peek shows me you do not have the correct syntax.

Try:

<cfquery datasource="Yours">
INSERT INTO
YourTable(
column1,
column2,
column3)
Values(
'#Session.Column1#',
'#Session.Column1#',
'#Session.Column1#',)
</cfquery>

scripter73 · Aug 1, 2001

Hi,

I've updated my code a little bit, however, I'm still getting an error message.

<cfset #session.entrytimedate# = "08/01/2001">
<cfset #session.policyholder# = "GEORGE JETSON">
<cfset #session.policynumber# = "CGNCNS7500000">
<cfset #session.reportedby# = "Lois Lane">
<cfset #session.lossdate# = "07312001">
<cfset #session.losshour# = 12>
<cfset #session.lossmin# = 00>
<cfset #prelosstime# = "#session.losshour#:#session.lossmin#">
<cfset #session.losstime# = #prelosstime#>

<cfset #session.timeofday# = "AM">
<cfset #session.insuredfname# = "George">
<cfset #session.insuredmi# = "M">
<cfset #session.insuredlname# = "Jetson">
<cfset #session.insuredaddr# = "3333 Green St.">
<cfset #session.insuredcity# = "Greenville">
<cfset #session.insuredstate# = "TX">
<cfset #session.insuredzip# = 76401>
<cfset #session.insuredphonearea# = 972>
<cfset #session.insuredphoneprefix# = 596>
<cfset #session.insuredphonesuffix# = 1234>
<cfset #insuredhomenum# = "#session.insuredphonearea##session.insuredphoneprefix##session.insuredphonesuffix#">
<cfset #session.insuredhomephone# = #insuredhomenum#>

<cfset #session.insuredworkphonearea# = 972>
<cfset #session.insuredworkphoneprefix# = 690>
<cfset #session.insuredworkphonesuffix# = 5500>
<cfset #session.insuredworkext# = 239>
<cfset #insuredworknum# = "#session.insuredworkphonearea##session.insuredworkphoneprefix##session.insuredworkphonesuffix##session.insuredworkext#">
<cfset #session.insuredworkphone# = #insuredworknum#>

<cfset #session.insuredworkext# = 239>
<cfset #session.insuredemail# = "lois.lane@dailyplanet.com">
<cfset #session.contactfname# = "George">
<cfset #session.contactmi# = "M">
<cfset #session.contactlname# = "Jetson">
<cfset #session.contactaddr# = "3333 Green St.">
<cfset #session.contactcity# = "Greenville">
<cfset #session.contactstate# = "TX">
<cfset #session.contactzip# = 76401>
<cfset #session.contactphonearea# = 972>
<cfset #session.contactphoneprefix# = 596>
<cfset #session.contactphonesuffix# = 1234>
<cfset #contacthomenum# = "#session.contactphonearea##session.contactphoneprefix##session.contactphonesuffix#">
<cfset #session.contacthomephone# = #contacthomenum#>

<cfset #session.contactworkphonearea# = 972>
<cfset #session.contactworkphoneprefix# = 690>
<cfset #session.contactworkphonesuffix# = 5500>
<cfset #session.contactworkext# = 239>
<cfset #contactworknum# = "#session.contactphonearea##session.contactphoneprefix##session.contactphonesuffix##session.contactworkext#">
<cfset #session.contactworkphone# = #contactworknum#>

<cfset #session.caryear# = 1996>
<cfset #session.carmake# = "NISSAN">
<cfset #session.carmodel# = "Sentra">
<cfset #session.vin# = "ABCDEFGH12345678">


<cfset #session.driver# = "ELROY JETSON">
<cfset #session.driveraddr# = "123 Main St.">
<cfset #session.drivercity# = "Dallas">
<cfset #session.driverstate# = "TX">
<cfset #session.driverzip# = 75224>
<cfset #session.driverhomearea# = 214>
<cfset #session.driverhomeprefix# = 456>
<cfset #session.driverhomesuffix# = 1234>
<cfset #driverhomenum# = "#session.driverhomearea##session.driverhomeprefix##session.driverhomesuffix#">
<cfset #session.driverhomephone# = #driverhomenum#>

<cfset #session.driverworkarea# = 972>
<cfset #session.driverworkprefix# = 555>
<cfset #session.driverworksuffix# = 7894>
<cfset #session.driverworkext# = 123>
<cfset #driverworknum# = "#session.driverworkarea##session.driverworkprefix##session.driverworksuffix##session.driverworkext#">
<cfset #session.driverworkphone# = #driverworknum#>

<cfset #session.dlnum# = "123123123">
<cfset #session.dlstate# = "TX">
<cfset #session.driverdob# = "01/01/1981">
<cfset #session.driverexcl# = "N">
<cfset #session.driverssn# = 456778974>

<cfset #session.acclocation# = "Beltline/Arapaho">
<cfset #session.acccity# = "Dallas">
<cfset #session.accstate# = "TX">
<cfset #session.policecontact# = "YES">
<cfset #session.policedept# = "Dallas Police Dept.">
<cfset #session.policerpt# = "1234567890">
<cfset #session.accdesc# = "Proceeding North on Beltline Rd., and the light was green, and as I crossed the light, I was hit from behind.">
<cfset #session.witness1fname# = "Buggs">
<cfset #session.witness1lname# = "Bunny">
<cfset #session.witness1area# = "214">
<cfset #session.witness1prefix# = "742">
<cfset #session.witness1suffix# = "7894">
<cfset #session.witness2fname# = "Elmer">
<cfset #session.witness2lname# = "Fudd">
<cfset #session.witness2area# = "610">
<cfset #session.witness2prefix# = "310">
<cfset #session.witness2suffix# = "4561">


<cfset #session.claimstatus# = "Pending">
<cfset #session.claimteam# = "12">
<cfset #session.claimteamdesc# = "claimteam">
<cfset #session.adjustercode# = "Adj12">
<cfset #session.adjusterdesc# = "Adjuster1">
<cfset #session.producer# = "mainagent">
<cfset #session.producerphone# = 1234567899>
<cfset #session.recvdate# = "08/01/2001">
<cfset #session.sendto# = "aclaimadjuster">
<cfset #session.sendtotimedate# ="08/01/2001">
<cfset #session.endorsement# = "00">
<cfset #session.claimnum# = 123456>
<cfset #session.affnoloss# = "Affadavit on File">
<cfset #session.incident# = "FENDER BENT">
<cfset #session.effdate# = "08/01/2001">
<cfset #session.expdate# = "08/02/2001">


<cfset #session.drvfname# = ListFirst(session.driver, " &quot

>
<cfset #session.drvlname# = ListLast(session.driver, " &quot

>

Hello...Starting insert into TblClaims<br>
<cfquery datasource="CCMSLRDTA" name="updateclaims" debug="YES">
INSERT INTO TBLCLAIMS(PolicyNumber, Mode, Lossdate, Losstime, ReportedBy, Status, EntryTimeDate, Effdate, Expdate, Incident, InsuredLastName, InsuredMiddleI, InsuredFirstName, InsuredAddress, InsuredCity, InsuredState, InsuredZip, InsuredZip2, InsuredHomePhone, InsuredWorkPhone, ContactLastName, ContactMiddleI, ContactFirstName, ContactAddress, ContactCity, ContactState, ContactZip, ContactZip2, ContactHomePhone, ContactWorkPhone, InsuredVehicleYear, InsuredVehicleVIN, InsuredVehicleMake, InsuredVehicleModel, DriverLastName, DriverMiddleI, DriverFirstName, DriverAddress, DriverCity, DriverState, DriverZip, DriverZip2, DriverHomePhone, DriverWorkPhone, DriverLicenceNumber, DriverLicenseState, DriverDateofBirth, DriverExcluded, ReportTakenBy, ClaimStatus, ClaimTeamCode, ClaimTeamDescription, Adjustercode, AdjusterDescription, Producer, ProducerPhone, DriverSSNumber, ReceivedDateTime, Sendto, SendtodateTime, Endorsement, ClaimNumber, AffidavitNoLoss) VALUES
('#session.policynumber#', 0, '#session.lossdate#', '#session.losstime#', '#session.reportedby#', 'UNK', '08/01/2001', '#session.effdate#', '#session.expdate#', '#session.incident#', '#session.insuredlname#', #session.insuredmi#, '#session.insuredfname#', '#session.insuredaddr#', '#session.insuredcity#', '#session.insuredstate#', #session.insuredzip#, 1234, #session.insuredhomephone#, #session.insuredworkphone#, '#session.contactlname#', '#session.contactmi#', '#session.contactfname#', '#session.contactaddr#', '#session.contactcity#', '#session.contactstate#', #session.contactzip#, 1234, #session.contacthomephone#, #session.contactworkphone#, #session.caryear#, '#session.vin#', '#session.carmake#', '#session.carmodel#', '#session.drvlname#', 'O', '#session.drvfname#', '#session.driveraddr#', '#session.drivercity#', '#session.driverstate#', #session.driverzip#, 1234, #session.driverhomephone#, #session.driverworkphone#, '#session.dlnum#', '#session.dlstate#', '#session.driverdob#', '#session.driverexcl#','WEBSITE', '#session.claimstatus#', '#session.claimteam#', '#session.claimteamdesc#', '#session.adjustercode#', '#session.adjusterdesc#', '#session.producer#', #session.producerphone#, #session.driverssn#, '#session.recvdate#', '#session.sendto#', '08/01/2001', '#session.endorsement#', #session.claimnum#, '#session.affnoloss#')
</cfquery>

I've checked and re-checked the names of the fields and also the count of attributes I'm passing, and I can't figure it out. The only thing is that this table has an AutoNumber field called "ReportNumber" that I'm not adding because I want Access to do it.

Here's the error I'm getting:

ODBC Error Code = 07001 (Wrong number of parameters)
[Microsoft][ODBC Microsoft Access 97 Driver]Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.

Any help you can provide is very much appreciated.

Thanks,
scripter73

calista · Aug 2, 2001

My first suspision is that somewhere you have single quotes where you don't need them, or don't have them where you do. That will give that kind of an error.

With such a large table, the way I would approach it would be to break the insert in half. Try inserting the first half and see what happens. Then try the second half. If one works and the other doesn't, you know which half the problem is in. Repeat the process until you have isolated the field or fields that are causing the problem. If neither half works, you'll have to keep breaking it down until you get something that works. If it were a small table, I'd just do them one at a time, but this method will get you through in the least amount of time. Yes, it's tedious, but it should work. When you isolate the field that is wrong, the problem should be obvious by then. With so much to look at, it's easy to miss something. Hope this helps you. Calista :-X
Jedi Knight,
Champion of the Force

scripter73 · Aug 2, 2001

You're a gem! That's exactly what I did and everything's working fine. You're right. I didn't have a single quote where one needed to be.

Thanks again,
scripter73

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Inserting Table Row

scripter73

Programmer

CFHub

Technical User

scripter73

Programmer

calista

Programmer

scripter73

Programmer

Similar threads

Part and Inventory Search

Sponsor