Fieldname collection w/ SQL Server 1

[travisbrown]

Is it possible to return a fieldname collection from a SQL Server rs like one can with Access?

I thought I'd read it before hear, but I must not be searching for the right thing. It was different than how one did it with Access.

 

[johnwm]

Try thread183-807871

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[travisbrown]

Thanks. That will be useful for other things, but is there a way to return the field names in a joined query? I have a view that I'm using for a dynamic pivot table so the column names are aliased and variable.

 

[onpnt]

If I understand correct you just want to loop through the recordset returned and write the column names out.

You can do that by going through to the count of the fields and writing them out by index.

Check ADO collections object and properties

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[travisbrown]

Argh. Power went out halfway through posting my reply.

I assume you are talking about this:

Count Returns the number of items in the fields collection. Starts at zero. 
Example:

countfields = rs.Fields.Count 
 
Item(named_item/number) Returns a specified item in the fields collection. 
Example:

itemfields = rs.Fields.Item(1)
or
itemfields = rs.Fields.Item("Name")

I don't see what collection I should be calling by index. Doesn't the above call the field value, not the field name? Maybe I'm just daft.

FieldCount = rs.Fields.Count
TestFieldName = rs.Fields.Item(0) <-- throws error "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

with Access I'd do something like this:

For each field in rs.fields
 Response.write "<td>" & field.name & "</td>"
Next

By the bye onpnt, what happened to that post about the dynamic dropdown and skipping the last three days of the month that you and I were replying to? Mysteriously diappeared.

 

[onpnt]

You should be able to do the same for next loop on the fields collections on any database. It's the record set you're worried about at that point. I just tested the following and it worked fine on my local sql server 2005 install. give it a try on your db

<%
Dim conStr
conStr = "Driver={SQL Server};Server=xxxxx; " & _
        "Trusted_Connection=no;" & _
        "Database=master;Uid=xxxxx;Pwd=xxxxx;"
Dim con
Set con = Server.CreateObject("ADODB.Connection")

con.Open(conStr)

Set rs = con.Execute("SELECT * FROM sysservers")

For each f in rs.Fields
	Response.Write(f.Name & "<br>")
Next

con.Close
Set con = Nothing
Set rs = Nothing
%>

btw..that is of course if you are a sysadmin on the server. try some other select if you are not.


That thread must have been removed. I'm assuming I was RF'd sense that member is still a member. No worries.

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[onpnt]

Sorry, didn't really answer your question about my last post after rereading it. Apologies for it being short. Busy lately.

anyhow, this is was I was going at

Do While x <= rs.Fields.Count - 1
	Response.Write rs.Fields(x).Name & "<br />"
x = x + 1
Loop

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[travisbrown]

How very odd. I don't know what was causing it to fail when I first tried. I thought it was a db difference.

Is there a way to pass rs.Fields names into a variable without looping and manually creating an array? I'm passing an array of the resultset out of a function using GetRows. I'd like to do the same with the field names.

 

[travisbrown]

nevermind. figured it out. Had a name conflict somewhere else.

arrFields = ARRAY(rs.Fields)

For i = 0 TO UBOUND(arrFields)
 Response.Write arrFields(i)
Next

 

[travisbrown]

Okay. Back to the same problem. Fails when I return a dynamic crosstab rs.

Here's the error

Response object error 'ASP 0185 : 8002000e' 

Missing Default Property 

/cops/Admin/test.asp, line 0 

A default property was not found for the object.

The line error isn't accurate. It's not zero. The response write of the fieldname array errs out. Works on simple select statements, not the rs from the crosstab.

Dim objConn
Dim rs

Set objConn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
objConn.Open MM_connCOPS_STRING

rs.Open "exec dbo.up_CrossTab 'SELECT profile_id, profile_name, module_id, module_name FROM PivotProfilesModules', 'module_name','COUNT(module_id)[]','profile_id'", MM_connCOPS_STRING
	
	arrProfileModules = rs.getrows()
	arrProfileModulesFields = ARRAY(rs.Fields)
	
IF isArray(arrProfileModules) THEN 
	Response.write "<table>"
	Response.write "<tr>"
For i = 0 TO UBOUND(arrProfileModulesFields)
	Response.write "<th>"
	Response.write arrProfileModulesFields(i)
	Response.write "</th>"
NEXT
	Response.write "<tr>"

For i = 0 TO UBOUND(arrProfileModules,2)
	Response.write "<tr>"
	For c = 0 TO UBOUND(arrProfileModulesFields)
		Response.write "<td>"
		Response.write arrProfileModules(c,i)
		Response.write "</td>"
	NEXT
	Response.write "</tr>"
NEXT
	Response.write "</table>"
END IF

RS looks like this out of SQL Query Analyzer

profile_id      Admin Tab   CL - Line Button CL - Sub/Gen Button Contacts Tab Control Centres Tab CRC Tab     Create List Tab myCops Tab  Reports Tab The Pen     
--------------- ----------- ---------------- ------------------- ------------ ------------------- ----------- --------------- ----------- ----------- ----------- 
ABENNETT        0           0                0                   1            0                   0           0               0           1           1
ABROWN          0           0                0                   1            0                   0           0               0           1           1
ABRUDER         0           0                0                   1            1                   0           0               0           0           1
ABURROWS        0           1                0                   1            0                   0           1               0           1           1
ACUCCION        0           0                0                   1            0                   0           0               0           1           1
ADMINISTRATOR   1           1                1                   1            1                   1           1               1           1           1
ADUBOWIT        0           0                0                   1            0                   0           0               0           1           1
AGAMAGE         0           0                0                   1            0                   0           0               0           1           1
AGARNEAU        0           1                0                   1            0                   0           1               0           1           1
AHARRIS         0           0                0                   1            0                   0           0               0           1           1
AHENRY          0           0                0                   1            0                   0           0               0           1           1
AHERRICK        0           0                0                   1            1                   0           0               0           1           1
AHOLT           0           0                0                   1            0                   0           0               0           1           1
akellett        0           0                0                   1            0                   0           0               0           0           0