ADO control works while ADO Connection String fails 
Author Message
 ADO control works while ADO Connection String fails

Dear all:

Please, explain the following problem:
There are ADO control and ADO connection string
in the same form. While Text Box controls and ADO
control work fine with SQL Server 7.0, the method
of ADO connection string cannot work:
MoveLast, MovePrevious, AddNew and of course, UpDate.

I use ADO Connection String to have access to data
from SQL server at declaration:

Option Explicit
Dim cnn1 As ADODB.Connection
Dim RecSet As ADODB.Recordset

and implementation:

Private Sub Form_Resize()

Dim strCnn As String

strCnn = "Provider=SQLOLEDB.1;" & _
    "Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=FromText;" & _
    "Data Source=ANHMYTRAN00"

Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

At an event by user's action:

Private Sub cmdTextSQL_Click()

Set RecSet = New ADODB.Recordset
RecSet.Open "Person", cnn1, , , adCmdTable

The action that work:
1- RecSet.MoveNext
2- RecSet.MoveFirst
' The feedback that shows the sucessful operation
MsgBox RecSet!FirstName & "  " & RecSet!LastName

The actions that cause errors:

RecSet.MovePrevious
Error -2147217884(80040e24)
"The rowset does not support fetching backwards"

RecSet.MoveLast
Error 3219
"The operation requested by the application
is not allowed in this context."

RecSet.AddNew
Error 3251
"The operation requested by the application
is not supported by the provider."

Of course, the following method cannot be tested:
RecSet.Update

All method that work give evidents by the feedback
with MsgBox(SQLRecordset!FieldName) that shows
the exact value as shown in the Text Box controls
connected to an ADO control.

Thank you in advance.

--
Posted via CNET Help.com
http://www.*-*-*.com/



Fri, 20 Sep 2002 03:00:00 GMT  
 ADO control works while ADO Connection String fails
Quote:

> Please, explain the following problem:
> There are ADO control and ADO connection string
> in the same form. While Text Box controls and ADO
> control work fine with SQL Server 7.0, the method
> of ADO connection string cannot work:
> MoveLast, MovePrevious, AddNew and of course, UpDate.

<snip>

  I don't see that your code sets a CursorType, so ADO gives you the
default "adOpenForwardOnly" - that explains why you can MoveNext but not
MovePrevious.  Why MoveFirst works and MoveLast doesn't:  VB Help says
"You can call the MoveFirst method in a forward-only Recordset object;
doing so may cause the provider to re-execute the command that generated
the Recordset object."  ...and about MoveLast "The Recordset object must
support bookmarks or backward cursor movement; otherwise, the method
call will generate an error."  That seems backwards to me, but that's
ADO for ya!
  You did not specify LockType; default is "adLockReadOnly" so you can't
AddNew or Update.
  The ADO data control is designed with Cursor and Lock Types to support
moving all directions, adding/editing.  Add the settings you want to
your Open method, and you should be okay.
--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"
 ((("What's so funny 'bout peace, love & understanding?" - Nick Lowe)))



Fri, 20 Sep 2002 03:00:00 GMT  
 ADO control works while ADO Connection String fails
Dear Mr. Jim in Cleveland:

Thank you for your help.

Yes. In my experiment, I can have a loop MoveFirst then MoveNext until EOF
and keep looping over and over displaying Each Current Record. That means
that this experiment is Read-only, and Keep Looping forward Only. The
MoveFirst enables the MoveNext loop. Even though it seems to be backwards
to me prior to your help, it is consistent with the MoveNext design.

Yes. Your help is very helpful. I will study on the 2 keywords
"CursorType" and "LockType". I have no idea where I can code them.

Quote:
> The ADO data control is designed with Cursor and Lock Types to support
> moving all directions, adding/editing.  Add the settings you want to
> your Open method, and you should be okay.

I hope so.

Have a nice day.
AnhMy Tran.

--
Posted via CNET Help.com
http://www.help.com/



Fri, 20 Sep 2002 03:00:00 GMT  
 ADO control works while ADO Connection String fails

