473,395 Members | 2,713 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Querying More Than 255 Characters from FoxPro/DBase Database Field

I've been tasked with integrating an older management system based on
DBF files with my snappy new ASP application to provide users of the
ASP application with real-time data from the management system.

I figure with DBF files, I should use either the DBase drivers or the
FoxPro drivers to connect to the database.

The integration has gone quite smoothly until yesterday. This means
that my connection strings work well based on the DBase drivers, and I
have been able to query data successfully.

What changed yesterday is that a field from the management system has
grown beyond 255 characters. It seems there is a limitation somewhere
with the connection object. The management system handles the large
field just fine. But when I try to query the very same field in my
ASP application via my DBase-based connection object, it returns a
truncated result at the 254th character (too close to 255 characters
for a max varchar field size that I am accustomed to in SQL server).

The data in the DBF is indeed not truncated. Only the result from my
SQL statement is. Switching to the FoxPro driver does not change
this.

Can anyone help me modify the following code so that it converts the
field type to, say, MEMO, so that the queried results are not
truncated? Or perhaps I'm barking up the wrong tree and someone can
tell me where else I've gone wrong. This is just the way I'm working
with the cards I've been dealt.

Thanks,
Matt

---------------------------

Set dbfConn = Server.CreateObject("ADODB.Connection")
'The DBase Connection String
dbfConn.Open "Driver={Microsoft dBASE Driver "_
& "(*.dbf)};DriverID=277;Dbq=C:\ManagementSys\Data\; "

'The Visual FoxPro Connection String
'dbfConn.Open "Driver=Microsoft Visual FoxPro " _
'& "Driver;SourceType=DBF;SourceDB=C:\ManagementSys\D ata\;"_
'& "BackgroundFetch=no;"

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

sql = "SELECT fieldX FROM tableY WHERE recordID = 1234"
dbfRS.Open sql, dbfConn
Jul 19 '05 #1
4 6119
(a) Don't use an explicit recordset object that way,
(b) See http://www.aspfaq.com/2188

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Matt Young" <st********@yahoo.com> wrote in message
news:38**************************@posting.google.c om...
I've been tasked with integrating an older management system based on
DBF files with my snappy new ASP application to provide users of the
ASP application with real-time data from the management system.

I figure with DBF files, I should use either the DBase drivers or the
FoxPro drivers to connect to the database.

The integration has gone quite smoothly until yesterday. This means
that my connection strings work well based on the DBase drivers, and I
have been able to query data successfully.

What changed yesterday is that a field from the management system has
grown beyond 255 characters. It seems there is a limitation somewhere
with the connection object. The management system handles the large
field just fine. But when I try to query the very same field in my
ASP application via my DBase-based connection object, it returns a
truncated result at the 254th character (too close to 255 characters
for a max varchar field size that I am accustomed to in SQL server).

The data in the DBF is indeed not truncated. Only the result from my
SQL statement is. Switching to the FoxPro driver does not change
this.

Can anyone help me modify the following code so that it converts the
field type to, say, MEMO, so that the queried results are not
truncated? Or perhaps I'm barking up the wrong tree and someone can
tell me where else I've gone wrong. This is just the way I'm working
with the cards I've been dealt.

Thanks,
Matt

---------------------------

Set dbfConn = Server.CreateObject("ADODB.Connection")
'The DBase Connection String
dbfConn.Open "Driver={Microsoft dBASE Driver "_
& "(*.dbf)};DriverID=277;Dbq=C:\ManagementSys\Data\; "

'The Visual FoxPro Connection String
'dbfConn.Open "Driver=Microsoft Visual FoxPro " _
'& "Driver;SourceType=DBF;SourceDB=C:\ManagementSys\D ata\;"_
'& "BackgroundFetch=no;"

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

sql = "SELECT fieldX FROM tableY WHERE recordID = 1234"
dbfRS.Open sql, dbfConn

Jul 19 '05 #2
Aaron,

Thanks for the pointer. I've tried what you suggested without any
luck. The following code returns the exact same result:

---------------------------
Set dbfConn = Server.CreateObject("ADODB.Connection")
'The DBase Connection String
dbfConn.Open "Driver={Microsoft dBASE Driver "_
& "(*.dbf)};DriverID=277;Dbq=C:\ManagementSys\Data\; "

