I'm transforming a classic application to make use of SQL Compact database (it really works comfortable with SQ Server 2005 and 2008) and taking advantage of the next code gives a mistake when trying to carry out a simple choose 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've recommended Microsoft ActiveX Data Access Object 6. Library within the project.
The mistake I recieve is:
Run-Time error -2147217887 (80040e21)
Multipe-Step operation produced errors. Check each status value
Just wondering if anybody has any suggestions?
Got this working now:
mDbConnection.CursorLocation = adUseClient
mDbConnection.CursorLocation = adUseServer worked!
I am not 100% sure the mistake comes from this code instead of something after it which is the "last known good" type position. Most typical reasons for the mistake:
You're unconditionally transforming data in to the wrong datatype (e.g. An alpha string value has been placed right into a number area. )
You're placing something having a wrong format (has a tendency to happen on dates most often)
A null value has been placed right into a area that doesn't allow nulls.
You've exceeded the max entire area having a value. (e.g. adhering a string 50 figures lengthy inside a 10 character area)
Also, there's nothing catching a mistake when the connection isn't open.
This is much like both of these:
It will likely be among three things:
- The bond string (what driver are you currently using?)
- Security (using Integrated Security rather than a username/password)
- or as Belliez stated, the Cursor Location