Quote:

> Yes. Your help is very helpful. I will study on the 2 keywords
> "CursorType" and "LockType". I have no idea where I can code them.

You would change your line:

RecSet.Open "Person", cnn1, , , adCmdTable

...to this:

RecSet.Open "Person", cnn1, adOpenDynamic, adLockOptimistic, adCmdTable

  There is more info on this in VB Help under "Open Method (ADO
Recordset)"
Regards,  
--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"
 ((("What's so funny 'bout peace, love & understanding?" - Nick Lowe)))



Fri, 20 Sep 2002 03:00:00 GMT  
 ADO control works while ADO Connection String fails

Quote:

> Dear Mr. Jim in Cleveland:

> Thank you for your help.

> Yes. In my experiment, I can have a loop MoveFirst then MoveNext
until EOF
> and keep looping over and over displaying Each Current Record. That
means
> that this experiment is Read-only, and Keep Looping forward Only. The
> MoveFirst enables the MoveNext loop. Even though it seems to be
backwards
> to me prior to your help, it is consistent with the MoveNext design.

> Yes. Your help is very helpful. I will study on the 2 keywords
> "CursorType" and "LockType". I have no idea where I can code them.

> > The ADO data control is designed with Cursor and Lock Types to
support
> > moving all directions, adding/editing. Add the settings you want to
> > your Open method, and you should be okay.

> I hope so.

> Have a nice day.
> AnhMy Tran.

> --
> Posted via CNET Help.com
> http://www.help.com/

I tend to use this code as a template.

Option Explicit
Dim MyRs As ADODB.Recordset

Private Sub Form_Load()
Dim sStr As String
sStr = "Provider = Microsoft.Jet.Oledb.4.0;Data Source = " & App.Path &
"\MyDatabase.mdb"
Dim Cn As ADODB.Connection

On Error GoTo AdoError

Set Cn = New ADODB.Connection
Cn.CursorLocation = adUseClient
Cn.Open sStr

'create the recordset
Dim sSQL As String
sSQL = "SELECT * FROM MyTable ORDER BY ChooseField"

Set MyRs = New ADODB.Recordset
MyRs.Open sSQL, Cn, adOpenStatic, adLockOptimistic

'populate the controls
Dim i As Integer
For i = 0 To Text1.Count - 1
Set Text1(i).DataSource = MyRs
Next i

'Other code here
Exit Sub

' ADO Error Handler
AdoError:
   ErrNumber = Err.Number
   ErrSource = Err.Source
   ErrDescription = Err.Description

End Sub

Cheers,
Dave

--
dcward

Sent via Deja.com http://www.deja.com/
Before you buy.



Sat, 21 Sep 2002 03:00:00 GMT  
 ADO control works while ADO Connection String fails

Quote:
> You would change your line:
> RecSet.Open "Person", cnn1, , , adCmdTable

> ...to this:
> RecSet.Open "Person", cnn1, adOpenDynamic, adLockOptimistic, adCmdTable

Thank you. Yes. I have changed as exactly as the above code.
It works perfectly. I have done many more experiments with it.

I will try the template by Mr. CDWard as well.
It is good to have many advisors and suggestions.
Even though I have books and Online Help, it is better to know
the direction rather than dying of hunger of knowledge between available
resources.

--
Posted via CNET Help.com
http://www.help.com/



Sat, 21 Sep 2002 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. ADO connection passed to a DLL fails to maintain valid connection

2. Changing the ADO control connection string at run time

3. Why ISAM error using Access and unbound ADO controls with sql string connection

4. Change ADO Control connection string at runtime (error)

5. Deploying ADO Data bound controls / changing connection string

6. ADO Bound Controls & Connections Strings

7. ADO Connection fails with 'sa'

8. ADO connection fails

9. ADO Connection failing after few successes

10. ?ADO connection reference passed to dll failing

11. ADO Connection to Oracle DB with Arabic Username Fails

12. Q: ADO Connection / Properties failed!?

 

 
Powered by phpBB® Forum Software