1. Raising the question
In a recent project, I encountered a problem with ASP's operation of FoxPro library tables (*.DBF). In reality, there are indeed many application software that use DBF tables. How to use these data in a network environment has left many friends at a loss.
I also checked a lot of information and couldn't find a detailed explanation of the solution. After testing, I have initially solved this problem and will share it with everyone.
This article attempts to solve the following problems:
1. ASP joins the free table (*.dbf file) generated by FoxPro
2. Store multiple types of data and graphics files into the dbf table at the same time
(The sample program is available from Set conn = Server.CreateObject("ADODB.Connection")
connstr = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;"&_
"SourceDB=" & Server.MapPath(db) &";Exclusive=No"
conn.Open connstr
In the above code, data is the relative path where my DBF file is located (relative to the file where this code is located), and it is converted into an absolute path through Server.MapPath(db).
http://www.connectionstrings.com provides connection strings for many types of library table files. For DBF files, the connection strings given are:
"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:mydbpath;"
I did not connect successfully using this connection string and did not conduct further testing. Interested friends can try the above string.
.www.downcodes.com
2. Declare the table name in the SQL statement
You can declare the table name to be operated in the SQL statement in the form of [table name] or [table name.extension]. For example, the table file name is t1.dbf, and the SQL statement is:
select * from [t1] or select * from [t1.dbf]
3. Storage operations of various types of data
DBF supports not many data types, among which it is worth noting Date (date type), Memo (note type), and General (general type). Here we focus on Data type data, Memo and Gen types are used when storing graphics files, which will be explained later.
I generally use two methods to write database operations, one is to use the insert statement, and the other is to use the addnew method. For DBF tables, these two methods are slightly different.
When using the insert statement, please note that the writing format of Date type data is {^yyyy-mm-dd}, and the delimiter is different from Access's # and SQL Server's'. The specific SQL statement is:
insert into [t1.dbf] (name,birthday) values ('MyName',{^1970-1-1})
When using the addnew method, the code I originally used was:
rst.open "[t1]",conn,0,3
rst.addnew
rst(0).value = "MyName"
rst(1).value = {^1970-1-1}
rst.update
rst.close
There is no problem when operating SQL Server and Access, but there is a problem when operating DBF files. After experimenting, I finally found the correct method:
sql = "select * from t1"
rst.open sql,conn,0,3
rst.addnew
rst(0).value = "MyName"
rst(1).value = {^1970-1-1}
rst.update
rst.close
Please note that the difference between the above two pieces of code is mainly in the SQL statement. As mentioned before, in SQL statements, you can declare table files in the form of [table name] or [table name.dbf], but when using the addnew method, the table name cannot have an extension and square brackets cannot be added, otherwise it will Prompt "Not a simple table name, cannot be updated".
4. Storage of graphic files
In the DBF table, both memo and general fields can be used to store graphics, audio and video, text and other files (please refer to http://www.chinadesign.com.cn/NewsContents1.asp?id=2663 ). Here, we set the field type to memo(binary) (binary preparation type), use the rst(n).AppendChunk() method to write the resulting image binary data, and use the Response.BinaryWrite() method to restore the binary data to picture. There are many articles related to image warehousing, so I won’t go into details here.
As for using the General field to store pictures, I tried it, but it didn't work, so I didn't try again.
5. Deletion of data
You can use the delete statement to delete the data, but when you open the table file after deletion, you find that the data is only marked for deletion and is not actually deleted from the table. In Foxpro, use the pack command to permanently delete data. After checking some information, it was said that VB cannot implement the pack operation, and of course VBS cannot implement it. The general solution is to import the data in the table (without deletion mark of course) into a new table every once in a while, delete the original table, and then rename the new table to the original table name.
6. Data and pictures are stored in the database at the same time
This problem is not within the scope of this article. There are many articles on the Internet that provide solutions. I will mention them here by the way.
I implemented this function using the "Huajing" componentless upload program. Some friends may ask, "Huajing" upload is written as a file and there is no way to store it in the database. Not bad, but with a slight change, you can get the binary data of the image and then store it in the database. In my example, the changes in the environment program are commented, please refer to them.
4. Conclusion
This article mainly discusses ASP's operation of DBF free tables. If it is a DBC library, the corresponding connection string is given in inc/conn.asp in the example.
At this point, I believe that friends have a general understanding of the operation of DBF tables. Combined with my examples, I believe that you can develop more functions.
GOOD LUCK!