ADO Problem... 2

[GDX]

Ok what I am trying to do is create a function within VB, The fucntion will pass along 2 variables as a string, One of them is to create the object connection and the other will be the object row after running the SQL Query, this is what I have....

Public Function RUNConnection(ObjectZ As String, ObjectRow as String)
GDz = ObjectZ
Rowz = ObjectRow
Connstr = "DSN=vslink;UID=blah;PWD=blah"
Set GDz = CreateObject("Adodb.Connection&quot
GDz.Open Connstr
SQLQ = "select * from developers;"
Set Rowz = GDz.Execute(SQLQ)
RUNConnection = Rowz
End Function

In a command button I have the following...
myconn = RUNConnection("gd", "gdzrow&quot
MsgBox gdzrow

The program works up to opening the DSN connection but after that I get the following error on this line:

myconn = RUNConnection("gd", "gdzrow&quot

Error: Wrong number of arguments or invalid property assignment, any ideas? Basically i want to return the row after the query has been sent. Thanks a lot. Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371

 

[subhavs]

I might be wrong here. But shouldn't you specify the return type of the function during it's declaration.

- Subha Nothing is impossible, even the word impossible says I'm possible.

 

[Snaggs]

Looks like the data types have run amuck. But it can be fixed. My first suggestion is to use "Option Explicit" at the top of the modules. Subha also brought up a good point, that the return data type from the function should be defined. I think this is what has lead to the intitial confusion for the routine. If you don't define a return data type it defaults to a variant.

Your variable Rowz changes data type throught out the routine. In general this is not a good practice. It starts off as a variant since it's not defined (Using Option Explict would have made you define this variable), then gets assigned a string data type from the variable ObjectRow from the function parameter.

In the line that states: Set Rowz = GDz.Execute(SQLQ)
the data type changes again to a recordset object. The reason you had to use Set in the code is becuase you're returning a recordset from the Execute method of the ADO.Connection Object.

The Function name RunConnection is getting assigned to Rowz like this: RUNConnection = Rowz

Since the RunConnection function doesn't have a return data type this leads to the confusion about what the function is really returning. By following the code, it's returning a Recordset. Since the recordset is an object, when you assign it to something you must also use the word Set.

The first error is not having Set on the "RUNConnection = Rowz" line at the end of the function.

Now that we know what data type is being returned from the function, it should really be defined like this:

Public Function RUNConnection(ObjectZ As String, ObjectRow as String) As Recordset

As I said before the function returns an object, a recordset object to be exact. So when we assign a variable the value of the returned object, we must use the word Set.

The second error is in the code behind the Command button in the line:

myconn = RUNConnection("gd", "gdzrow&quot

It needs to look like this:

Set myconn = RUNConnection("gd", "gdzrow&quot

That should at least make the program run. However I would encourage the use of explicit data types instead of implicit data types. Implicit things in programming leaves a lot of unanswered questions as to what the code is really doing. Code like this can be extremely hard to debug!

By using Option Explict, you would have gotten many warnings regarding the lack of variabled being defined which should be a tip that something is wrong. Option Explicit can save you a lot of headache in a short amount of time. Snaggs
tribesaddict@swbell.net
2 wire mesh butchering gloves:
1 5-finger, 1 3-finger, pair: $15

 

[GDX]

GREATTT this worked, I learned a lot from the 2 posts I recieved, thanks a lot guys. Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371