As part of an ADO Recordset, I would like to create an empty Field. I would
like to open the RecordSet (using an SQL statement) with data from an SQL
Server database, disconnect the Recordset, and then use the empty Field to
enter additional data. I can do all of this, but . . .

My question is - How do I assign this empty field a data type?

(I do not believe I can use the ".Fields.Append" method, since any existing
fields would be wiped out when I open the recordset with my SQL command. So,
I need to create this empty field and assign it a datatype in the same SQL
statement that I use to access data from the SQL Server database. I think.
Sigh.)

Thanks much!

I haven't tested this in any detail, but you can kludge it with something
like this in your SQL select command:

SELECT 0 as MyIntegerField, Field1, Field2 FROM View1

ADO assigns an integer type to the field. I suspect if you tried

SELECT "This is a string", Field1 etc

that ADO might assign a variant type of some form.

Give it a try.

Alternatively,

Can you amend the view that you are selecting from to contain a field with
null values in the type that you want?

Robin Hammond
www.enhanceddatasystems.com

"Developer of the Caribbean"
wrote in message
...
As part of an ADO Recordset, I would like to create an empty Field. I
would
like to open the RecordSet (using an SQL statement) with data from an SQL
Server database, disconnect the Recordset, and then use the empty Field to
enter additional data. I can do all of this, but . . .

My question is - How do I assign this empty field a data type?

(I do not believe I can use the ".Fields.Append" method, since any
existing
fields would be wiped out when I open the recordset with my SQL command.
So,
I need to create this empty field and assign it a datatype in the same SQL
statement that I use to access data from the SQL Server database. I
think.
Sigh.)

Thanks much!

Robin, thank you very much for your answer. I was able to amend an existing
field (essentially, I threw in a field (with the right data type) from the
database that I did not need, and then put new data in it after the recordset
was opened and disconnected.) That should work for my current application.

I'm curious if there is a more direct way of doing it. I've found that some
SQL applications use a "Cast" command to assign a field a specific data type,
but I have been unable to get such a command to work with ADO.

"Robin Hammond" wrote:

I haven't tested this in any detail, but you can kludge it with something
like this in your SQL select command:

SELECT 0 as MyIntegerField, Field1, Field2 FROM View1

ADO assigns an integer type to the field. I suspect if you tried

SELECT "This is a string", Field1 etc

that ADO might assign a variant type of some form.

Give it a try.

Alternatively,

Can you amend the view that you are selecting from to contain a field with
null values in the type that you want?

Robin Hammond
www.enhanceddatasystems.com

"Developer of the Caribbean"
wrote in message
...
As part of an ADO Recordset, I would like to create an empty Field. I
would
like to open the RecordSet (using an SQL statement) with data from an SQL
Server database, disconnect the Recordset, and then use the empty Field to
enter additional data. I can do all of this, but . . .

My question is - How do I assign this empty field a data type?

(I do not believe I can use the ".Fields.Append" method, since any
existing
fields would be wiped out when I open the recordset with my SQL command.
So,
I need to create this empty field and assign it a datatype in the same SQL
statement that I use to access data from the SQL Server database. I
think.
Sigh.)

Thanks much!

As far as I know, once you have opened a recordset you can't change the
fields or datatypes.

What I sometimes do is create the recordset at the client with the fields I
want, usually because I am retrieving multiple subsets of data using
multiple queries, then add the data from the subqueries to the larger client
side recordset, which allows me to manipulate all data in one RS. Or I might
set up a view with no data in it but the correct data structure and use that
to set up the client side recordset which then has data appended it to it
from all the subqueries. Not very elegant but I have my reasons. It's not
trivial, so the routine to append data is shown below. It appears overly
complicated because it is designed to handle differing field orders between
similar recordsets.

In your case, you could
1. set up the exact data structure you want in an empty recordset (RS1) at
the client, including the additional fields
2. open RS1
3. retrieve your data as it exists at the sql server (RS2) without the
additional field(s) you want to use at the client
4 appendrs RS1,RS2
5 use the empty fields in RS1 to do whatever you want

