Multiple-step OLE DB operation generated errors.

[RonRepp]

Hi all,

I'm having a problem filling a treeview control with data from a SQL Server DB. Here is the code.
=================
Public Sub OpenMaintenance()
Dim MaintCon As New ADODB.Connection
Dim MaintRS As New ADODB.Recordset

Set MaintCon = New ADODB.Connection
MaintCon.ConnectionString = "Provider=sqloledb;" & _
"Data Source=D63WV941;Initial Catalog=TSMaster;User Id=sa;Password=sa; "


SQL = "SELECT TOP 100 PERCENT TRANSMITTAL_NR From dbo.SV_MACORD_FT_SHIP " & _
"GROUP BY TRANSMITTAL_NR " & _
"ORDER BY TRANSMITTAL_NR"

On Error GoTo MaintErr

MaintCon.ConnectionTimeout = 0

MaintCon.Open MaintCon.ConnectionString

Set MaintRS = New ADODB.Recordset

MaintRS.Open SQL, MaintCon, adOpenDynamic, adLockOptimistic, adCmdText

Do While Not MaintRS.EOF

tv1.Nodes.Add MaintRS(0).Value

MaintRS.MoveNext

Loop

MaintRS.Close
MaintCon.Close
Set MaintRS = Nothing
Set MaintCon = Nothing
Exit Sub

MaintErr:

Debug.Print Err.Number & vbCrLf & Err.Description
End Sub
================

The error I'm receiving is this:

-2147217887
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Does anyone have any ideas?

 

[zemp]

Try removing the 'Group By' clause from your SQL. I don't think that you need it.

zemp

 

[eudf]

Hello!
1. You are using "NEW" twice:
Dim ... as New ...
and
Set ...= New ...
for Connection and Recordset statements.
2. If you do not suppose to modify recordset values, instesd of "...,adOpenDynamic, adLockOptimistic" use "...adOpenForwardOnly, adLockReadOnly".
eudf

 

[ArtieChoke]

Print out the database error from the connection object. It should give you more info. Note: there can be multiple error messages. The err routine only catches the last (first?) error of the group.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[vbObjectError]

Change the rs cursor type to a Static cursor

 

[simickey]

how to change the rs cursor type to a static cursor? I also have this problem, and the following is my code

Dim adoConn As New ADODB.Connection
Dim adoRsCom As New ADODB.Recordset
Dim adoRsBak As New ADODB.Recordset

Private Sub Command1_Click()
Set adoConn = New ADODB.Connection
Set adoRsCom = New ADODB.Recordset
Set adoRsBak = New ADODB.Recordset

adoConn.Open "Provider=SQLOLEDB.1;User ID=xiaopeng;pwd=xiaopeng;Initial Catalog=yuxun;Data Source=127.0.0.1"

adoRsCom.Open "SELECT * FROM Sm_Send", adoConn, adOpenDynamic, adLockOptimistic
adoRsBak.Open "SELECT * FROM Sm_Send_Backup", adoConn, adOpenDynamic, adLockOptimistic
If adoRsCom.EOF = True Then
MsgBox "no data in the database"
End
End If

SmTcpConnect1.Host = ehost.
SmTcpConnect1.Port = eport.Text
SmTcpConnect1.LogName = elogname.Text
SmTcpConnect1.LogPwd = elogpwd.Text
SmTcpConnect1.BufSavePath = App.Path
SmTcpConnect1.ThreadName = "Receive"
SmTcpConnect1.ConType = 1
SmTcpConnect1.StartTcp


SmTcpConnect2.Host = ehost.Text
SmTcpConnect2.Port = eport.Text
SmTcpConnect2.LogName = elogname.Text
SmTcpConnect2.LogPwd = elogpwd.Text
SmTcpConnect2.BufSavePath = App.Path
SmTcpConnect2.ThreadName = "Send"
SmTcpConnect2.ConType = 2
SmTcpConnect2.StartTcp
LogPrintOut1.AddString "[..........Start..........]"
Timer1.Enabled = True
End Sub

I learned from Microsoft, and deleted the "Persist Security Info" keyword, and set the registry, and seems no useful. I had tried to remove the "New" from "Dim adoConn As New ADODB.Connection", "Dim adoRsCom As New ADODB.Recordset", and "Dim adoRsBak As New ADODB.Recordset", and still got this error. hope here someone could help me

 

[Jasen]

I generally will do

Dim var as ADODB.object

Set var = new ADODB.object

should work fine.

To change to static cursor, replace the "adOpenDynamic" with "adOpenStatic". However, all those added options are not really needed unless you need to have read/write access, client side cursor, or dynamic/static cursor type. I often will simply write out:

varConn.Open strConnectString
varRst.Open strSQL_Qry, varConn

Also, why are you pulling the same query twice with adoRsCom and adoRsBak? And have you set a watch to see exactly what line it fails at?


________
Remember, you're unique... just like everyone else.

 

[simickey]

thanx jasen, I had solved my problem, I think should be the * problem when I excute sql. I chaged it to select (table_name), all works fine