[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2144
  • Last Modified:

Opening DBF files in a data grid using VB.Net

Hi,

I'm designing a Windows App that will allow a user to browse for a DBF file and display it in a datagrid.

I've done the browsing part fine, and code is shown below:

  Private Sub GetFileInfo()
        'Show the Open File Dialogue Box
        If ofdOpenDatabase.ShowDialog = DialogResult.OK Then
            'Show file name in title bar of Form
            sDBName = ofdOpenDatabase.FileName

            Me.Text = "Database Viewer (" & sDBName & ")"
        End If
    End Sub


And I then create the connection string for the DBF file and try to display it in a data grid (called DataGrid1) but I run into problems..

Private Sub openDBFDatabase()
        sConnDBF = "Driver={Microsoft dBase Driver (*.dbf)};DBQ=" & sDBName

        Dim sSQL As String = "select * from CREF0014"
        Dim da
        da = New Odbc.OdbcDataAdapter(sSQL, cnDBF)
        Dim ds As New DataSet

        da.Fill(ds)
        DataGrid1.DataSource = ds
        DataGrid1.DataMember = ds.Tables(0)
    End Sub


I get the following error after selecting the DBF file:

An unhandled exception of type 'System.InvalidOperationException' occurred in microsoft.visualbasic.dll

Additional information: The ConnectionString property has not been initialized.

this happens at line

da.Fill(ds)

Any help on this would be greatly appreciated, as I don't ahve much experience of using DBF files in VB.Net

thanks
0
Liteswitch
Asked:
Liteswitch
  • 7
  • 7
  • 2
1 Solution
 
flavoCommented:
Try this

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=Admin;Password="

from www.connectionstrings.com

Dave
0
 
LiteswitchAuthor Commented:
No, that didn't work, but thanks anyway.

In the string above, I noticed for the data source you put just the path to the folder, whereas my connection included the file name.  
As I am getting the path as a variable from user input, do I point the data source part of the connection string to the actual file, or jsut to the folder it's contained in?
0
 
flavoCommented:
never done it... I had a look at the ODBC one there too, and it didnt have a file name either... Its wired... Ill see what i can do

Dave
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
flavoCommented:
this is the same

http://www.visual-basic-data-mining.net/Forum/showpost.aspx?PostID=748

It looks like you set the db name in the sql string, like this

string SelectString = "SELECT LastName, FirstName, Address FROM 1.dbf";

wired..

Dave :-)
0
 
LiteswitchAuthor Commented:
Yes, I decided to use ODBC, but when the user selects the file for use, it stores the entire path including file name in the variable, and I can't find any way to separate the two out.

It seems you only need the path in the connection string, and specifty the file in the SQL statement, but as I said earlier, I have no idea how to separate the two..  I'll keep looking myself, and thanks for the help so far
0
 
flavoCommented:
ok, this is the bit i can do

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.OpenFileDialog1.ShowDialog()
        Dim sTemp As String = Me.OpenFileDialog1.FileName
        If Not sTemp = "" Then
            ' MsgBox(sFile)
            Dim sFile As String = Microsoft.VisualBasic.Right(sTemp, sTemp.Length - Microsoft.VisualBasic.InStrRev(sTemp, "\"))
            MsgBox(sFile)
            Dim sPath As String = Microsoft.VisualBasic.Left(sTemp, sTemp.Length - sFile.Length)
            MsgBox(sPath)
        End If
    End Sub

Dave
0
 
LiteswitchAuthor Commented:
Cheers for that, it really helped!

However, I now get a problem on the following line:

        DataGrid1.DataMember = sFile

or if I try it like  DataGrid1.DataMember = ds.Tables(0)

     To try and get it to open the table in the datagrid..

Any ideas at all?
0
 
flavoCommented:
ok..
Dim sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath & ";Extended Properties=dBASE IV;User ID=Admin;Password="
 Dim con As New OleDb.OleDbConnection(sCon)
        Dim cmd As New OleDb.OleDbCommand("select * from " & sFile)
        Dim adpt As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
        con.Open()
        adpt.SelectCommand = cmd
        Dim ds = New DataSet
        adpt.Fill(ds, "myTable")
        Me.myDataGrid.DataSource = ds.Tables("myTable")
        con.Close()

See how that goes..

we may need to trim off the last \ from the sPath

Dave
0
 
LiteswitchAuthor Commented:
No, that didn't work..

I've done an extra thing, creating a variable sTable, which it the filename without the.dbf extension,. and used that in the SQL statement and in the DataGrid.DataSource line as well, just to see if that was causing any problems, but still get an error on the

       Me.myDataGrid.DataSource = ds.Tables("myTable")
when I have it as

        DataGrid1.DataMember = ds.Tables(sTable)

0
 
wguerramCommented:
DataGrid1.DataMember receives a string

DataGrid1.DataSource = ds
DataGrid1.DataMember = sTable
0
 
LiteswitchAuthor Commented:
I tried that, and I get the following error:

An unhandled exception of type 'System.InvalidCastException' occurred in system.windows.forms.dll

Additional information: Cast from type 'String' to type 'OdbcCommand' is not valid.

I'll repaste my code as it is now..

   Private Sub GetFileInfo()
        'Show the Open File Dialogue Box
        If ofdOpenDatabase.ShowDialog = DialogResult.OK Then
            'Show file name in title bar of Form
            sDBName = ofdOpenDatabase.FileName

            sFile = Microsoft.VisualBasic.Right(sDBName, sDBName.Length - Microsoft.VisualBasic.InStrRev(sDBName, "\"))
            MsgBox(sFile)
            sPath = Microsoft.VisualBasic.Left(sDBName, sDBName.Length - (sFile.Length + 1))
            MsgBox(sPath)
            sTable = Microsoft.VisualBasic.Left(sFile, sFile.Length - 4)
            MessageBox.Show(sTable)

            Me.Text = "Database Viewer (" & sDBName & ")"
        End If
    End Sub



  Private Sub openDBFDatabase()
        sConnDBF = "Driver={Microsoft dBase Driver (*.dbf)};DBQ=" & sPath

        cnDBF = New Odbc.OdbcConnection(sConnDBF)
        cnDBF.ConnectionString = sConnDBF
        Dim sSQL = "select * from " & sTable
        Dim da
        da = New Odbc.OdbcDataAdapter(sSQL, cnDBF)
        da.SelectCommand = sSQL
        cnDBF.Open()

        MessageBox.Show(sSQL)

        Dim ds As DataSet = New DataSet
        da.Fill(ds, sTable)
        DataGrid1.DataSource = ds
        DataGrid1.DataMember = sTable

        cnDBF.Close()

    End Sub


I'm really getting stumped as to where I'm going wrong...
0
 
LiteswitchAuthor Commented:
ah hah!!!  

If I remove the line

 da.SelectCommand = sSQL


It works, but I'm puzzled as to why it works.....

0
 
wguerramCommented:
This how you use the select command.

da.SelectCommand.CommandText = sSQL
0
 
flavoCommented:
sorry, was sleeping (live in Australia)...  Got it now??
0
 
LiteswitchAuthor Commented:
Yes, it works fully now, thanks for your help!

Dan
0
 
flavoCommented:
Thanks for the points mate!

Glad it worked....  Good luck with your app!

Dave :-)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now