Casting in an Excel ADO SQL Command

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"
<DeveloperoftheCaribbean@discussions.microsoft.com> wrote in message
news:5AE6BB23-3AB3-48E7-8E64-349305D70D26@microsoft.com...
> 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"
> <DeveloperoftheCaribbean@discussions.microsoft.com> wrote in message
> news:5AE6BB23-3AB3-48E7-8E64-349305D70D26@microsoft.com...
> > 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"
<DeveloperoftheCaribbean@discussions.microsoft.com> wrote in message
news:F53BAF5B-F5BC-45C7-B771-215D4D1E1719@microsoft.com...
> 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"
>> <DeveloperoftheCaribbean@discussions.microsoft.com> wrote in message
>> news:5AE6BB23-3AB3-48E7-8E64-349305D70D26@microsoft.com...
>> > 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"
> <DeveloperoftheCaribbean@discussions.microsoft.com> wrote in message
> news:F53BAF5B-F5BC-45C7-B771-215D4D1E1719@microsoft.com...
> > 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"
> >> <DeveloperoftheCaribbean@discussions.microsoft.com> wrote in message
> >> news:5AE6BB23-3AB3-48E7-8E64-349305D70D26@microsoft.com...
> >> > 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!
> >>
> >>
> >>
>
>
>