February 15, 2002 at 10:09 am
There were a very interesting article on http://www.sqlservercentral.com, about using FOR XML for drop-down boxes, called "ASP - Reaping the benefits of SQL 2000's FOR XML clause".
Unfortunately, I cannot get the example to work.
I have removed the comment in runSqlGetXML
objCmd.CommandText="<root xmlns:sql " & _
"='urn:schemas-microsoft-com:xml-sql '>" & _
"<sql:query>" & _
strSQL & _ 'insert sql command into xml packet
"</sql:query></root>"
but I'm still getting an error on the line
objcmd.Properties("Output Stream").Value=xml
What am I doing wrong?
Henrik Staun Poulsen
Stovi Software
Denmark
February 19, 2002 at 10:07 pm
Sorry been sick with flu for last 5 days.
Make sure you have the latest MDAC.
ADO2.6 may also run with 2.5 not sure.
What error are you getting?
February 20, 2002 at 7:44 am
Leon,
I have ADO2.6.
On the line
objcmd.Properties("Output Stream").Value=xml
I get
Item cannot be found in the collection corresponding to the requested name or ordinal.
Kind regards
Henrik
February 20, 2002 at 10:49 pm
Don't know why you would get that error. Just to verify please open an Ado connection and then print the version:
set dbConn=server.createobject("adodb.connection")
dbConn.Open "file Name=c:\code\cnLanWeb.udl" 'need to put your connection string here
response.write dbConn.Version
February 21, 2002 at 1:58 am
Here is what I did:
sub GetAdoVersion()
Dim o
On Error Resume Next
Set o = CreateObject("ADODB.Connection")
If Err.Number = 0 then
out o.version
Else
out "ADO is not installed"
End If
End sub
and that is why I think I have 2.6 installed
Could this problem be caused by running an international version of IIS or SQL server?
It is probably something really simple, but where/what?
February 21, 2002 at 4:54 am
To make sure visit http://www.microsoft.com/data/ and download the component checker tool.
Andy
February 21, 2002 at 7:56 am
I'm now sure that I've got MDAC 2.7 installed, and SQL2K Sp2. I just installed both of them. Here is the complete list of properties that objCmd has. There is nothing that comes close to "output stream". What is wrong?
Preserve on Abort=False
Blocking Storage Objects=True
Use Bookmarks=False
Skip Deleted Bookmarks=False
Bookmark Type=1
Fetch Backwards=False
Hold Rows=False
Scroll Backwards=False
Column Privileges=False
Command Time Out=30
Preserve on Commit=False
Delay Storage Object Updates=False
Immobile Rows=True
Literal Bookmarks=False
Literal Row Identity=True
Maximum Open Rows=0
Maximum Pending Rows=0
Maximum Rows=0
Notification Phases=31
Others' Inserts Visible=False
Others' Changes Visible=False
Own Inserts Visible=False
Own Changes Visible=False
Quick Restart=False
Reentrant Events=True
Remove Deleted Rows=False
Report Multiple Changes=False
Row Privileges=False
Row Threading Model=1
Objects Transacted=False
Updatability=0
Strong Row Identity=False
IAccessor=True
IColumnsInfo=True
IColumnsRowset=True
IConnectionPointContainer=False
IRowset=True
IRowsetChange=False
IRowsetIdentity=False
IRowsetInfo=True
IRowsetLocate=False
IRowsetResynch=False
IRowsetScroll=False
IRowsetUpdate=False
ISupportErrorInfo=True
ISequentialStream=False
Column Set Notification=3
Row Delete Notification=3
Row First Change Notification=3
Row Insert Notification=3
Row Resynchronization Notification=3
Rowset Release Notification=3
Rowset Fetch Position Change Notification=3
Row Undo Change Notification=3
Row Undo Delete Notification=3
Row Undo Insert Notification=3
Row Update Notification=3
Change Inserted Rows=True
Return Pending Inserts=False
IConvertType=True
Notification Granularity=1
IMultipleResults=False
Access Order=1
Bookmark Information=0
Unique Rows=False
Query Based Updates/Deletes/Inserts=False
Generate a Rowset that can be marshalled=False
Position on the last row after insert=False
IRowsetChangeExtInfo=False
ODBC Cursor Type=0
ODBC Concurrency Type=15
BLOB accessibility on Forward-Only cursor=False
Include SQL_FLOAT, SQL_DOUBLE, and SQL_REAL in QBU where clauses=False
Force SQL Server Firehose Mode cursor=False
Force no parameter rebinding when executing a command=False
Force no command preparation when executing a parameterized command=False
Force no command reexecution when failure to satisfy all required properties=False
Bookmarkable=False
February 21, 2002 at 5:11 pm
Did this in VB6 with a reference set to ADO 2.6. Got the same when I set a reference to ADO 2.7. Haven't looked at Leon's code yet..curious!
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim oProp As ADODB.Property
Set cn = New Connection
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=xzy"
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
For Each oProp In cmd.Properties
Debug.Print oProp.Name
Next
cn.Close
Set cn = Nothing
IAccessor
IColumnsInfo
IColumnsRowset
IConnectionPointContainer
IConvertType
IGetSession
IMultipleResults
IRow
IRowset
IRowsetChange
IRowsetIdentity
IRowsetInfo
IRowsetLocate
IRowsetRefresh
IRowsetResynch
IRowsetScroll
IRowsetUpdate
ISupportErrorInfo
ISequentialStream
Preserve on Abort
Access Order
Blocking Storage Objects
Bookmark Information
Use Bookmarks
Skip Deleted Bookmarks
Bookmark Type
Fetch Backwards
Hold Rows
Scroll Backwards
Change Inserted Rows
Column Privileges
Command Time Out
Preserve on Commit
Defer Column
Delay Storage Object Updates
Hidden Columns
Immobile Rows
Literal Bookmarks
Literal Row Identity
Lock Mode
Maximum Open Rows
Maximum Pending Rows
Maximum Rows
Notification Granularity
Notification Phases
Column Set Notification
Row Delete Notification
Row First Change Notification
Row Insert Notification
Row Resynchronization Notification
Rowset Release Notification
Rowset Fetch Position Change Notification
Row Undo Change Notification
Row Undo Delete Notification
Row Undo Insert Notification
Row Update Notification
Others' Inserts Visible
Others' Changes Visible
Own Inserts Visible
Own Changes Visible
Quick Restart
Reentrant Events
Remove Deleted Rows
Report Multiple Changes
Return Pending Inserts
Row Privileges
Row Threading Model
Server Cursor
Server Data on Insert
Strong Row Identity
Objects Transacted
Unique Rows
Updatability
Maximum BLOB Length
Fastload Options
Keep Nulls
Keep Identity
Cursor Auto Fetch
Defer Prepare
IRowsetFastLoad
Bookmarkable
Output stream
Output encoding
Mapping schema
xsl
Base path
Command type
xml root
ss stream flags
Content type
Andy
February 22, 2002 at 2:35 am
Andy,
<<cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=xzy">>
That is a different connection string from the one I use, and the one Leon has used. Once I got your connection string working, the rest was working in minutes.
So: IT WORKS!
Great. I still had to remove a comment on line 16, but that was easy, and shouldn't stop anyone except beginners like me.
But all this means I have to learn something about ADO connections. What's the best source?
Henrik
February 22, 2002 at 4:03 am
Both Wrox & Apress have some good material on ADO, or MSDN for free help. We probably have some related stuff here on the site (I have a couple on ADO). If you just want to learn about connection strings (they are rather cryptic at times) try this trick - create a new blank text file on your desktop, then rename to something like a.udl. Double click to open the UDL editor. Make your selections, use the test connection button to make sure, then click ok. Then rename a.udl back to a.txt. Double click, the second line is the connection string.
Andy
February 22, 2002 at 10:18 pm
Glad you got things working. Sorry about the comment problem. Thats what happens when you try to add comments as an after thought instead of doing it while you are coding.
Leon
February 23, 2002 at 1:44 am
Leon, Andy
<<create a new blank text file on your desktop, then rename to something like a.udl. >>
Nice trick, except that I didn't notice at first that it had to be a blank file.
Many thanks goes to both of you for all your help.
Henrik
February 23, 2002 at 5:38 am
June 21, 2017 at 12:59 am
This was removed by the editor as SPAM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply