flat file matching partial field variable

[mfenn]

Hello,
Our live database is actually flat files (.dbf). We use SQL Server 2000 DTS packages to copy data from the flat files into SQL tables every night. All of our reports are based on these SQL tables instead of the live data.

We are changing one of our asp pages to use the live database (.dbf) files so we get realtime data.

I'm still pretty new to asp so not sure where to go with this problem. The problem I'm having is with a select statment that is getting data based on a partial field match. Not sure if i'm explaining it correctly so here is an example.

strSQL = "Select * from MEME where MEM_ID = '" & strMyMEMID & "' AND LEFT(MEME.PLAN_CODE,3) <> 'ORT' AND LEFT(MEME.PLAN_CODE,1) <> 'V' and MEME.EFFECTIVE_DATE <'" & strToday & "' and (MEME.TERMINATE_DATE > '" & strToday & "' or MEME.TERMINATE_DATE = ' ')"

This is selecting rows based on the first 3 characters of PLAN_CODE not matching "ORT" or the first character of PLAN_CODE not matching "V"). This works fine with SQL server but not with the dbf files. I don't know why.

It seems to think that the LEFT in LEFT(MEME.PLAN_CODE,3) is part of the actual field name and is giving me this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[DB/C Software][FS4 ODBC Driver]Parsing error: invalid column name: LEFT
/app/companywide/onlineenrollment/PendEmp.asp, line 719


I've spent several hours googling this but haven't come close to a solution. Any help would be appreciated.

mfenn

 

[foxbox]

.dbf = dBase? What is your connection string? LEFT
your SQL looks oke, but try:
- To response.write the strSQL after that assignment (so you see the exact SQL
- left(PLAN_CODE,3)
- Check if plan_code is alfanumeric

 

[mfenn]

Hi foxbox

The live databases in this case are flat (text) files with an extention of .dbf

Original connection:

' Create and open the database connection
Set dbConn  = Server.CreateObject("ADODB.Connection")
Set dbConn2 = Server.CreateObject("ADODB.Connection")

dbConn.Open  "DSN=Reports; UID=Reports;Pwd=Reports;Database=Web" 
dbConn2.Open "DSN=Reports; UID=Reports;Pwd=Reports;Database=TEST" 

' Create and open a recordset by applying the SELECT to the open database 
Set rst   = Server.CreateObject("ADODB.RecordSet") 
Set rst2  = Server.CreateObject("ADODB.RecordSet") 
Set rst3  = Server.CreateObject("ADODB.RecordSet")

and new connection

' Create and open the database connection
Set dbConn  = Server.CreateObject("ADODB.Connection")
Set dbConn2 = Server.CreateObject("ADODB.Connection")

dbConn.Open  "DSN=Reports; UID=Reports;Pwd=Reports;Database=Web" 
dbConn2.Open "fstest.dbd"

' Create and open a recordset by applying the SELECT to the open database 
Set rst   = Server.CreateObject("ADODB.RecordSet") 
Set rst2  = Server.CreateObject("ADODB.RecordSet") 
Set rst3  = Server.CreateObject("ADODB.RecordSet")

WEB is a SQL 2000 database that we need in either case. fstest.dbd and TEST are the same data, except fstest.dbd is current and TEST is from yesterday.

The Select statement works fine when we connect to TEST SQL database, but when we change the connection to fstest.dbd (live) it fails with the error in original post.

I did change strSQL to

...AND left(PLAN_CODE,3) <> 'ORT' AND left(PLAN_CODE,1)...

and refreshed the page. It's been working for about 10 minutes and hasn't displayed either the webpage or an error. there are 700k+ rows in MEME and it needs to return 1 row.

PLAN_CODE is CHAR(4); some are numbers only (2054) and some are alphanumeric (2lMA). We do have plan codes that start with "ORT" and we do have plan codes that start with "V".

Hope this helps

 

[mfenn]

anyone have any ideas please?

 

[gmmastros]

Have you thought about setting up a linked server between your SQL Server database and the flat files?

Setting up linked servers is relatively easy and it's a "once and done" thing. After you set up the linked server, you can query the tables in the flat files almost as though they were in the same database. I encourage you to do a little research on this. I think it will make a lot of things easier for you.


-George

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