1

I am converting an old application to use SQL Compact database (it works ok with SQ Server 2005 and 2008) and using the following code gives an error when attempting to execute a simple select command:

Private Const mSqlProvider          As String = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;"
Private Const mSqlHost              As String = "Data Source=C:\database.sdf;"
Private mCmd                        As ADODB.Command   ' For executing SQL'
Private mDbConnection               As ADODB.Connection


Private Sub Command1_Click()
   
    Dim DbConnectionString As String

    DbConnectionString = mSqlProvider & _
                            mSqlHost

        
    Set mDbConnection = New ADODB.Connection
    mDbConnection.CursorLocation = adUseClient
    
    Call mDbConnection.Open(DbConnectionString)
    
    If mDbConnection.State = adStateOpen Then
        Debug.Print (" Database is open")
        ' Initialise the command object'
        Set mCmd = New ADODB.Command
        mCmd.ActiveConnection = mDbConnection
        
    End If
    

    mCmd.CommandText = "select * from myTable"
    mCmd.CommandType = adCmdText
    mCmd.Execute  ' FAILS HERE! '

    
End Sub

I have referenced Microsoft ActiveX Data Access Object 6.0 Library in the project.

The error I get is:

Run-Time error -2147217887 (80040e21)

Multipe-Step operation generated errors. Check each status value

Just wondering if anyone has any suggestions?

Thanks

Community
  • 1
  • 1
Belliez
  • 5,356
  • 12
  • 54
  • 62

3 Answers3

0

Got this working now:

Changing 

mDbConnection.CursorLocation = adUseClient

to 

mDbConnection.CursorLocation = adUseServer worked!
Belliez
  • 5,356
  • 12
  • 54
  • 62
0

I'm not 100% sure the error is from this code rather than something after it and this is the "last known good" type position. Most common causes of the error:

  1. You are implicitly converting data into the wrong datatype (e.g. An alpha string value is being inserted into a numeric field. )

  2. You are inserting a value with a wrong format (tends to happen on dates most frequently)

  3. A null value is being inserted into a field that does not allow nulls.

  4. You have exceeded the max length of the field with a value. (e.g. sticking a string 50 characters long in a 10 character field)

Also, there is nothing catching an error if the connection is not open.

jasonk
  • 1,580
  • 4
  • 25
  • 33
0

This question is very similar to these two:

It will be one of three things:

  • The connection string (what driver are you using?)
  • Security (using Integrated Security instead of a username/password)
  • or as Belliez said, the Cursor Location
Community
  • 1
  • 1
Christian Payne
  • 7,081
  • 5
  • 38
  • 59