Connection Errors via ADO Connection Object 
Author Message
 Connection Errors via ADO Connection Object

I recently changed this bit of code to add a record via the .addnew method.
I get a message that says "Run-time error '91': Object Variable or With
block variable not set."  when I hit debug it highlights the line "    Set
cmd.ActiveConnection = connSQL".  Any ideas would be greatly appreciated!
Thanks!!!

ABH

Private Sub Command1_Click()
    Dim connSQL As ADODB.Connection
    Dim strSQL As String
    Dim strConn As String
    Dim cmd As ADODB.Command
    Dim rsEmail As ADODB.Recordset

    Set connSQL = New ADODB.Connection
    Set cmd.ActiveConnection = connSQL
    cmd.CommandType = adCmdTable
    cmd.CommandText = "EmailData"
    Set rsEmail = cmd.Execute

    strConn = "Provider=sqloledb;Data Source=DEVELOPMENT\DEVTEST1;Initial
Catalog=EmailDB;User Id=EmailProcessor;Password=emailprocessor;"

    connSQL.ConnectionString = strConn
    connSQL.Open

    With rsEmail
        .AddNew

        !Subject = "Another Subject for 800 times"
        !received = "11/11/99"
        !Body = "blagh blagh blagh this is the body of the e-mail letter."
        !processed = "0"
        .Update
    End With

    'Cleanup Objects
    rsEmail.Close
    Set rsEmail = Nothing
    connSQL.Close
    Set connSQL = Nothing
    Set cmd = Nothing


'This is the body of the message', 1
End Sub



Sat, 21 Aug 2004 03:25:27 GMT  
 Connection Errors via ADO Connection Object
Try doing the connection open stuff before assigning it to the
ActiveConnection property.

Bruce Johnson
TAG Consulting
http://www.tagconsulting.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 21 Aug 2004 03:40:30 GMT  
 Connection Errors via ADO Connection Object
Hi,

It looks like you have declared command object variable
(cmd) but did not initialize it. Check if you have

Set cmd=New ADODB.Command

before first use of that variable

Val

Quote:
>-----Original Message-----
>I recently changed this bit of code to add a record via
the .addnew method.
>I get a message that says "Run-time error '91': Object
Variable or With
>block variable not set."  when I hit debug it highlights
the line "    Set
>cmd.ActiveConnection = connSQL".  Any ideas would be

greatly appreciated!
Quote:
>Thanks!!!

>ABH

>Private Sub Command1_Click()
>    Dim connSQL As ADODB.Connection
>    Dim strSQL As String
>    Dim strConn As String
>    Dim cmd As ADODB.Command
>    Dim rsEmail As ADODB.Recordset

>    Set connSQL = New ADODB.Connection
>    Set cmd.ActiveConnection = connSQL
>    cmd.CommandType = adCmdTable
>    cmd.CommandText = "EmailData"
>    Set rsEmail = cmd.Execute

>    strConn = "Provider=sqloledb;Data

Source=DEVELOPMENT\DEVTEST1;Initial
Quote:
>Catalog=EmailDB;User

Id=EmailProcessor;Password=emailprocessor;"

- Show quoted text -

Quote:

>    connSQL.ConnectionString = strConn
>    connSQL.Open

>    With rsEmail
>        .AddNew

>        !Subject = "Another Subject for 800 times"
>        !received = "11/11/99"
>        !Body = "blagh blagh blagh this is the body of
the e-mail letter."
>        !processed = "0"
>        .Update
>    End With

>    'Cleanup Objects
>    rsEmail.Close
>    Set rsEmail = Nothing
>    connSQL.Close
>    Set connSQL = Nothing
>    Set cmd = Nothing



subject', '4/20/99',

- Show quoted text -

Quote:
>'This is the body of the message', 1
>End Sub

>.



Sat, 21 Aug 2004 03:42:31 GMT  
 Connection Errors via ADO Connection Object
