Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Delete records from a delimeted file being used as datasource for a linked server using Jet 4.0 OLE DB in ASP.net application

Posted on 2008-11-12
Medium Priority
Last Modified: 2013-11-07
I have a .txt file that I am using as a datasource for a linked server. This is a log file that has 3 fields (date, path and message) that I am viewing in a gridview in asp.net.

Since this log file will grow, I would like to give users the ability to delete records in the file that have a date less then a date they choose in a parameter field.
Here is a sample of my SELECT statement:
Select convert(varchar(10),edate,101) as [Date], ePATH, eMessage from ARLOCKBOX...boaLog#txt
      WHERE convert(varchar(10),edate,101) = '11/10/2008'

I don't think Jet 4.0 supports deleting records through a query.

What is the best way to approach this?

Thanks in advance!
Question by:billkrieger
  • 2
LVL 51

Expert Comment

by:Mark Wills
ID: 22966603
why not import the log file into a table, index according to the most commonly used access paths (but the looks it would include date), and then you can archive, delete, and make use of an indexed query.

Author Comment

ID: 22993825
Thanks for the response. The file will be appended to every night & it will grow very fast. so I was looking for a way to delete old records from the file. So I would not have to go through all the records in my queries. I gave them an option to export the web page results to an Excel file and delete the log file. The application will recreate it.

I did not find any way to be able to delete records in the file? Any suggestions?

Thanks again for taking the time.
LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 22993971
Don't think you will get much joy in deleting from the text file short of reading it in and re-writing it.  There are a couple of DOS type commands to "find" certain values and can be either output, or held back from output - but then you will need to loop around the whole spectrum of dates - not at all a good thing.

What about if...

A stored procedure to import the file - two parameters, one is filename, and the other date. It loads the file into a table, and then deletes anything older than date

Can then export it again if needed.  

If all else fails, could try the following :

DELETE OPENQUERY (ARLOCKBOX, 'SELECT edate FROM boaLog#txt WHERE convert(datetime,edate,101) < ''20060601''');

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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