Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[tonys123]

Hi Guys

I have a data load programme which reads data in from a CSV file. It then selects a record from the database and updates that database record accordingly. The sql SELECT statement is generating an error when I introduce a WHERE clause; the WHERE clause is comapring 2 database fields against 2 fields read in from the CSV file. It complains that the variables I have defined for the CSV fields are invalid.

The actual error I get is as follows:

DB Open.
Reading CSV Data...
Reading Data...
About to update.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Oracle][ODBC][Ora]ORA-00904: "COUNTRYEMBARKID": invalid identifier
/country_load2.asp, line 90

The programme is as follows:
******************************************************
<!--#include virtual="/adovbs.inc"-->
<%
' Author:
' Date:

Set cn = Server.CreateObject("ADODB.Connection")
dsn = "dsn=Test; UID=TestLogin; PWD=TestPasswd" ' ORACLE

cn.Mode = 3
cn.Open dsn

Response.Write("<p style='font-weight: bold;'>DB Open.</p>")

Server.ScriptTimeout = 500

Dim abortOnError
Dim dict, fso, csvFile, csvRow, commaPos, CountryCode, CountryName, RegionID, CountryAuth, ILTAuth, CountryGuideLocation, LineManagerAuth, CountryManagerInvite, VisaRequired, TravelPlanRequired, SecurityBrief, TravelClassID, RegionalManagerAuth, TravelAdminRequired, CountryEmbarkID, Apartment


Set fso = Server.CreateObject("Scripting.FileSystemObject")
Set dict = Server.CreateObject("Scripting.Dictionary")

' Read CSV file into dictionary

Response.Write("<p style='font-weight: bold;'>Reading CSV Data...</p>")
Response.Flush

Set csvFile = fspenTextFile(Server.MapPath("country_dataload.csv"))

While Not csvFile.AtEndOfLine
Response.Write("<p style='font-weight: bold;'>Reading Data...</p>")
csvRow = csvFile.ReadLine
commaPos = InStr(csvRow, ",")
CountryCode = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
CountryName = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
RegionID = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
CountryAuth = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
ILTAuth = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
CountryGuideLocation = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
LineManagerAuth = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
CountryManagerInvite = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
VisaRequired = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
TravelPlanRequired = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
SecurityBrief = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
TravelClassID = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
RegionalManagerAuth = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
TravelAdminRequired = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
commaPos = InStr(csvRow, ",")
CountryEmbarkID = Left(csvRow, commaPos - 1)
csvRow = Mid(csvRow,commaPos + 1)
Apartment = Mid(csvRow, commaPos + 1)
MatchField = CountryCode + " " + CountryEmbarkID

Response.Write("<p style='font-weight: bold;'>About to update.</p>")

' Update COUNTRY table

Set rs = Server.CreateObject("ADODB.Recordset")


sql = "SELECT COUNTRY_CODE, COUNTRY_NAME, REGION_ID, COUNTRY_AUTH, ILT_AUTH, COUNTRY_GUIDE_LOCATION, LINE_MANAGER_AUTH, COUNTRY_MANAGER_INVITE, VISA_REQUIRED, TRAVEL_PLAN_REQUIRED, SECURITY_BRIEF, TRAVEL_CLASS_ID, REGIONAL_MANAGER_AUTH, TRAVEL_ADMIN_REQUIRED, COUNTRY_EMBARK_ID, APARTMENT from COUNTRY where COUNTRY_CODE = CountryCode AND COUNTRY_EMBARK_ID = CountryEmbarkID;"

rs.Open sql, cn, adOpenKeyset, adLockOptimistic

If rs.BOF or rs.EOF Then
response.write("Record not found")
Else
' rs("COUNTRY_CODE") = CountryCode
' rs("COUNTRY_NAME") = CountryName
' rs("REGION_ID") = RegionID
' rs("COUNTRY_AUTH") = CountryAuth
' rs("ILT_AUTH") = ILTAuth
' rs("COUNTRY_GUIDE_LOCATION") = CountryGuideLocation
' rs("MANAGER_AUTH") = ManagerAuth
' rs("COUNTRY_MANAGER_INVITE") = CountryManagerInvite
' rs("VISA_REQUIRED") = VisaRequired
' rs("TRAVEL_PLAN_REQUIRED") = TravelPlanRequired
' rs("SECURITY_BRIEF") = SecurityBrief
' rs("TRAVEL_CLASS_ID") = TravelClassID
' rs("REGIONAL_MANAGER_AUTH") = RegionalManagerAuth
' rs("TRAVEL_ADMIN_REQUIRED") = TravelAdminRequired
' rs("COUNTRY_EMBARK_ID") = CountryEmbarkID
' rs("APARTMENT") = Apartment


rs.Update
rs.Close
End If

Wend

csvFile.Close

Response.Write("<p style='font-weight: bold;'>Import Complete.</p>")



cn.Close()
%>
******************************************************

Any help anyone can give would be appreciated. Thanks.

 

[gmmastros]

I think your problem is here:


sql = "SELECT COUNTRY_CODE, COUNTRY_NAME, REGION_ID, COUNTRY_AUTH, ILT_AUTH, COUNTRY_GUIDE_LOCATION, LINE_MANAGER_AUTH, COUNTRY_MANAGER_INVITE, VISA_REQUIRED, TRAVEL_PLAN_REQUIRED, SECURITY_BRIEF, TRAVEL_CLASS_ID, REGIONAL_MANAGER_AUTH, TRAVEL_ADMIN_REQUIRED, COUNTRY_EMBARK_ID, APARTMENT from COUNTRY where [!]COUNTRY_CODE = CountryCode AND COUNTRY_EMBARK_ID = CountryEmbarkID;[/!]"

This is all part of the string. You need to build your string based on the data. Like this...

        sql = "SELECT COUNTRY_CODE, COUNTRY_NAME, REGION_ID, COUNTRY_AUTH, ILT_AUTH, COUNTRY_GUIDE_LOCATION, LINE_MANAGER_AUTH, COUNTRY_MANAGER_INVITE, VISA_REQUIRED, TRAVEL_PLAN_REQUIRED, SECURITY_BRIEF, TRAVEL_CLASS_ID, REGIONAL_MANAGER_AUTH, TRAVEL_ADMIN_REQUIRED, COUNTRY_EMBARK_ID, APARTMENT from COUNTRY where COUNTRY_CODE = '" & CountryCode & "' AND COUNTRY_EMBARK_ID = '" & CountryEmbarkID & "';"

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tonys123]

Hi George

Makes sense and worked a treat! Thanks.

Tony