Deleting dbf records from VB.NET

[Adriano]

Hello,

I have a DBF table, connection is okay, can select necessary records and
fill into dataset,
but can't delete and update them, can anyone please help me on how to do
this/

thanks in advance,
Adriano

 

[W.G. Ryan eMVP]

When you say you can't delete, what do you mean? For instance, have you
written the commands and the fields aren't deleting, do you need help
writing the commands, or are you looking for the GUI tools to work and they
aren't? If you can tell me a little more abou tyour situation, I can be of
more help (I hope ;-P)

 

[Adriano]

Hello Ryan,

thanks for your reply,
I'm not looking for GUI tools for deleting, I just want my vb.net DELETE
query to work. When I execute the following query:
cmd.CommandText = "DELETE FROM `310705` WHERE SAN = '25107'

there's no error message, it seems okay but actually rows containing SAN =
'25107' is not removed. SELECT and INSERT statements work fine.

I have ODBC connection and dataadapter.

Regards,
Adriano

 

[Ken Tucker [MVP]]

Hi,

Dont put the tablename in ' '

cmd.CommandText = "DELETE FROM 310705 WHERE SAN = '25107'"


Ken
--------------

 

[Adriano]

Hello Ken,

I tryied without ' ', many other combinations, no result((((

anyone please help me....

Adriano

 

[m.posseth]

try this


cmd.CommandText = "DELETE FROM [310705] WHERE SAN = '25107'"

if SAN is a numeric fiield the syntax is like this

cmd.CommandText = "DELETE FROM [310705] WHERE SAN = 25107"


regards

Michel Posseth [MCP]

 

[Adriano]

No result((((((

Any other ideas???

thanks in advance
Adriano

try this


cmd.CommandText = "DELETE FROM [310705] WHERE SAN = '25107'"

if SAN is a numeric fiield the syntax is like this

cmd.CommandText = "DELETE FROM [310705] WHERE SAN = 25107"


regards

Michel Posseth [MCP]

Hello Ken,

I tryied without ' ', many other combinations, no result((((

anyone please help me....

Adriano

 

[Cindy Winegarden]

Hi Adriano,

When you say the rows are not removed, what do you mean? In FoxPro DBFs when
a record is deleted it is only flagged as deleted and not physically removed
from the table until the table is Packed. Packing requires exclusive use of
the table and if done at all is usually performed as part of off-hours
housekeeping routines.

For what it's worth, names of Fox tables do not usually begin with numeric
values, but rather with alpha characters or underscores. However, in my test
in VFP9 I was able to create a table with a name like yours and delete
records successfully both with Delete From 123 and Delete From "123".

 

[Adriano]

Hello Cindy,

thanks for your reply,
Yes, I mean they're not physically deleted after executing query, I'd
appreciate your help if you send me some code on how to do Packing.
I've attached my DBF file in case you'll need it,

Best regards,
Adriano

 

[Cindy Winegarden]

Hi Adriano,

Why do you think you need to pack the tables? Most Fox developers run with
Set Deleted On and only pack the tables when doing some sort of
weekly/monthly maintenance. Also, packing tables requires exclusive access
and so it can't be done every time a record is deleted.


'-- VB.NET code to pack a table:
'-- Download and install the latest VFP OLE DB data provider from
'-- http://msdn.microsoft.com/vfoxpro/downloads/updates/default.­aspx

Imports System
Imports System.Data.OleDb
Module Module1
Sub Main()
Try
Dim cn As OleDbConnection = New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")

Dim cmd As OleDbCommand = New OleDbCommand( _
"Set Exclusive On; Pack Test.dbf", cn)

cn.Open()
cmd.ExecuteNonQuery()
cn.Close()

Catch e As Exception
MsgBox("Exception")
End Try

End Sub
End Module

 

[m.posseth]

Aha ,, he was using the Fox


Cindy ,, did you made a mistake in your footer ?? or is this a title i do
not know yet "MSCD" should have been MCSD i guess ( Microsoft Certified
Solution`s Developer )

regards

Michel Posseth [MCP]

 

[Cindy Winegarden]

Thanks Michel. Some days I just can't type.

 

[Homer J Simpson]

No result((((((

Any other ideas???

Do you need a COMMIT statement?