Connect FoxPro Database with VB 6.0

[07sprint]

Access foxpro database with VB 6.0. I get error message when I get to this code: Set rs = cmd.Execute

The error message is:

The connection cannot be used to perform this operation. It is Either closed or invalid in this context.

This is the code to connect:

Dim cn As New ADODB.Connection
cn.Open "Provider=vfpoledb.1;" & _
"Data Source =D:\PerCap1\percap2.dbf;"
cn.Close
Set cn = CreateObject("ADODB.Connection&quot
cn.ConnectionString = "Provider=vfpoledb.1;Data Source =D:\PerCap1\percap2.dbf"
cn.Open

 

[zemp]

What is the scope of cn relative to the command object and the trecordset? Post your code to create the command object and the recordset.

Thanks and Good Luck!

zemp

 

[07sprint]

'Create a SQL String

strSQL = "Select * from percap2 where Name Like "
strSQL = strSQL & "'" & Trim(cboNAME.Text & "%&quot & "'"

'Declare and set the Command

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandText = strSQL

'Declare and Set the RecordSet

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

**************************************
Set rs = cmd.Execute

This is were I get the ERROR
MESSAGE
***************************************

 

[zemp]

You need to set the command .activeconnection property.

Set cmd = New ADODB.Command
    cmd.CommandText = strSQL
    cmd.ActiveConnection = cn

Make sure that the connection has the correct scope.

Thanks and Good Luck!

zemp

 

[07sprint]

Set cn = CreateObject("ADODB.Connection&quot
cn.ConnectionString = "Provider=vfpoledb.1;Data Source =D:\PerCap1\percap2.dbf"
cn.Open

'Create a SQL String

strSQL = "Select * from percap2 where Name Like "
strSQL = strSQL & "'" & Trim(cboNAME.Text & "%&quot & "'"

'Declare and set the Command

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandText = strSQL
MsgBox cn
cmd.ActiveConnection = cn

'Declare and Set the RecordSet

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs = cmd.Execute
****************************************

MsgBox rs

ERROR MESSAGE: TYPE MISMATCH
****************************************
'Checking to see if the record Exists

If (rs.RecordCount < 1) Then
MsgBox &quot; No Record Found&quot;, _
vbInformation, &quot;Error&quot;
Exit Sub
End If

 

[zemp]

What is the purpose of the line 'Msgbox rs'? Is it necessary?



Thanks and Good Luck!

zemp

 

[07sprint]

No it not, but use message box to check the information. It like it connect to the database, but it not show the information in the database.

 

[zemp]

I would remove it then. The msgbox function probably does not recognize the recordset as a valid prompt.

Also the .recordcount property does not always function correctly. It depends on your cursor type. To check for records you can use this.

If Not rs.bof and not rs.eof Then
      ... 'records found
  else
     MsgBox " No Record Found", vbInformation, "Error"
     Exit Sub
  End If

Thanks and Good Luck!

zemp

 

[07sprint]

Thank ZEMP for your help. That code help me connect to the database.