I tried this and am still getting the error message.  I'm plum out of ideas?
:(

ABH

Private Sub Command1_Click()
    Dim cmd As adodb.Command
    Dim rsEmail As adodb.Recordset
    Dim connSQL As adodb.Connection
    Dim strSQL As String
    Dim strConn As String

    Set connSQL = New adodb.Connection

    strConn = "Provider=sqloledb;Data Source=DEVELOPMENT\DEVTEST1;Initial
Catalog=EmailDB;User Id=EmailProcessor;Password=emailprocessor;"

    connSQL.ConnectionString = strConn
    connSQL.Open

    Set cmd.ActiveConnection = connSQL
    cmd.CommandType = adCmdTable
    cmd.CommandText = "EmailData"
    Set rsEmail = cmd.Execute

    With rsEmail
        .AddNew

        !Subject = "Another Subject for 800 times"
        !received = "11/11/99"
        !Body = "blagh blagh blagh this is the body of the e-mail letter."
        !processed = "0"
        .Update
    End With

    'Cleanup Objects
    rsEmail.Close
    Set rsEmail = Nothing
    connSQL.Close
    Set connSQL = Nothing
    Set cmd = Nothing

End Sub


Quote:
> Try doing the connection open stuff before assigning it to the
> ActiveConnection property.

> Bruce Johnson
> TAG Consulting
> http://www.tagconsulting.com

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Sat, 21 Aug 2004 03:47:54 GMT  
 Connection Errors via ADO Connection Object
How do I change the recordset support for updating?  Now that I've got this
figured out I don't remember/know how to change the locktype.  ... Thus I
get the error "Run-time error 3251 - Current recordset does not support
updating.  This may be a limitation of the provider, or of the selected
locktype.

Thanks again all!
ABH

PS:  This is my current code.

    Dim cmd As ADODB.Command
    Dim rsEmail As ADODB.Recordset
    Dim connSQL As ADODB.Connection
    Dim strSQL As String
    Dim strConn As String

    Set connSQL = New ADODB.Connection
    Set cmd = New ADODB.Command

    strConn = "Provider=sqloledb;Data Source=DEVELOPMENT\DEVTEST1;Initial
Catalog=EmailDB;User Id=EmailProcessor;Password=emailprocessor;"

    connSQL.ConnectionString = strConn
    connSQL.Open

    Set cmd.ActiveConnection = connSQL
    cmd.CommandType = adCmdTable
    cmd.CommandText = "EmailData"
    Set rsEmail = cmd.Execute

    With rsEmail
        .AddNew

        !Subject = "Another Subject for 800 times"
        !received = "11/11/99"
        !Body = "blagh blagh blagh this is the body of the e-mail letter."
        !processed = "0"
        .Update
    End With

    'Cleanup Objects
    rsEmail.Close
    Set rsEmail = Nothing
    connSQL.Close
    Set connSQL = Nothing
    Set cmd = Nothing



Sat, 21 Aug 2004 03:58:12 GMT  
 Connection Errors via ADO Connection Object
Add the following code:

rsEmail.CursorLocation = adUseClient
rsEmail.LockType = adLockOptimistic

Good luck!

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.



Sat, 21 Aug 2004 04:50:16 GMT  
 Connection Errors via ADO Connection Object
A command object always returns a forwardonly readonly
recordset.  You need to Open the recordset using the
command, instead of executing the command.

Replace this line
Set rsEmail = cmd.Execute

With

Set rsEmail = new ADODB.Recordset
rsEmail.Open cmd,,adOpenStatic,adLockOptimistic
   (or use whatever cursor type/lock type you need).

HTH
Bruce

Quote:
>-----Original Message-----
>How do I change the recordset support for updating?  Now
that I've got this
>figured out I don't remember/know how to change the

locktype.  ... Thus I
Quote:
>get the error "Run-time error 3251 - Current recordset
does not support
>updating.  This may be a limitation of the provider, or
of the selected
>locktype.

>Thanks again all!
>ABH

>PS:  This is my current code.

>    Dim cmd As ADODB.Command
>    Dim rsEmail As ADODB.Recordset
>    Dim connSQL As ADODB.Connection
>    Dim strSQL As String
>    Dim strConn As String

>    Set connSQL = New ADODB.Connection
>    Set cmd = New ADODB.Command

>    strConn = "Provider=sqloledb;Data

Source=DEVELOPMENT\DEVTEST1;Initial
Quote:
>Catalog=EmailDB;User

Id=EmailProcessor;Password=emailprocessor;"

- Show quoted text -

Quote:

>    connSQL.ConnectionString = strConn
>    connSQL.Open

>    Set cmd.ActiveConnection = connSQL
>    cmd.CommandType = adCmdTable
>    cmd.CommandText = "EmailData"
>    Set rsEmail = cmd.Execute

>    With rsEmail
>        .AddNew

>        !Subject = "Another Subject for 800 times"
>        !received = "11/11/99"
>        !Body = "blagh blagh blagh this is the body of
the e-mail letter."
>        !processed = "0"
>        .Update
>    End With

>    'Cleanup Objects
>    rsEmail.Close
>    Set rsEmail = Nothing
>    connSQL.Close
>    Set connSQL = Nothing
>    Set cmd = Nothing

>.



Sat, 21 Aug 2004 08:17:57 GMT  
 Connection Errors via ADO Connection Object
Hi,

This is not actually like this. Command object returns
read-only, forward-only recordset BY DEFAULT, but not
always. If you specify CursorLocation of CONNECTION
object, associated with command, as adUseClient, then you
have possibility to open recordset on client side, which
will NOT be READ-ONLY and FORWARD-ONLY

Val

Quote:
>-----Original Message-----
>A command object always returns a forwardonly readonly
>recordset.  You need to Open the recordset using the
>command, instead of executing the command.

>Replace this line
>Set rsEmail = cmd.Execute

>With

>Set rsEmail = new ADODB.Recordset
>rsEmail.Open cmd,,adOpenStatic,adLockOptimistic
>   (or use whatever cursor type/lock type you need).

>HTH
>Bruce

>>-----Original Message-----
>>How do I change the recordset support for updating?  Now
>that I've got this
>>figured out I don't remember/know how to change the
>locktype.  ... Thus I
>>get the error "Run-time error 3251 - Current recordset
>does not support
>>updating.  This may be a limitation of the provider, or
>of the selected
>>locktype.

>>Thanks again all!
>>ABH

>>PS:  This is my current code.

>>    Dim cmd As ADODB.Command
>>    Dim rsEmail As ADODB.Recordset
>>    Dim connSQL As ADODB.Connection
>>    Dim strSQL As String
>>    Dim strConn As String

>>    Set connSQL = New ADODB.Connection
>>    Set cmd = New ADODB.Command

>>    strConn = "Provider=sqloledb;Data
>Source=DEVELOPMENT\DEVTEST1;Initial
>>Catalog=EmailDB;User
>Id=EmailProcessor;Password=emailprocessor;"

>>    connSQL.ConnectionString = strConn
>>    connSQL.Open

>>    Set cmd.ActiveConnection = connSQL
>>    cmd.CommandType = adCmdTable
>>    cmd.CommandText = "EmailData"
>>    Set rsEmail = cmd.Execute

>>    With rsEmail
>>        .AddNew

>>        !Subject = "Another Subject for 800 times"
>>        !received = "11/11/99"
>>        !Body = "blagh blagh blagh this is the body of
>the e-mail letter."
>>        !processed = "0"
>>        .Update
>>    End With

>>    'Cleanup Objects
>>    rsEmail.Close
>>    Set rsEmail = Nothing
>>    connSQL.Close
>>    Set connSQL = Nothing
>>    Set cmd = Nothing

>>.

>.



Sat, 21 Aug 2004 20:29:09 GMT  
 Connection Errors via ADO Connection Object
Sorry, you are right of course. Using the SQL Server OLEDB
provider the recordset is a Static cursor that is read
only for a client side cursor.  Should I assume that this
is provider specific?

Bruce

Quote:
>-----Original Message-----
>Hi,

>This is not actually like this. Command object returns
>read-only, forward-only recordset BY DEFAULT, but not
>always. If you specify CursorLocation of CONNECTION
>object, associated with command, as adUseClient, then you
>have possibility to open recordset on client side, which
>will NOT be READ-ONLY and FORWARD-ONLY

>Val

>>-----Original Message-----
>>A command object always returns a forwardonly readonly
>>recordset.  You need to Open the recordset using the
>>command, instead of executing the command.

>>Replace this line
>>Set rsEmail = cmd.Execute

>>With

>>Set rsEmail = new ADODB.Recordset
>>rsEmail.Open cmd,,adOpenStatic,adLockOptimistic
>>   (or use whatever cursor type/lock type you need).

>>HTH
>>Bruce

>>>-----Original Message-----
>>>How do I change the recordset support for updating?  
Now
>>that I've got this
>>>figured out I don't remember/know how to change the
>>locktype.  ... Thus I
>>>get the error "Run-time error 3251 - Current recordset
>>does not support
>>>updating.  This may be a limitation of the provider, or
>>of the selected
>>>locktype.

>>>Thanks again all!
>>>ABH

>>>PS:  This is my current code.

>>>    Dim cmd As ADODB.Command
>>>    Dim rsEmail As ADODB.Recordset
>>>    Dim connSQL As ADODB.Connection
>>>    Dim strSQL As String
>>>    Dim strConn As String

>>>    Set connSQL = New ADODB.Connection
>>>    Set cmd = New ADODB.Command

>>>    strConn = "Provider=sqloledb;Data
>>Source=DEVELOPMENT\DEVTEST1;Initial
>>>Catalog=EmailDB;User
>>Id=EmailProcessor;Password=emailprocessor;"

>>>    connSQL.ConnectionString = strConn
>>>    connSQL.Open

>>>    Set cmd.ActiveConnection = connSQL
>>>    cmd.CommandType = adCmdTable
>>>    cmd.CommandText = "EmailData"
>>>    Set rsEmail = cmd.Execute

>>>    With rsEmail
>>>        .AddNew

>>>        !Subject = "Another Subject for 800 times"
>>>        !received = "11/11/99"
>>>        !Body = "blagh blagh blagh this is the body of
>>the e-mail letter."
>>>        !processed = "0"
>>>        .Update
>>>    End With

>>>    'Cleanup Objects
>>>    rsEmail.Close
>>>    Set rsEmail = Nothing
>>>    connSQL.Close
>>>    Set connSQL = Nothing
>>>    Set cmd = Nothing

>>>.

>>.

>.



Sun, 22 Aug 2004 01:15:12 GMT  
 Connection Errors via ADO Connection Object
Hi,

Yes, it depends on provider

Val

Quote:
>-----Original Message-----
>Sorry, you are right of course. Using the SQL Server
OLEDB
>provider the recordset is a Static cursor that is read
>only for a client side cursor.  Should I assume that this
>is provider specific?

>Bruce

>>-----Original Message-----
>>Hi,

>>This is not actually like this. Command object returns
>>read-only, forward-only recordset BY DEFAULT, but not
>>always. If you specify CursorLocation of CONNECTION
>>object, associated with command, as adUseClient, then
you
>>have possibility to open recordset on client side, which
>>will NOT be READ-ONLY and FORWARD-ONLY

>>Val

>>>-----Original Message-----
>>>A command object always returns a forwardonly readonly
>>>recordset.  You need to Open the recordset using the
>>>command, instead of executing the command.

>>>Replace this line
>>>Set rsEmail = cmd.Execute

>>>With

>>>Set rsEmail = new ADODB.Recordset
>>>rsEmail.Open cmd,,adOpenStatic,adLockOptimistic
>>>   (or use whatever cursor type/lock type you need).

>>>HTH
>>>Bruce

>>>>-----Original Message-----
>>>>How do I change the recordset support for updating?  
>Now
>>>that I've got this
>>>>figured out I don't remember/know how to change the
>>>locktype.  ... Thus I
>>>>get the error "Run-time error 3251 - Current recordset
>>>does not support
>>>>updating.  This may be a limitation of the provider,
or
>>>of the selected
>>>>locktype.

>>>>Thanks again all!
>>>>ABH

>>>>PS:  This is my current code.

>>>>    Dim cmd As ADODB.Command
>>>>    Dim rsEmail As ADODB.Recordset
>>>>    Dim connSQL As ADODB.Connection
>>>>    Dim strSQL As String
>>>>    Dim strConn As String

>>>>    Set connSQL = New ADODB.Connection
>>>>    Set cmd = New ADODB.Command

>>>>    strConn = "Provider=sqloledb;Data
>>>Source=DEVELOPMENT\DEVTEST1;Initial
>>>>Catalog=EmailDB;User
>>>Id=EmailProcessor;Password=emailprocessor;"

>>>>    connSQL.ConnectionString = strConn
>>>>    connSQL.Open

>>>>    Set cmd.ActiveConnection = connSQL
>>>>    cmd.CommandType = adCmdTable
>>>>    cmd.CommandText = "EmailData"
>>>>    Set rsEmail = cmd.Execute

>>>>    With rsEmail
>>>>        .AddNew

>>>>        !Subject = "Another Subject for 800 times"
>>>>        !received = "11/11/99"
>>>>        !Body = "blagh blagh blagh this is the body of
>>>the e-mail letter."
>>>>        !processed = "0"
>>>>        .Update
>>>>    End With

>>>>    'Cleanup Objects
>>>>    rsEmail.Close
>>>>    Set rsEmail = Nothing
>>>>    connSQL.Close
>>>>    Set connSQL = Nothing
>>>>    Set cmd = Nothing

>>>>.

>>>.

>>.

>.



Sun, 22 Aug 2004 04:05:10 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. VB6.0/Access connection via ADO objects

2. errors different when executing SQL statements via recordset or connection object

3. ON ERROR GOTO does not fire using ADO connection object

4. Error in ADO Connection Object

5. -2147467259 Unspecified error using ADO 2.5 connection object

6. Multi User Connection via COM for database connection

7. ADO Command Object using Multiple Connection Objects

8. Connection Problem - ado failover connection

9. ADO Connection State Does Not Reflect Actual Connection State

10. ADO Connection - How to set ANSI_NULLS OFF through the connection

11. Release ADO connection from a connection pool

12. ADO Connection (test + controlService via API)

 

 
Powered by phpBB® Forum Software