Export data from MsSQL to DBF using Vb >Net

G

Guest

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
Pls show me the example.

Thanks.

Kok Wey
 
B

Bernie Yaeger

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top