'The Visual FoxPro Connection String
'dbfConn.Open "Driver=Microsoft Visual FoxPro " _
'& "Driver;SourceType=DBF;SourceDB=C:\ManagementSys\D ata\;"_
'& "BackgroundFetch=no;"

sql = "SELECT fieldX FROM tableY WHERE recordID = 1234"
dbfConn.Execute(sql)
---------------------------

Both the dBase and FoxPro drivers provide the same result.

BUT, there is new information I was not aware of. I was provided with
a text file which describes the different fields I'm working with
here. The particular field I am trying to query is of type ARRAY.

So, if I open up the DBF in notepad, the field appears as one long
string. But if you break it down, each element in the string
represents three pieces of information: a date and two
single-character codes for a total of ten characters. For example:

mmddyyyyax

Where a and x are the single character codes respectively. As more
elements are added to the field, in notepad it begins to look like
this:

mmddyyyyaxmmddyyyyaxmmddyyyyaxmmddyyyyaxmmddyyyyax mmddyyyyax

This has not been a problem until the field got beyond 255 characters.
I'm not sure if it's this ARRAY typing that is causing the issue.

I'd love more suggestions.

Matt
Jul 19 '05 #3
> sql = "SELECT fieldX FROM tableY WHERE recordID = 1234"
dbfConn.Execute(sql)
What do you expect to happen here? You haven't assigned the results to
anything, you told the database to execute a SELECT statement, and it
probably did that no problem, but didn't send results anywhere because you
didn't tell it to...
mmddyyyyax

Where a and x are the single character codes respectively. As more
elements are added to the field, in notepad it begins to look like
this:

mmddyyyyaxmmddyyyyaxmmddyyyyaxmmddyyyyaxmmddyyyyax mmddyyyyax


Sorry, I know nothing about DBF's file format. This seems to be a FoxPro
issue, not an ASP one, so my only suggestion is to post your issue to a
FoxPro group.
Jul 19 '05 #4
st********@yahoo.com (Matt Young) wrote in news:38**************************@posting.google.c om:
BUT, there is new information I was not aware of. I was provided with
a text file which describes the different fields I'm working with
here. The particular field I am trying to query is of type ARRAY.


VFP doesn't support an array field type.

Also, have you tried downloading the VFP OLE DB provider from
<http://www.microsoft.com/downloads/details.aspx?familyid=0f43eb58-7a94-4ae1-a59e-965869cb3bc9&displaylang=en>?

Using the ODBC driver requires that you do something with GetChunk() which,
fortunately, I've never needed to do. :-) The OLE DB provider _might_
avoid this.

Garrett Fitzgerald
MailMovers
Seattle, WA
Jul 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: bwalke | last post by:
Hi all, I am fairly new to using triggers and was seeking some help from those that have experience with them. I am looking to transfer data from a SQL 2000 database to a Visual FoxPro database...
13
by: Simon Bailey | last post by:
I am a newcomer to databases and am not sure which DBMS to use. I have a very simplified knowledge of databases overall. I would very much appreciate a (simplifed) message explaining the advantages...
3
by: Sarah | last post by:
Hi I am using vb.net Is it possible to fetch data from foxpro tables (vfp8) and update tables in sql server 2000? The table structures of the foxpro tables are different from those in sql...
18
by: Ty Salistean | last post by:
I think it would be nice if we could have a SQL engine available on the client side. I primarily do Windows Forms programming and it seems that I have to make alot of calls accross the wire to...
4
by: Robert Hooker | last post by:
I have installed our .NET application on a system running Microsoft Windows Vista operating system. When I run our application, I receive the following error message whenever it tries to create...
3
by: The One | last post by:
Hi, I'm working on a project which needs 10 GB of foxpro database needed to be migrated to DB2. Please suggest me some database migration strategy or is there any tool which can import data from...
2
by: Songyang | last post by:
I'm new to this group. In my company in Japan, we have been using dbase for almost 20 years, and we are now switching to foxpro. Dose anybody here has experience in converting dbase programs from...
1
by: ray well | last post by:
i need to access a legacy database that uses dBase files running under foxpro for read only purposes, within net 2.0, ado 2.0, and sql statements. i have no problem getting the info i need, but it...
10
by: Johny | last post by:
Is there a module for reading/modifing db files from Python? Thanks for help B.
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.