By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,649 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,649 IT Pros & Developers. It's quick & easy.

how to use ASP for working .DBF files ?

P: n/a
Also idea ?

how to use ASP for working .DBF files ?

Please help me ..

thanks

Vivian
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Viviane....

check out this link....

http://www.pstruh.cz/tips/detpg_asp-dbf-database.htm

It should give you all the info you need.

Best
Tom Gahagan

Jul 19 '05 #2

P: n/a
Querying and extracting data is relatively easy with the right connection
string.

Here's my function for returning an ADODB.Connection for a DBF (using ADO
and the Jet 4.0 OLEDB Provider):

Public Function GetDBFConnection(ByVal strHubFilename As String, Optional
ByVal strDBFUserID As String, Optional ByVal strDBFPwd As String, Optional
ByVal plngTimeoutSeconds As Long, Optional ByVal penumCursorLocation As
Integer = adUseClient) As ADODB.Connection
Dim connPrivate As ADODB.Connection
Dim strConn As String
Dim strSource As String
Dim plngCount As Long
Dim pstrErrMessage As String

'Create the connection object
Set connPrivate = New ADODB.Connection
Set GetDBFConnection = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strHubFilename & ";Persist Security Info=True;Extended Properties=dBase IV"
connPrivate.ConnectionString = strConn
connPrivate.CursorLocation = penumCursorLocation
If plngTimeoutSeconds > 0 Then connPrivate.ConnectionTimeout =
plngTimeoutSeconds
On Error GoTo Try_Again
Err.Clear
plngCount = 0
Try_Again:
plngCount = plngCount + 1
If plngCount > 5 Then GoTo Error_Occurred
connPrivate.Open
On Error GoTo 0
Set GetDBFConnection = connPrivate
Set connPrivate = Nothing
Exit Function
Error_Occurred:
pstrErrMessage = S_ERR_RSACCESS_CONNECTION & vbCrLf
pstrErrMessage = pstrErrMessage & "Connection String = '" & strConn &
"'" & vbCrLf
pstrErrMessage = pstrErrMessage & "Line " & Erl & " - " &
Err.Description & vbCrLf & "in RSFunctionsADODB.GetDBFConnection." & vbCrLf
Err.Clear
Err.Number = ERR_RSACCESS_CONNECTION
Err.Description = pstrErrMessage
Err.Raise Err.Number
End Function

Just to be clear, the folder that the DBF's reside in is considered the
'database' (or 'catalog') and each DBF file is treated as a table within
that database. So .. in the above .. strHubFilename is actually the folder
that the DBF is within and the SQL statement uses the name of the DBF (no
extension) as the table that it's querying.

