Pass XML parameter to stored procedure

BCullenward · September 9th, 2008, 02:58 PM

I'm trying to pass an XML parameter to a stored procedure. I had it working using the command object, but since the recordcount and pagecount refuse to work with the command object I had to switch to the connection object.

How can I pass an XML parameter to my stored procedure? Here is what I was using for the command object:

Code:

sXMLDoc = oXmlDom.selectSingleNode("/").xml
oCmd.Parameters.Append oCmd.CreateParameter("", adVarchar, adParamInput, Len(sXMLDoc), sXMLDoc)

The above worked for me, however there can be several thousands of rows returned, and the rest of the code only displays 250 records on the screen at a time so the recordcount and pagecount are needed.

Your help is appreciated.

Thanks

Old Pedant · September 9th, 2008, 06:12 PM

So if that works for you, what's the point of your question???

If all you are trying to do is get the recordcount and pagecount, that has nothing at all to do with whether you used the value from XML or just used any old value.

It's all a matter of how you open the recordset. And you don't show that code.

*PROBABLY* what you need to do, after the code you show, is something like:

Code:

<%
...
Set RS = Server.CreateObject("ADODB.Recordset")
RS.PageSize = 10 ' or whatever size you want
RS.Open oCmd, ,3

recordCount = RS.recordCount
pageCount = RS.pageCount
...
%>

BCullenward · September 10th, 2008, 08:38 AM

I found where my issue was. I didn't have single quotes around the xml parameter when using the recordset to open.

Thanks.

Old Pedant · September 10th, 2008, 02:07 PM

How strange!??!!

YOu shouldn't need apostrophes around a value used with a command parameter, as in your code

oCmd.Parameters.Append oCmd.CreateParameter("", adVarchar, adParamInput, Len(sXMLDoc), sXMLDoc)

Are you saying you changed the way of opening the recordset??

Oh, well. Glad you got it working.