How to make connection VFP database with VB.NET

Question

I'm creating a system that use foxpro as a database. I keep getting this error error [42S02][microsoft][ODBC visual foxpro driver] not a table when I want to connect VFP database with Visual Studio. When I add data connection in the visual studio, it shows connection success, but when I try to open the table, it shows the error.

This is a VB.Net system that use database foxpro 9. I have use mysql as the database and it work, but when I try to use foxpro database I get an error.

Imports System.Data.Odbc
Imports System.Data.OleDb

Public Class login

    Private Sub btnEnter_Click(sender As Object, e As EventArgs) Handles btnEnter.Click
        Dim oConn = CreateObject("adodb.connection")
        oConn.ConnectionString = "Provider=vfpoledb;DSN=visual_foxpro"
        oConn.Open()
        Dim conn = New OleDbConnection()

        Dim cmdString As String = "SELECT * FROM `login` WHERE `staffID`= @staffid AND `staffName`= @staffname"
        Dim cmd As New OleDbCommand(cmdString, oConn)
        cmd.Parameters.Add(New OleDbParameter("staffID", CType(txtStaffID.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("staffName", CType(txtStaffID.Text, String)))

        Dim adapter As New OleDbDataAdapter(cmd)
        Dim table As New DataTable()

        adapter.Fill(table)

        If table.Rows.Count = 0 Then
            MessageBox.Show("Staff ID or Staff Name not available")

        Else
            MessageBox.Show("Welcome " & txtStaffName.Text)

            Dim form As New formLeave

            form.PassStaffid = txtStaffID.Text
            form.PassStaffName = txtStaffName.Text
            form.Show()
            Me.Hide()

        End If

    End Sub

End Class

I expected the system can login using the database.

Why are you using CreateObject and then creating an OleDbConnection that you don't use? Get rid of oConn and use the OleDbConnection that you create. — jmcilhinney

Cetin Basoz Cetin Basoz · Accepted Answer · 2019-07-09T17:24:30

VFP database versions later than 6.x do not have an official ODBC driver from Microsoft. If you HAVE TO use ODBC, then you can find alternative drivers from sources like Sybase ADS. I use OLEDB instead successfully well.

While your code might work with MySQL, that is not the way you should write it. Also, it is MySQL specific, it wouldn't work in say MS SQL Server or postgreSQL either. You should read the documentation on the backend you are using. In VFP (or MS SQL Server, postgreSQL ...), you don't use back tics as table and field name identifiers. In VFP, if need be, to use name identifiers you could use single, double quotes or square brackets but you would need to enclose with parentheses (and use only for table name in an SQL query). Anyway, the easy way is to simply not to use identifiers at all.

Also, with an ODBC or OLEDB query, you need to use ? as parameter placeholders. Using @staffID wouldn't normally work in MySQL, ... either, but driver makers decided to support them for those backends.

From your messageBox messages, looks like you expect to get a single row for that query (I don't know why you use both staffId and staffName if staffId is primary key). Anyway here is your query in VB.Net:

Imports System.Data.OleDb

Public Class login

    Private Sub btnEnter_Click(sender As Object, e As EventArgs) Handles btnEnter.Click
        Dim strConn As String = "Provider=VFPOLEDB;Data source=c:\MyDataFolder\"
        Dim strQuery As String = <sql>SELECT * 
                                      FROM login 
                                      WHERE staffID=? AND staffName=?
                                 </sql>

        Using cn As New OleDbConnection(strConn)
            Using cmd As New OleDbCommand(strQuery, cn)
                cmd.Parameters.Add("@staffid", OleDbType.VarChar).Value = txtStaffID.Text;
            cmd.Parameters.Add("@staffname", OleDbType.VarChar).Value = txtStaffName.Text;
            cn.Open()
                Dim rdr As OleDbDataReader = cmd.ExecuteReader()
                If rdr.Read()
                    MessageBox.Show("Welcome " & txtStaffName.Text)

                    Dim form As New formLeave

                    form.PassStaffid = txtStaffID.Text
                    form.PassStaffName = txtStaffName.Text
                    form.Show()
                    Me.Hide()
                Else
                    MessageBox.Show("Staff ID or Staff Name not available")
                End If
                cn.Close()
            End Using
        End Using
    End Sub

End Class

How to make connection VFP database with VB.NET

1 Answers