Public Sub AppendRS(rsOutput As ADODB.Recordset, _
rsInput As ADODB.Recordset)
'---------------------------------------------------------------------------------------
' Procedure : AppendRS
' DateTime : 4/15/2004 12:24
' Author : Robin Hammond
' Purpose : creates a recordset if it doesn't exist and adds input values
' to the output recordset
'---------------------------------------------------------------------------------------

Dim lFieldCounter As Long
'if the output rs is empty then copy across the fields
With rsOutput
If .State < adStateOpen Then
For lFieldCounter = 0 To rsInput.Fields.Count - 1
If Not RsFieldExists(rsOutput,
rsInput.Fields(lFieldCounter).Name) Then
.Fields.Append rsInput.Fields(lFieldCounter).Name, _
rsInput.Fields(lFieldCounter).Type, _
rsInput.Fields(lFieldCounter).DefinedSize, _
(rsInput.Fields(lFieldCounter).Attributes Or _
adFldUpdatable) And Not adFldUnknownUpdatable
End If
Next lFieldCounter
.Open CursorType:=adOpenStatic
End If
If rsInput.RecordCount = 0 Then
On Error GoTo 0
Exit Sub
End If
rsInput.MoveFirst
Do While Not rsInput.EOF
.AddNew
For lFieldCounter = 0 To .Fields.Count - 1
If RsFieldExists(rsInput, .Fields(lFieldCounter).Name) Then
.Fields(lFieldCounter).Value =
rsInput.Fields(.Fields(lFieldCounter).Name).Value
End If
Next lFieldCounter
rsInput.MoveNext
Loop
End With
End Sub

Public Function RsFieldExists(rsTest As ADODB.Recordset, strFieldName As
String) As Boolean
'returns true if a recordset contains fields name strfieldname
Dim fldTest As ADODB.Field
On Error Resume Next
Set fldTest = rsTest.Fields(strFieldName)
On Error GoTo 0
RsFieldExists = Not fldTest Is Nothing
End Function

Robin Hammond
www.enhanceddatasystems.com

"Developer of the Caribbean"
wrote in message
...
Robin, thank you very much for your answer. I was able to amend an
existing
field (essentially, I threw in a field (with the right data type) from the
database that I did not need, and then put new data in it after the
recordset
was opened and disconnected.) That should work for my current
application.

I'm curious if there is a more direct way of doing it. I've found that
some
SQL applications use a "Cast" command to assign a field a specific data
type,
but I have been unable to get such a command to work with ADO.

"Robin Hammond" wrote:

I haven't tested this in any detail, but you can kludge it with something
like this in your SQL select command:

SELECT 0 as MyIntegerField, Field1, Field2 FROM View1

ADO assigns an integer type to the field. I suspect if you tried

SELECT "This is a string", Field1 etc

that ADO might assign a variant type of some form.

Give it a try.

Alternatively,

Can you amend the view that you are selecting from to contain a field
with
null values in the type that you want?

Robin Hammond
www.enhanceddatasystems.com

"Developer of the Caribbean"
wrote in message
...
As part of an ADO Recordset, I would like to create an empty Field. I
would
like to open the RecordSet (using an SQL statement) with data from an
SQL
Server database, disconnect the Recordset, and then use the empty Field
to
enter additional data. I can do all of this, but . . .

My question is - How do I assign this empty field a data type?

(I do not believe I can use the ".Fields.Append" method, since any
existing
fields would be wiped out when I open the recordset with my SQL
command.
So,
I need to create this empty field and assign it a datatype in the same
SQL
statement that I use to access data from the SQL Server database. I
think.
Sigh.)

Thanks much!

Thanks again! This is extremely helpful!

"Robin Hammond" wrote:

As far as I know, once you have opened a recordset you can't change the
fields or datatypes.

What I sometimes do is create the recordset at the client with the fields I
want, usually because I am retrieving multiple subsets of data using
multiple queries, then add the data from the subqueries to the larger client
side recordset, which allows me to manipulate all data in one RS. Or I might
set up a view with no data in it but the correct data structure and use that
to set up the client side recordset which then has data appended it to it
from all the subqueries. Not very elegant but I have my reasons. It's not
trivial, so the routine to append data is shown below. It appears overly
complicated because it is designed to handle differing field orders between
similar recordsets.

