Re: COM class Function - ADO - try this out.

[Sameer Gupta]

Hi Michael
How r u doin? I too faced similar problem last month with 'Addnew' option
of 'rs' (recordsource).
I used 'insert' statement instead of 'addnew' with 'rs' and it worked well.

I think that particular error occurs because of 'version problem'. Just try
using 'insert' statement once.

Good luck
Sam

"Michael Egbert" <egbertmr@sverdrup.com> wrote:
>
>I am trying to return an ADO recordset from a DLL class. The situation
is
>this:
>
>The class (in a DLL) has a function called CreateRS. The arguments are
the
>DataSource (used by the ADO Connection) and the Recordsource ("SELECT *
FROM
>TABLE"). I wnat this function to return a recordset so the syntax is thus:
>
>All variables have been dimmed.
>
>
>Public Function IConnRecSet_CreateRecordset(ByVal DataSource As String,
ByVal
>RecordSource As String, Optional SQLServer As String) As ADODB.Recordset
> Set mConn = New Connection
> Set mRecSet = New Recordset
> mConn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & DataSource
> mRecSet.Open RecordSource, mConn
> Set IConnRecSet_CreateRecordset = mRecSet
> Set mRecSet = Nothing
>End Function
>
>The ADODB typelib is referenced in this server, and the client.
>
>When I try to implement the object in the client this is the code:
>
>Dim connrec As CConnRecSetJet351
>Dim rs As ADODB.Recordset
>Dim DataSource as String
>Dim RecordSource = as String
>Dim fso As FileSystemObject
>Dim ts As TextStream
>Dim log as String
>
>Private Sub Form_Load()
>Set connrec = New CConnRecSetJet351
>Set rs = New Recordset
>
>
>
>DataSource = "c:\astuff\test.mdb"
>RecordSource = "SELECT * FROM Printlog"
>
>Set rs = connrec.IConnRecSet_CreateRecordset("c:\astuff\test.mdb", "SELECT
>* FROM Printlog")
>
>
>log = "C:\astuff\test.txt"
>Set ts = fso.OpenTextFile(log, ForReading, False)
>tmp = ts.ReadLine
>
>rs.AddNew
>rs.Fields(0).Value = Now 'Date Datatype
>rs.Fields(1).Value = "Name" 'String datatype
>rs.Fields(2).Value = tmp 'String datatype
>rs.Fields(3).Value = 10 'Integer datatype
>rs.Update
>rs.Close
>connrec.IConnRecSet_CloseRecordset 'Closes and kills the conn.
>
>Set rs = Nothing
>Set connrec = Nothing
>Set fso = Nothing
>Set ts = Nothing
>
>
>End Sub
>
>
>If you've read this far, thank you. The problem is when I try to run the
>AddNew method of rs I get the error - Run time Error 3251 - Object or provider
>is not capable of performing the requested operation.
>
>I don't know if the class can return the recordset intact with the info
set
>in the CreateRecordset function or if the Set rs = connrec.IConnRecSet_CreateRecordset
>is illegal or if rs should be defined as a variant so that it just "morphs"
>into the recordset from the function.
>
>I know this probably doesnt belong ion this group but I have a feeling it's
>the component not my ADO syntax or a general error.
>
>Thanks for your help.
>Michael

[Michael Egbert]

Sam,
Thanks for the idea. But I figured out what the problem was; the default
CursorType of a Recordset is ForwardOnly, which is read only. when I set
the CursorType to OpenDynamic the AddNew worked and I was able to write to
the recordset and save it to the DB.

I haven't looked at the Insert method but I will take a look. I have a feeling
that you need to have a record there to Insert data into, but I'm not sure.

Thanks again,
Michael


"Sameer Gupta" <jambo_sam@usa.net> wrote:
>
>Hi Michael
> How r u doin? I too faced similar problem last month with 'Addnew' option
>of 'rs' (recordsource).
>I used 'insert' statement instead of 'addnew' with 'rs' and it worked well.
>
>I think that particular error occurs because of 'version problem'. Just
try
>using 'insert' statement once.
>
>Good luck
>Sam
>
>"Michael Egbert" <egbertmr@sverdrup.com> wrote:
>>
>>I am trying to return an ADO recordset from a DLL class. The situation
>is
>>this:
>>
>>The class (in a DLL) has a function called CreateRS. The arguments are
>the
>>DataSource (used by the ADO Connection) and the Recordsource ("SELECT *
>FROM
>>TABLE"). I wnat this function to return a recordset so the syntax is thus:
>>
>>All variables have been dimmed.
>>
>>
>>Public Function IConnRecSet_CreateRecordset(ByVal DataSource As String,
>ByVal
>>RecordSource As String, Optional SQLServer As String) As ADODB.Recordset
>> Set mConn = New Connection
>> Set mRecSet = New Recordset
>> mConn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & DataSource
>> mRecSet.Open RecordSource, mConn
>> Set IConnRecSet_CreateRecordset = mRecSet
>> Set mRecSet = Nothing
>>End Function
>>
>>The ADODB typelib is referenced in this server, and the client.
>>
>>When I try to implement the object in the client this is the code:
>>
>>Dim connrec As CConnRecSetJet351
>>Dim rs As ADODB.Recordset
>>Dim DataSource as String
>>Dim RecordSource = as String
>>Dim fso As FileSystemObject
>>Dim ts As TextStream
>>Dim log as String
>>
>>Private Sub Form_Load()
>>Set connrec = New CConnRecSetJet351
>>Set rs = New Recordset
>>
>>
>>
>>DataSource = "c:\astuff\test.mdb"
>>RecordSource = "SELECT * FROM Printlog"
>>
>>Set rs = connrec.IConnRecSet_CreateRecordset("c:\astuff\test.mdb", "SELECT
>>* FROM Printlog")
>>
>>
>>log = "C:\astuff\test.txt"
>>Set ts = fso.OpenTextFile(log, ForReading, False)
>>tmp = ts.ReadLine
>>
>>rs.AddNew
>>rs.Fields(0).Value = Now 'Date Datatype
>>rs.Fields(1).Value = "Name" 'String datatype
>>rs.Fields(2).Value = tmp 'String datatype
>>rs.Fields(3).Value = 10 'Integer datatype
>>rs.Update
>>rs.Close
>>connrec.IConnRecSet_CloseRecordset 'Closes and kills the conn.
>>
>>Set rs = Nothing
>>Set connrec = Nothing
>>Set fso = Nothing
>>Set ts = Nothing
>>
>>
>>End Sub
>>
>>
>>If you've read this far, thank you. The problem is when I try to run the
>>AddNew method of rs I get the error - Run time Error 3251 - Object or
provider
>>is not capable of performing the requested operation.
>>
>>I don't know if the class can return the recordset intact with the info
>set
>>in the CreateRecordset function or if the Set rs = connrec.IConnRecSet_CreateRecordset
>>is illegal or if rs should be defined as a variant so that it just "morphs"
>>into the recordset from the function.
>>
>>I know this probably doesnt belong ion this group but I have a feeling
it's
>>the component not my ADO syntax or a general error.
>>
>>Thanks for your help.
>>Michael
>

[Richard Simpson]

Ok, I read your code. It looks like you have a read-only recordset. You are
using an ODBC connection to access, in SQL you would have to make sure that
the table had a primary(or at least unique) key so you could update it. Examine
the Updateable Property, then work out the coorect syntax. By the way, Mr
Gupta's solution is the more elegant, since adding new records increases
the size of your recordset. If you use the insert statement, you do not have
to have all those records in memory, just to insert new ones. You will find
the insert statement works better. I don't think it's a version problem though.
Cursor type (ODBC settings) or recordset type.

[Michael Egbert]

"Richard Simpson" <Richard.Simpson@epsrc.ac.uk> wrote:
>
>Ok, I read your code. It looks like you have a read-only recordset. You
are
>using an ODBC connection to access, in SQL you would have to make sure that
>the table had a primary(or at least unique) key so you could update it.
Examine
>the Updateable Property, then work out the coorect syntax. By the way, Mr
>Gupta's solution is the more elegant, since adding new records increases
>the size of your recordset. If you use the insert statement, you do not
have
>to have all those records in memory, just to insert new ones. You will find
>the insert statement works better. I don't think it's a version problem
though.
>Cursor type (ODBC settings) or recordset type.

Thanks Richard I will try the Insert statement.
Michael