Error parsing XML SQL Command

Hi,
I'm getting some strange behavior when calling a stored proc...
It's an invoice page, so it has a list of assets, description, and $
total
You can modify this information and click on Update to update the
records with entered information. This works fine if I have one asset
in the invoice. But if I have more than one asset I get the following
error...
ParseXMLCommand->ReportEngine.Retrieval.ParseXMLCommand() [v
1.1.3]->ReportEngine.Retrieval.ExecuteQuery() [v 1.1.3] error
'80040067'
Error parsing XML SQL Command (Only one top level element is allowed in
an XML document. ).
/common/jax/applications/meter_invoices_client2.asp, line 126
Below is the code from meter_invoices_client2.asp which takes care of
this SQL operation. The commented lines are the existing query, which
is now being moved to a stored proc. with the code below. The embedded
SQL works fine under any circumstance. I don't think its a problem
with the stored procedure as it doesnt even get there yet.
'BEConn.Execute "Update InvoiceAsset SET SortTotal='" &
replace(Request.Form("SortTotal" & AssetID),"'","''") & "', " & _
' "SortOrderID='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "', " & _
' "Asset_location='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "', " & _
' "SubTotalDesc='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") & "',
" & _
' "RecordChanged='" & trim(Request.Form("Perm")) & "' " & _
' "WHERE asset_cd='" & AssetID & "'" & _
' "AND invoicenumber ='" & invoicenumber & "';"
XML_SP5 = XML_SP5 & "<sp name='p_update_sortOrders' db-code='" &
dbId & "' loc-code='1'>"
XML_SP5 = XML_SP5 & "<param name='@.asset_total' datatype='string'
index='1' value='" & replace(Request.Form("SortTotal" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_orderId' datatype='string'
index='2' value='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_location' datatype='string'
index='3' value='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_subtotal' datatype='string'
index='4' value='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") &
"'/>"
XML_SP5 = XML_SP5 & "<param name='@.perm' datatype='string' index='5'
value='" & trim(Request.Form("Perm")) & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.assetID' datatype='string'
index='6' value='" & AssetID & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.invoiceNo' datatype='string'
index='7' value='" & invoicenumber & "'/>"
XML_SP5 = XML_SP5 & "</sp>"
Set oXMLRetrieve = Server.Createobject("ReportEngine.Retrieval")
Call oXMLRetrieve.ExecuteQuery(reRecordSet,XML_SP5)I think posting on here just helps me out...lol
I got it to work finally...if anyone is interested...the error was
occurring because it was looping through all the fields on the form to
check if it is the one to be updated...so everytime through the loop it
was invoking the query XML_SP5 but XML_SP5 was continually being
appended do so it would look like this on the 2nd iteration...
<sp name='p_update_sortOrders' db-code='2' loc-code='1'><param
name='@.asset_total' datatype='string' index='1' value='1029'/><param
name='@.asset_orderId' datatype='string' index='2' value='2'/><param
name='@.asset_location' datatype='string' index='3' value='WEXFORD, PA
900-18999'/><param name='@.asset_subtotal' datatype='string' index='4'
value=''/><param name='@.perm' datatype='string' index='5'
value='N'/><param name='@.assetID' datatype='string' index='6'
value='663018'/><param name='@.invoiceNo' datatype='string' index='7'
value='3459956'/></sp><sp name='p_update_sortOrders' db-code='2'
loc-code='1'><param name='@.asset_total' datatype='string' index='1'
value='4016'/><param name='@.asset_orderId' datatype='string' index='2'
value='43'/><param name='@.asset_location' datatype='string' index='3'
value='COLUMBIA, MD 900-19002'/><param name='@.asset_subtotal'
datatype='string' index='4' value='SPEED CODE 4016'/><param
name='@.perm' datatype='string' index='5' value='N'/><param
name='@.assetID' datatype='string' index='6' value='663021'/><param
name='@.invoiceNo' datatype='string' index='7' value='3459956'/></sp>
which makes it 2 top level elements (sp) and hence throws the error
Cheers!

Error parsing XML SQL Command

