ADO Errors

[JimmyJam]

I get Error 3001 "The Application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another. The microsoft solution does not work for me. My Code is
Set adoRS = New ADODB.Recordset
adoRS.Open "SELECT * FROM table WHERE Name = "JOHN",adoConnectToSQL,adopendynamic,adlockoptimistic,adcmdText
It bombs on the open line. My Ado connection object is being set in a public function called ConnectToSQL. The recordset object is being set and opened in another function. Any ideas? If I establish the connection in the same function as the recordset it works fine. But I don't want to have to establish a connection every time I create a recordset. I want to be able to use one connection throughout the project.

[Gimpster]

It bombs because you've got the recordset defined wrong, change

Set adoRS = New ADODB.Recordset

to

Dim adoRS as New ADODB.Recordset

The "Set" function is used when you want to set things like the ActiveConnection to an already defined and running connection. You would use it like this:

Set adoRS.ActiveConnection = Connection

------------------
Ryan
[email protected]
ICQ# 47799046



[This message has been edited by Gimpster (edited 12-29-1999).]

[Clunietp]

Actually, Gimpster, you would use Set adors = new adodb.connection when you want to instantiate the object.

It is considered better programming practice to declare like this:

dim cn as adodb.connection
set cn = new adodb.connection

and NOT like this

dim cn as new adodb.connection

=========================================
It is easier to debug when using global variables where the errors are happening (because you know where the object is being instantiated) and VB does not have to check on each call to the object if it was already instantiated or not.

[Clunietp]

Jimmy:

Use a global variable reference to an existing connection in your adoRS.Open method. That way, you can open a recordset in any sub/function, while still using only that one connection to the db.

Example:

'general/declarations of form/module
'depending on desired scope of object
dim cn as adodb.connection

Private Sub Form_Load()
    'this could also be sub_main
    set cn = new adodb.connection
    cn.open <ConnectionStringHere>
End Sub

Private Sub GetRecordSet()
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    
    rs.open <SQL Statement>, cn, adOpenDynamic, adLockOptimistic

    msgbox rs.fields(0).value
    rs.close
    set rs = nothing
end sub

[JimmyJam]

Error Message 300"The Application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another. The microsoft solution does not work for me.

Thanks everyone for your responses. But some of the responses I am doing in my code. Let me explain a bit more. I have a function that is called ConnectToSQL. This function establishes a connection. All of my variables are global. ADOConn is a class module that we use to connect. All of our developers can use this generic module to connect to SQL Servers.
Global Variables
Public Conn as ADODB.Connection
Public adoRS as ADODB.Recordset


Public Function ConnectToSQL()
Set Conn = New ADOConn
With Conn
.Provider = "SQLOLEDB"
.UserID = "User"
.Password = "Password"
.CursorLocation = "adUseClient"
.DatabaseName = "DBNAme"
.DataSource = "ServerName"
.OpenConnection
end with
End function
This code works fine I get a connection everytime with no problem.

Now I click a button to execute another function to open a recordset. Now note the connection is still open I have not closed it.

Public Function CheckFile()
dim strSQL as string

strSQL = "SELECT DISTINCT Filename FROM tblFileNames WHERE FileName = '1999Bill.dat'

Set adoRS = New ADODB.Recordset
adors.open strSQL,Conn,adOpenDynamic,adLockOptimistic, adcmdText


The error occurs on the adors.open

I hope this is a better explanation of the problem.

[Gimpster]

Clunietp, as you have often told me in the past (see post), the help files are your friend, and looking through the help files, or MSDN files, I have found many references to instantiating an object the way I had stated. However, I am an open-minded person who enjoys finding new ways to do the same task and I realize that there are many ways to accomplish the same task, and that each one has it's pros and cons. In going back through the MSDN files, I also found another way to instantiate an object:

Dim adoRs
Set adoRs = CreateObject("ADODB.Recordset")

With this method you can also instantiate specific versions of an object like this:

Set adoRs = CreateObject("ADODB.Recordset.2.0")

One of the bonuses of using this method is that you do not have to reference the ADO library in your project. However, this method is typically slower than the Dim method that you and I both mentioned. So for anyone else reading this, I would recommend using one of the two methods that Clunietp and I mentioned.


One additional thing, Clunietp, can you use your method to define the object in the General Declarations section? Meaning can you put both of the lines:

Dim adoRs
Set adoRs = New ADODB.Recordset

in the General Declarations of your code? Because that is what I need to do, and currently I have them defined in the General Declarations using the method that I suggested.

------------------
Ryan
[email protected]
ICQ# 47799046

[JimmyJam]

Hey everyone my problem is solved. When I instatiated my class module I assumed the variable Conn Set Conn = New ADOConn 'My class module

Would bring back the connection string the same as if I had instatiated it like
Set Conn = New ADODB.Connection

But it does not. SO I have to set a variable equal to the class modules connection string property and now it works