Hi,
This is not as easy as you might suppose. I do it frequently, but I first
have to create the structure of the .dbf table I want to generate and then I
place it with an _ in the name in a designated subdirectory. The reason I
have to do this is because the odbc driver for ado .net is limited in the
datatypes it will create, so I can't very well fashion the .dbf file
directly inside .net.
The function below does the rest - it turns a given dataset into a .dbf
file, as you are trying to do; I simply pass the dataset, the .dbf name and
the number of columns to it.
Public Function tabletodbf(ByVal mtable As DataSet, ByRef mdbf As String,
ByVal numcols As Integer) As Integer
' suppositions: the dbf file is in f:\imcapps\dbffiles; also, there is
' an empty of it with a _ at the end of the filename; also, we are working
' with dbf files exclusively in f:\imcapps\dbffiles; also, the table and
' the dbf have to have the exact same structure and in the same column #
' sequence; also, if the _ causes
' the file name to be too large, this probably won't work, so I have to
' ensure this doesn't happen
' signature:
' dim funcs as new imcfunctionlib.functions
' dim xint as integer
' xint = funcs.tabletodbf(dspslips, "netcsv.dbf", 5)
' xint = funcs.tabletodbf(dsletsumtt2, "letsumtt.dbf", 27)
tabletodbf = 0
Dim oconn_ As New SqlConnection("data source=d5z0071;database=imc;integrated
security=sspi;")
Dim oconn_d_ As New OdbcConnection("Driver={Microsoft dBase Driver
(*.dbf)};UID=admin;usercommitsync=yes;threads=3;statistics=0;safetransaction
s=0;pagetimeout=5;maxscanrows=8;maxbuffersize=2048;FIL=dBase
IV;DriverID=533;deleted=0;defaultdir=f:\imcapps\dbffiles;dbq=f:\imcapps\dbff
iles;collatingsequence=ascii;")
oconn_.Open()
oconn_d_.Open()
Dim path As String = "f:\imcapps\dbffiles\" & mdbf
Dim underscorename As String
underscorename = Mid(mdbf, 1, mdbf.Length - 4) & "_.dbf"
Dim fi As FileInfo = New FileInfo(path)
If fi.Exists = True Then
Kill("f:\imcapps\dbffiles\" & mdbf)
End If
FileCopy("f:\imcapps\dbffiles\" & underscorename, "f:\imcapps\dbffiles\" &
mdbf)
' always save an empty file with _ as a convention
Dim da_d As New OdbcDataAdapter("select * from f:\imcapps\dbffiles\" & mdbf,
oconn_d_)
Dim ds_d As New DataSet("_d")
da_d.Fill(ds_d, "_d")
Dim commandbuilder_ds_d As OdbcCommandBuilder = New OdbcCommandBuilder(da_d)
Dim i As Integer
Dim irow, mrow_d As DataRow
For Each irow In mtable.Tables(0).Rows
mrow_d = ds_d.Tables(0).NewRow()
For i = 0 To numcols - 1
mrow_d(i) = irow(i)
Next
ds_d.Tables("_d").Rows.Add(mrow_d)
Next
Try
da_d.Update(ds_d, "_d")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
oconn_.Close()
oconn_d_.Close()
tabletodbf = 1
End Function
HTH,
Bernie Yaeger
kokwey28 said:
Dear All,
I want to connect to an MsSQL database.
Then I want to get data from the table in MsSQL and export this data to
dbase .dbf format using the Vb .net coding
