SQL Table Valued Function

[spinkung]

Hi


i'm trying to connect to a table valued function on sql (2005) but keep getting the following error:

argument are of the wrong type, are out of acceptable range, or in conflict with one another

here's the code

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=mydsn;UID=myuser;PWD=mypw;"
cnn.Open

Dim myValue As String
myValue = "123456"

rst.Open "select * from [dbo].[tvf_ASN_bodyShop_header] ('" & myValue & "')", cnn.Connection, adOpenKeyset, adLockReadOnly

is there another way i should be doing this?

Thanks

 

[Galaxiom]

cnn is the connect object. Not cnn.connection

 

[spinkung]

thanks


so that now leads to the following error...

Object variable or with variable not set

rst.Open "select * from [dbo].[tvf_ASN_bodyShop_header] ('" & myAsn & "')", cnn, adOpenKeyset, adLockReadOnly

 

[spinkung]

resolved:

this is the way to use it...

Set rst = cnn.Execute("select * from [dbo].[tvf_ASN_bodyShop_header] ('" & myAsn & "')")

 

[Galaxiom]

That is one way.

The other problem with your first attempt is that you didn't instantiate the recordset first.

Set rs = New ADODB.Recordset
rs.Open blah