In your case, you could
1. set up the exact data structure you want in an empty recordset (RS1) at
the client, including the additional fields
2. open RS1
3. retrieve your data as it exists at the sql server (RS2) without the
additional field(s) you want to use at the client
4 appendrs RS1,RS2
5 use the empty fields in RS1 to do whatever you want

Public Sub AppendRS(rsOutput As ADODB.Recordset, _
rsInput As ADODB.Recordset)
'---------------------------------------------------------------------------------------
' Procedure : AppendRS
' DateTime : 4/15/2004 12:24
' Author : Robin Hammond
' Purpose : creates a recordset if it doesn't exist and adds input values
' to the output recordset
'---------------------------------------------------------------------------------------

Dim lFieldCounter As Long
'if the output rs is empty then copy across the fields
With rsOutput
If .State < adStateOpen Then
For lFieldCounter = 0 To rsInput.Fields.Count - 1
If Not RsFieldExists(rsOutput,
rsInput.Fields(lFieldCounter).Name) Then
.Fields.Append rsInput.Fields(lFieldCounter).Name, _
rsInput.Fields(lFieldCounter).Type, _
rsInput.Fields(lFieldCounter).DefinedSize, _
(rsInput.Fields(lFieldCounter).Attributes Or _
adFldUpdatable) And Not adFldUnknownUpdatable
End If
Next lFieldCounter
.Open CursorType:=adOpenStatic
End If
If rsInput.RecordCount = 0 Then
On Error GoTo 0
Exit Sub
End If
rsInput.MoveFirst
Do While Not rsInput.EOF
.AddNew
For lFieldCounter = 0 To .Fields.Count - 1
If RsFieldExists(rsInput, .Fields(lFieldCounter).Name) Then
.Fields(lFieldCounter).Value =
rsInput.Fields(.Fields(lFieldCounter).Name).Value
End If
Next lFieldCounter
rsInput.MoveNext
Loop
End With
End Sub

Public Function RsFieldExists(rsTest As ADODB.Recordset, strFieldName As
String) As Boolean
'returns true if a recordset contains fields name strfieldname
Dim fldTest As ADODB.Field
On Error Resume Next
Set fldTest = rsTest.Fields(strFieldName)
On Error GoTo 0
RsFieldExists = Not fldTest Is Nothing
End Function

Robin Hammond
www.enhanceddatasystems.com

"Developer of the Caribbean"
wrote in message
...
Robin, thank you very much for your answer. I was able to amend an
existing
field (essentially, I threw in a field (with the right data type) from the
database that I did not need, and then put new data in it after the
recordset
was opened and disconnected.) That should work for my current
application.

I'm curious if there is a more direct way of doing it. I've found that
some
SQL applications use a "Cast" command to assign a field a specific data
type,
but I have been unable to get such a command to work with ADO.

"Robin Hammond" wrote:

I haven't tested this in any detail, but you can kludge it with something
like this in your SQL select command:

SELECT 0 as MyIntegerField, Field1, Field2 FROM View1

ADO assigns an integer type to the field. I suspect if you tried

SELECT "This is a string", Field1 etc

that ADO might assign a variant type of some form.

Give it a try.

Alternatively,

Can you amend the view that you are selecting from to contain a field
with
null values in the type that you want?

Robin Hammond
www.enhanceddatasystems.com

"Developer of the Caribbean"
wrote in message
...
As part of an ADO Recordset, I would like to create an empty Field. I
would
like to open the RecordSet (using an SQL statement) with data from an
SQL
Server database, disconnect the Recordset, and then use the empty Field
to
enter additional data. I can do all of this, but . . .

My question is - How do I assign this empty field a data type?

(I do not believe I can use the ".Fields.Append" method, since any
existing
fields would be wiped out when I open the recordset with my SQL
command.
So,
I need to create this empty field and assign it a datatype in the same
SQL
statement that I use to access data from the SQL Server database. I
think.
Sigh.)

Thanks much!