Hi,
I'm getting some strange behavior when calling a stored proc...
It's an invoice page, so it has a list of assets, description, and $
total
You can modify this information and click on Update to update the
records with entered information. This works fine if I have one asset
in the invoice. But if I have more than one asset I get the following
error...
ParseXMLCommand->ReportEngine.Retrieval.ParseXMLCommand() [v
1.1.3]->ReportEngine.Retrieval.ExecuteQuery() [v 1.1.3] error
'80040067'
Error parsing XML SQL Command (Only one top level element is allowed in
an XML document. ).
/common/jax/applications/meter_invoices_client2.asp, line 126
Below is the code from meter_invoices_client2.asp which takes care of
this SQL operation. The commented lines are the existing query, which
is now being moved to a stored proc. with the code below. The embedded
SQL works fine under any circumstance. I don't think its a problem
with the stored procedure as it doesnt even get there yet.
'BEConn.Execute "Update InvoiceAsset SET SortTotal='" &
replace(Request.Form("SortTotal" & AssetID),"'","''") & "', " & _
'"SortOrderID='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "', " & _
'"Asset_location='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "', " & _
'"SubTotalDesc='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") & "',
" & _
'"RecordChanged='" & trim(Request.Form("Perm")) & "' " & _
'"WHERE asset_cd='" & AssetID & "'" & _
'"AND invoicenumber ='" & invoicenumber & "';"
XML_SP5 = XML_SP5 & "<sp name='p_update_sortOrders' db-code='" &
dbId & "' loc-code='1'>"
XML_SP5 = XML_SP5 &"<param name='@.asset_total' datatype='string'
index='1' value='" & replace(Request.Form("SortTotal" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_orderId' datatype='string'
index='2' value='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_location' datatype='string'
index='3' value='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_subtotal' datatype='string'
index='4' value='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") &
"'/>"
XML_SP5 = XML_SP5 &"<param name='@.perm' datatype='string' index='5'
value='" & trim(Request.Form("Perm")) & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.assetID' datatype='string'
index='6' value='" & AssetID & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.invoiceNo' datatype='string'
index='7' value='" & invoicenumber & "'/>"
XML_SP5 = XML_SP5 & "</sp>"
Set oXMLRetrieve = Server.Createobject("ReportEngine.Retrieval")
Call oXMLRetrieve.ExecuteQuery(reRecordSet,XML_SP5)
I think posting on here just helps me out...lol
I got it to work finally...if anyone is interested...the error was
occurring because it was looping through all the fields on the form to
check if it is the one to be updated...so everytime through the loop it
was invoking the query XML_SP5 but XML_SP5 was continually being
appended do so it would look like this on the 2nd iteration...
<sp name='p_update_sortOrders' db-code='2' loc-code='1'><param
name='@.asset_total' datatype='string' index='1' value='1029'/><param
name='@.asset_orderId' datatype='string' index='2' value='2'/><param
name='@.asset_location' datatype='string' index='3' value='WEXFORD, PA
900-18999'/><param name='@.asset_subtotal' datatype='string' index='4'
value=''/><param name='@.perm' datatype='string' index='5'
value='N'/><param name='@.assetID' datatype='string' index='6'
value='663018'/><param name='@.invoiceNo' datatype='string' index='7'
value='3459956'/></sp><sp name='p_update_sortOrders' db-code='2'
loc-code='1'><param name='@.asset_total' datatype='string' index='1'
value='4016'/><param name='@.asset_orderId' datatype='string' index='2'
value='43'/><param name='@.asset_location' datatype='string' index='3'
value='COLUMBIA, MD 900-19002'/><param name='@.asset_subtotal'
datatype='string' index='4' value='SPEED CODE 4016'/><param
name='@.perm' datatype='string' index='5' value='N'/><param
name='@.assetID' datatype='string' index='6' value='663021'/><param
name='@.invoiceNo' datatype='string' index='7' value='3459956'/></sp>
which makes it 2 top level elements (sp) and hence throws the error
Cheers!
sql

error parsing the query

hi all

I try to run the query below it throw me an exception, error parsing , is there anywat to resolve it?

SELECT TYPE2.list_price as 'TYPE2'
FROM TBL_MST_PRICE TYPE2
WHERE
price_grp_code = (SELECT price_grp_code FROM TBL_MST_PRICE_GRP WHERE prd_code = '' AND cust_code = '')

Sub-selects (nested select statements) are not supported by SQL CE. You will have to rephrase your query to use a join or use 2 select statements.

Error Parsing Name String

I''m reposting this problem because I may not have explained it well enough
before. in example 1 below, i've successfully extracted the last name from a
full name field, although I've hard-coded the fullname or userName.
Example 2 gives the listed error when i run it on my table with data in the
userName field. What I can't figure is Example 2 will work if I don't
include the "-1" part, buh returns the lastname and it's trailing comma.
What am I doing wrong? My data looks like the DATA section below.
--EXAMPLE 1: works , hard-coded name
SELECT 'Miles, Michael L' as userName,
LEFT('Miles, Michael L',CHARINDEX(',','Miles, Michael L')-1) as userLast
-- EXAMPLE 2
-- Error Msg 536, Level 16, State 3, Line 1
-- Invalid length parameter passed to the substring function.
SELECT name as userName,
LEFT(name,CHARINDEX(',',name)-1) as userLast
FROM sc_employee
DATA Table***********************************
****************
USERNAME
Smith, John R
Walker, L.T.
Adams, Charles
Weathersby, D.J.On Sun, 2 Apr 2006 17:56:42 -0500, scott wrote:
(snip)
>-- EXAMPLE 2
>-- Error Msg 536, Level 16, State 3, Line 1
>-- Invalid length parameter passed to the substring function.
>SELECT name as userName,
>LEFT(name,CHARINDEX(',',name)-1) as userLast
>FROM sc_employee
Hi Scott,
You probably have at least one row where there is no comma in the Name
column. To find the offenders, use
SELECT name
FROM sc_employee
WHERE name NOT LIKE '%,%'
And to exclude the offending rows from your query, change it to
SELECT name as userName,
LEFT(name,CHARINDEX(',',name)-1) as userLast
FROM sc_employee
WHERE name LIKE '%,%'
Hugo Kornelis, SQL Server MVP|||I found a null record. thanks.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:6nm0321acbpk9am57qii9nk8p23dinp6a3@.
4ax.com...
> On Sun, 2 Apr 2006 17:56:42 -0500, scott wrote:
> (snip)
> Hi Scott,
> You probably have at least one row where there is no comma in the Name
> column. To find the offenders, use
> SELECT name
> FROM sc_employee
> WHERE name NOT LIKE '%,%'
> And to exclude the offending rows from your query, change it to
> SELECT name as userName,
> LEFT(name,CHARINDEX(',',name)-1) as userLast
> FROM sc_employee
> WHERE name LIKE '%,%'
> --
> Hugo Kornelis, SQL Server MVP