Extremely Urgent : & character not accepted in parameter in stored procedure

5 posts, 0 answers
  1. Shiva
    Shiva avatar
    61 posts
    Member since:
    Oct 2010

    Posted 15 Dec 2010 Link to this post

    Dear Telerik Team,

    Please consider this as an urgent request so please find some time to address this issue.

    We are trying to execute a stored procedure using telerik open access. The stored procedure takes a string parameter. That parameter contains & character which is causing the system to throw following exception:

    Telerik.OpenAccess.RT.sql.SQLException: XML parsing: line 1, character 848, illegal name character
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
       at OpenAccessRuntime.Relational.RelationalQueryResult.createFetchResult(Connection conParam, SqlDriver sqlDriver, Boolean scrollableParam, FetchSpec fetchSpec, RelationalCompiledQuery relationalCompiledQuery, RelationalStorageManager storageManager, Object[] paramVals, Int32 fetchSize, Int32 maxRows)

    Here is the code snippet:

    object[] arrParam = new object[1] { xml };
                    SqlParameter parameterCategoryID = new SqlParameter("CategoryDataElementXML", OpenAccessType.Varchar);
      
                    List<SqlParameter> sqlParameters = new List<SqlParameter>();
                    sqlParameters.Add(parameterCategoryID);
      
                    var entityDataSource = new MDSDataContext();
                    var queryResult = entityDataSource.ExecuteStoredProcedure<object[]>("'SaveCategoryDataElement' ?", sqlParameters, arrParam);

    where
    xml    : Input string in xml format

    To add more information to it, we tried replacing & with &amp; but no help. We still encountered that error.

    Can somebody please assist us on this issue?

    Best Regards,
    Shiva
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 15 Dec 2010 Link to this post

    Hello Shiva,

    I'm sorry, but I cannot reproduce this  exception. Would it be possible to send us the definition of the SaveCategoryDataElement procedure? I'm especially interested in the parameter declaration and the column types that are involved. I guess the exception does not come from the parameter passing in as varchar, but from the conversion in the stored proc to an Xml column.

    Regards,
    Thomas
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  3. DevCraft banner
  4. Shiva
    Shiva avatar
    61 posts
    Member since:
    Oct 2010

    Posted 15 Dec 2010 Link to this post

    Dear Thomas,

    Thank you for your response. Below is the code of the procedure you have asked for:

    CREATE PROCEDURE [dbo].[SaveCategoryDataElement]
    (   
        @CategoryDataElementXML XML
    )
    AS
    BEGIN
      
    SET NOCOUNT ON; 
      
    BEGIN TRY
            BEGIN TRANSACTION           
            BEGIN
      
                --Preparing Handler document for reading XML
                DECLARE @DocDataElement INT, @DocSecurity INT
                  
                EXEC sp_xml_preparedocument @DocDataElement OUTPUT,@CategoryDataElementXML 
                  
                CREATE  Table #tmpDataElementTable(
                                            DataElementID       INT,
                                            DataElementName     VARCHAR(50),
                                            DataElementIDNew    INT
                                        )
                CREATE  Table #tmpCategoryTable(
                                            DataElementID       INT,                                            
                                            CategoryID          INT 
                                        )
                                                                      
                INSERT INTO #tmpDataElementTable
                SELECT                      DataElementID,
                                            DataElementName,
                                            DataElementID
                  
                FROM OPENXML (@DocDataElement,'/root/DataElement',2)
                WITH(
                                            DataElementID       INT,                                            
                                            DataElementName     VARCHAR(50)                                     
                    )
                  
                  
                INSERT INTO #tmpCategoryTable
                SELECT                      DataElementID,
                                            CategoryID
                  
                FROM OPENXML (@DocDataElement,'/root/DataElement/Category',2)
                WITH(
                                            DataElementID       INT,
                                            CategoryID          INT                                         
                    )
                  
                INSERT INTO MDSDataElementList (DataElementName, IsActive)
                SELECT DataElementName, 1 FROM #tmpDataElementTable WHERE DataElementID < 0
                  
                UPDATE #tmpDataElementTable SET DataElementIDNew=MDSDataElementList.DataElementID
                FROM MDSDataElementList
                WHERE MDSDataElementList.DataElementName=#tmpDataElementTable.DataElementName AND #tmpDataElementTable.DataElementID < 0
                  
                UPDATE MDSDataElementList SET DataElementName = #tmpDataElementTable.DataElementName
                FROM #tmpDataElementTable WHERE MDSDataElementList.DataElementID = #tmpDataElementTable.DataElementID AND #tmpDataElementTable.DataElementID > 0
                  
                UPDATE #tmpCategoryTable SET DataElementID = #tmpDataElementTable.DataElementIDNew
                FROM #tmpDataElementTable
                WHERE #tmpCategoryTable.DataElementID = #tmpDataElementTable.DataElementID
                  
                DELETE FROM MDSCategoryDataElements WHERE DataElementID IN (SELECT DataElementID FROM #tmpCategoryTable)
                  
                INSERT INTO MDSCategoryDataElements (DataElementID, CategoryID, IsActive)
                SELECT DataElementID, CategoryID, 1 FROM #tmpCategoryTable
                  
                DROP TABLE #tmpDataElementTable
                DROP TABLE #tmpCategoryTable
                  
            END
            COMMIT
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0          
            ROLLBACK
            DECLARE @ErrMsg NVARCHAR(4000), @ErrSeverity INT
            SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
            RAISERROR(@ErrMsg, @ErrSeverity, 1)
    END CATCH
                                                          
    END

    The parameter passed from the C# code in the stored procedure is varchar but received in stored procedure as XML. Please share your comments.

    Warm Regards,
    Shiva
  5. Shiva
    Shiva avatar
    61 posts
    Member since:
    Oct 2010

    Posted 16 Dec 2010 Link to this post

    Hi Thomas,

    May I please have your comments on this one. This is an urgent issue for us.

    Regards,
    Shiva
  6. Ady
    Admin
    Ady avatar
    589 posts

    Posted 20 Dec 2010 Link to this post

    Hello Shiva,

     I was not able to reproduce this error using a similar stored procedure with an XML parameter and called using a varchar parameter (with an & within the XML) . Can you execute the stored procedure using the same parameter value via an external tool like SQL Server Management Studio?
    Can you provide me with the sample value that reproduces this error?

    All the best,
    Ady
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
Back to Top
DevCraft banner