Now, I usually get disconnected recordsets and update them by reconnecting
them and calling .UpdateBatch on the recordset. However, I've never
successfully managed to update a DBF (on it's own with no index file etc.)
so I'm not 100% certain how to achieve it. If I need to do that then I
usually convert the DBF to Access (using the pstrh component) and work on it
as a standard Access database. There's obviously an over head to this
though.

This is the function that I generally use to get a recordset for a DBF:

Public Function GetDBFRS(ByVal strQuery As String, ByVal strHubDBFFilename
As String, Optional strDBFUserID As String, Optional ByVal strDBFPwd As
String, Optional ByVal plngTimeoutSeconds As Long, Optional ByVal
penumCursorLocation As Integer = adUseClient) As ADODB.Recordset
Dim privateRS As ADODB.Recordset
Dim privateConn As ADODB.Connection
Dim strSource As String
Dim plngCount As Long
Dim pstrErrMessage As String

'Create the ADODB.Recordset
Set GetDBFRS = New ADODB.Recordset
Set privateRS = New ADODB.Recordset
Set privateConn = New ADODB.Connection
Set privateConn = GetDBFConnection(strHubDBFFilename, strDBFUserID,
strDBFPwd, plngTimeoutSeconds, penumCursorLocation)
If plngTimeoutSeconds > 0 Then privateConn.CommandTimeout =
plngTimeoutSeconds
Set privateRS.ActiveConnection = privateConn
On Error GoTo Try_Again
plngCount = 0
Try_Again:
Err.Clear
plngCount = plngCount + 1
If plngCount > 5 Then GoTo Error_Occurred
DoEvents
privateRS.Open strQuery, privateConn, adOpenStatic,
adLockBatchOptimistic
On Error GoTo 0
If privateConn.CursorLocation = adUseClient Then
Set privateRS.ActiveConnection = Nothing
End If
Set GetDBFRS = privateRS
Set privateRS = Nothing
Set privateConn = Nothing
Exit Function
Error_Occurred:
pstrErrMessage = S_ERR_RSACCESS_RECORDSET & vbCrLf
pstrErrMessage = pstrErrMessage & "Connection String = '" &
privateConn.ConnectionString & "'" & vbCrLf
pstrErrMessage = pstrErrMessage & "SQL Statement = '" & strQuery & "'" &
vbCrLf
pstrErrMessage = pstrErrMessage & "Line " & Erl & " - " &
Err.Description & vbCrLf & "in RSFunctionsADODB.GetDBFRS." & vbCrLf
Err.Clear
Err.Number = ERR_RSACCESS_RECORDSET
Err.Description = pstrErrMessage
Err.Raise Err.Number
End Function

Notice that it calls the first function to get the connection and then drops
the connection immediately afterward - this is the beauty of disconnected
recordsets - they do not consume database connection resources unless
actually doing something and they can also be persisted to disk or sent to a
remote application. There is enough infromation inside the recordset to
allow it to function 'disconnected' from the database.

It shouldn't be too hard to implement this in VBScript - just drop the
variable types and make sure that the constants are correct.

Hope this helps (a bit).

NB: If you want to see the entire DLL that contains this function (and those
for Access and SQL Server) then download it from:

http:\\ftp.belper.blue-canoe.net\RSAccess\RSAccess.zip

Cheers,

Chris.

"viviane lima - * - * -" <vi*********@ligbr.com.br> wrote in message
news:ed**************@TK2MSFTNGP12.phx.gbl...
Chris

Yes, i want queries, updates, delete records but in the tools
I can work this ?

thanks

Vivian

"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> escreveu na mensagem
news:uR**************@TK2MSFTNGP12.phx.gbl...
Query or update? (update is harder - I've never got it to work successfully without converting to an Access DB and then back again on the fly).
Check out

http://www.pstruh.cz/help/RSConv/database.asp

for the tool that does that (it's very good).

I have a VB class that will do reads and queries - it's easily ported to
VBScript if necessary (and indeed I may have already done it somewhere) if
you need it.

Chris.

"viviane lima - * - * -" <vi*********@ligbr.com.br> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Also idea ?

how to use ASP for working .DBF files ?

Please help me ..

thanks

Vivian


Jul 19 '05 #3

P: n/a
Man nice. Thank you Chris.

--
George Hester
__________________________________
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message news:u0**************@TK2MSFTNGP12.phx.gbl...
Querying and extracting data is relatively easy with the right connection
string.

Here's my function for returning an ADODB.Connection for a DBF (using ADO
and the Jet 4.0 OLEDB Provider):

Public Function GetDBFConnection(ByVal strHubFilename As String, Optional
ByVal strDBFUserID As String, Optional ByVal strDBFPwd As String, Optional
ByVal plngTimeoutSeconds As Long, Optional ByVal penumCursorLocation As
Integer = adUseClient) As ADODB.Connection
Dim connPrivate As ADODB.Connection
Dim strConn As String
Dim strSource As String
Dim plngCount As Long
Dim pstrErrMessage As String

'Create the connection object
Set connPrivate = New ADODB.Connection
Set GetDBFConnection = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strHubFilename & ";Persist Security Info=True;Extended Properties=dBase IV"
connPrivate.ConnectionString = strConn
connPrivate.CursorLocation = penumCursorLocation
If plngTimeoutSeconds > 0 Then connPrivate.ConnectionTimeout =
plngTimeoutSeconds
On Error GoTo Try_Again
Err.Clear
plngCount = 0
Try_Again:
plngCount = plngCount + 1
If plngCount > 5 Then GoTo Error_Occurred
connPrivate.Open
On Error GoTo 0
Set GetDBFConnection = connPrivate
Set connPrivate = Nothing
Exit Function
Error_Occurred:
pstrErrMessage = S_ERR_RSACCESS_CONNECTION & vbCrLf
pstrErrMessage = pstrErrMessage & "Connection String = '" & strConn &
"'" & vbCrLf
pstrErrMessage = pstrErrMessage & "Line " & Erl & " - " &
Err.Description & vbCrLf & "in RSFunctionsADODB.GetDBFConnection." & vbCrLf
Err.Clear
Err.Number = ERR_RSACCESS_CONNECTION
Err.Description = pstrErrMessage
Err.Raise Err.Number
End Function

Just to be clear, the folder that the DBF's reside in is considered the
'database' (or 'catalog') and each DBF file is treated as a table within
that database. So .. in the above .. strHubFilename is actually the folder
that the DBF is within and the SQL statement uses the name of the DBF (no
extension) as the table that it's querying.

Now, I usually get disconnected recordsets and update them by reconnecting
them and calling .UpdateBatch on the recordset. However, I've never
successfully managed to update a DBF (on it's own with no index file etc.)
so I'm not 100% certain how to achieve it. If I need to do that then I
usually convert the DBF to Access (using the pstrh component) and work on it
as a standard Access database. There's obviously an over head to this
though.

This is the function that I generally use to get a recordset for a DBF:

Public Function GetDBFRS(ByVal strQuery As String, ByVal strHubDBFFilename
As String, Optional strDBFUserID As String, Optional ByVal strDBFPwd As
String, Optional ByVal plngTimeoutSeconds As Long, Optional ByVal
penumCursorLocation As Integer = adUseClient) As ADODB.Recordset
Dim privateRS As ADODB.Recordset
Dim privateConn As ADODB.Connection
Dim strSource As String
Dim plngCount As Long
Dim pstrErrMessage As String

'Create the ADODB.Recordset
Set GetDBFRS = New ADODB.Recordset
Set privateRS = New ADODB.Recordset
Set privateConn = New ADODB.Connection
Set privateConn = GetDBFConnection(strHubDBFFilename, strDBFUserID,
strDBFPwd, plngTimeoutSeconds, penumCursorLocation)
If plngTimeoutSeconds > 0 Then privateConn.CommandTimeout =
plngTimeoutSeconds
Set privateRS.ActiveConnection = privateConn
On Error GoTo Try_Again
plngCount = 0
Try_Again:
Err.Clear
plngCount = plngCount + 1
If plngCount > 5 Then GoTo Error_Occurred
DoEvents
privateRS.Open strQuery, privateConn, adOpenStatic,
adLockBatchOptimistic
On Error GoTo 0
If privateConn.CursorLocation = adUseClient Then
Set privateRS.ActiveConnection = Nothing
End If
Set GetDBFRS = privateRS
Set privateRS = Nothing
Set privateConn = Nothing
Exit Function
Error_Occurred:
pstrErrMessage = S_ERR_RSACCESS_RECORDSET & vbCrLf
pstrErrMessage = pstrErrMessage & "Connection String = '" &
privateConn.ConnectionString & "'" & vbCrLf
pstrErrMessage = pstrErrMessage & "SQL Statement = '" & strQuery & "'" &
vbCrLf
pstrErrMessage = pstrErrMessage & "Line " & Erl & " - " &
Err.Description & vbCrLf & "in RSFunctionsADODB.GetDBFRS." & vbCrLf
Err.Clear
Err.Number = ERR_RSACCESS_RECORDSET
Err.Description = pstrErrMessage
Err.Raise Err.Number
End Function

Notice that it calls the first function to get the connection and then drops
the connection immediately afterward - this is the beauty of disconnected
recordsets - they do not consume database connection resources unless
actually doing something and they can also be persisted to disk or sent to a
remote application. There is enough infromation inside the recordset to
allow it to function 'disconnected' from the database.

It shouldn't be too hard to implement this in VBScript - just drop the
variable types and make sure that the constants are correct.

Hope this helps (a bit).

NB: If you want to see the entire DLL that contains this function (and those
for Access and SQL Server) then download it from:

http:\\ftp.belper.blue-canoe.net\RSAccess\RSAccess.zip

Cheers,

Chris.

"viviane lima - * - * -" <vi*********@ligbr.com.br> wrote in message
news:ed**************@TK2MSFTNGP12.phx.gbl...
Chris

Yes, i want queries, updates, delete records but in the tools
I can work this ?

thanks

Vivian

"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> escreveu na mensagem
news:uR**************@TK2MSFTNGP12.phx.gbl...
Query or update? (update is harder - I've never got it to work

successfully
without converting to an Access DB and then back again on the fly).
Check out

http://www.pstruh.cz/help/RSConv/database.asp

for the tool that does that (it's very good).

I have a VB class that will do reads and queries - it's easily ported to
VBScript if necessary (and indeed I may have already done it somewhere) if
you need it.

Chris.

"viviane lima - * - * -" <vi*********@ligbr.com.br> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Also idea ?

how to use ASP for working .DBF files ?

Please help me ..

thanks

Vivian



Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.