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
 
Ad

Advertisements

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