Archive for the ‘ADO.NET’ Category

XPath Query On XML File Created From DataSet Returns Zero Records

April 20, 2010

Scenario

I had an XML file called LocationExtract.xml which I had created using DataSet.WriteXMLSchema. I wanted to modify the content of some of the nodes, so I used the SelectNodes method on the DocumentElement node of the XML file to select those nodes, but SelectNodes always returned zero records.

Solution

I had to to use the .NET XMLNamespaceManager object to add the namespace of the XML document to the collection of namespaces in the scope of my local XPath query. An XPath query needs to know the namespaces which it is operating on because it is by namespaces that XPath identifies XML documents. This MSDN document XML Namespaces and How They Affect XPath and XSLT explains this in detail.

Finding The XMLNamespace Of Your DataSet

If you inspect an XML file created from a strongly-typed DataSet you will find a line in it similar to the following: xmlns:http://tempuri.org/LocationExtractDataSet.xsd. That means that the XMLNamespace of the DataSet (.xsd file) named LocationExtractDataSet is http://tempuri.org/LocationExtractDataSet.xsd

Here’s The Code which allows an XPath Query to run against the XML file created from that DataSet (crucial bits in bold)

Dim reader As XmlTextReader = New XmlTextReader(Server.MapPath("LocationExtract.xsd"))
Dim doc As XmlDocument = New XmlDocument()
doc.Load(reader)
Dim topNode As XmlElement = doc.DocumentElement

'Set XPath Namespace
Dim topNode As XmlElement = doc.DocumentElement
Dim nsmgr As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable)
nsmgr.AddNamespace("loc", topNode.NamespaceURI)

Dim loc As XmlNodeList = topNode.SelectNodes("//ext:Location", nsmgr)

For Each locNode As XmlNode In loc
locNode.InnerText.Replace("Sugar", "Cinnamon")
Next

You will notice that I create an XMLNamespaceManager and add the namespace of my XML File to it with an associated prefix. The prefix is “ext” and the namespace is http://tempuri.org/LocationExtractDataSet.xsd. The weird thing is that the namespace is NOT available in doc.NamespaceURI. You have to get it from a Node in the document such as the DocumentElement

Having done this my XPath Query topNode.SelectNodes(“//ext:Location”, nsmgr) returns all the Location Nodes in the document LocationExtract.xml because it is in the namespace associated with the prefix “ext” . I then replace “Sugar” with “Cinnamon” in the manner approved by Jerry Seinfeld.

In short, if your XPath Query is returning zero records, you probably need to load the namespace of your XML file into an XMLNamespaceManager. Twenty Billion XPath Queries can’t be wrong.

Advertisements

Cannot Drag Additional Table From Server Explorer Into Existing DataSet (.xsd) Failed to add TableAdapter.

December 8, 2009

Just a short one here,…

I have a very complicated DataSet (.xsd) file and our schema is being updated. I needed to drag a couple of new tables from the ServerExplorer into the DataSet Designer…but Visual Studio wouldn’t let me do it. I just got the ‘ghostbusters’ icon and the table would not drop.

Trying to work around the issue, I did right-click on Designer Add->TableAdapter and configured the Adapter but when clicking ‘Finish’ I got the error ‘Failed to add TableAdapter’ along with the SQL Named Pipes Provider error 40.

For half an hour I toyed with hand-coding the necessary table and all associated ADO.NET code directly into the DataSet designer file. Fortunately this madness subsided.

Then I created a new DataSet and copied/pasted the old DataSet from the Designer into the new one. This worked. No Named Pipes errors AND I was able to drag new tables into the Designer for the new DataSet. But this was all just a Journey Of Pain. I should be able to simply edit the existing DataSet. I threw a brick through the monitor and went home for a relaxing session of concreting the hamster (also here) to let my right-brain work on the problem unencumbered by reason.

Well today’s a new day and since it was my last straw, what I did was rebind the Project to Visual Source Safe. I had unbound the Project from Soure Control a little earlier for weird reasons I will keep to myself. Once I rebound the Project to Source Control via File -> Source Control -> Change Source Control I was able to add the new table to the Dataset. This was so stupidly illogical that it did not surprise me in the least.

Once day I will be able to explain why this makes any difference but today I can’t. But if you’re in a similar position why not try rebinding the Source Control? It worked for me and now I’m a multi-millionaire with a full head of hair and a tremendously loyal concrete hamster. Buy one today.

Random Thought 20-Jan-2011
Maybe rebinding the Project to Source Control make the .xsd files writable. Perhaps they had become unwritable when I unbound?

Incorrect Syntax Near “.” during DataAdapter.Fill

September 24, 2009

Warning: Some of the links in this post may distress Cat Fanciers.

What Have I Done To Deserve This ?
Incorrect Syntax near “.” on a DataAdapter Fill means the SQL for your Select Command in your SQLCommand object has a syntax error in it…same as what I did. The bit in the double quotes after ‘Near’ shows what caused the SQL Parser to choke on its own giblets. Hey, look on the bright side. Mine happened while I was doing a practical test during a job interview.

Due to its initial obscurity and brevity, this error caused stage one panic (Brain leaking out of nostil, inflamed Hello Kitty, gargantuan screaming noise from reptilian brain stem), but a few Googles put me right.

How To Fix It

Well, correct the syntax error in the SQL obviously.

Mine came about through a bad concatenation. Like so:

' Create a SqlConnection to the Northwind database.
Using connection As SqlConnection = New SqlConnection( _
connectionString)

' Create a SqlDataAdapter for the Suppliers table.
Dim suppliersAdapter As SqlDataAdapter = _
New SqlDataAdapter()

' A table mapping names the DataTable.
suppliersAdapter.TableMappings.Add("Table", "Suppliers")

' Open the connection.
connection.Open()
Console.WriteLine("The SqlConnection is open.")

'Bad concatenation here
Dim commandString As String = _
"SELECT SupplierID, CompanyName FROM dbo.Suppliers" & _
"WHERE supplierID > 100"

' Create a SqlCommand to retrieve Suppliers data.
Dim suppliersCommand As SqlCommand = New SqlCommand( _
commandString, connection)
suppliersCommand.CommandType = CommandType.Text

' Set the SqlDataAdapter's SelectCommand.
suppliersAdapter.SelectCommand = suppliersCommand

' Fill the DataSet.
Dim dataSet As DataSet = New DataSet("Suppliers")
'Incorrect Syntax Crash Here
suppliersAdapter.Fill(dataSet)

You Bad Concatenation, You

As you can see above, there is no space between my WHERE clause and the SELECT clause.

The resulting command was thus:
“SELECT SupplierID, CompanyName FROM dbo.SuppliersWHERE supplierID > 100”

There is no table called dbo.SuppliersWHERE and no where clause in the query at all.
Fixing it entailed simply inserting a space at the end of the SELECT clause.

Dim commandString As String = _
“SELECT SupplierID, CompanyName FROM dbo.Suppliers “ & _
“WHERE supplierID > 100”

OracleDataProvider: ORA-01861 Literal Does Not Match Format String

March 19, 2009

Got the above error calling a Stored Procedure via an OracleCommand.ExecuteNonQuery.

Not being an Oracle savant, I Googled the error and found it can come about when an Oracle TO_DATE function call cannot match the string literal with the supplied date picture (Excuse my atavistic lapse into COBOL jargon) in the Format string.

For example, TO_DATE(’23/02/2009′, ‘DD-MON-YY’) produces ORA-01861 because the Format string specifies three character month and two character years, but received a two character month and a four character year.

In my case. however, the crash occurred on line one of the Stored Proc where a DATE parameter was listed in the contract viz:

CREATE OR REPLACE pCalculateStuff(
inDodgyDate IN Date,
inStudentNumber IN VARCHAR2,
inOrg_Number IN VARCHAR2)

and yet Oracle was still complaining with the above error.

Moreover, the parameters were explicitly named in the OracleParamter array I was using to populate the OracleCommand object which called the Stored Proc. I was expecting the parameters to resolve by name but they didn’t.

What I found was that the order of the parameters in the OracleParameter array must match the order of the parameters in the Stored Procedure, even though the OracleParameters are all explictly named in the .NET code

I had the following:

oraParams(0) = New OracleParameter("inStudentNumber", OracleDbType.Varchar2, ParameterDirection.Input)
oraParams(0).Value = studentNum.ToString()
oraParams(1) = New OracleParameter("inOrgTradeAutoNum", OracleDbType.Varchar2, ParameterDirection.Input)
oraParams(1).Value = orgTradeAutoNum.ToString()
oraParams(2) = New OracleParameter("inDateProcessing", OracleDbType.Date, ParameterDirection.Input)
oraParams(2).Value = termStartDate

oraCmd.CommandText = strStoredProcName
oraCmd.CommandType = CommandType.StoredProcedure

If (params.Length > 0) Then
For intParamCount = 0 To params.Length - 1
oraCmd.Parameters.Add(params(intParamCount))
Next
End If

' Code crashes here, giving ORA-01861
oraCmd.ExecuteNonQuery()

So, when I called ExecuteNonQuery on the StudentNumber was being bound to inDodgyDate, because it occupied parameter index 0 in the OracleParameters array so that ExecuteNonQuery threw ORA-01861 because ‘211946’ failed to parse into a Date

The fix was merely to re-order the position of the params in the OracleParameters array so that it matched the order in the Stored Procedure viz:

-- Make inDodgyDate the first parameter in the array
oraParams(0) = New OracleParameter("inDodgyDate", OracleDbType.Date, ParameterDirection.Input)
oraParams(0).Value = termStartDate

Lesson Learnt
The name of the OracleParameter is irrelevant when parameter binding. The order in which they are added to the OracleCommand is the only thing that matters.

Addendum 27-March-2009
Looks like I could have save myself a lot of trouble by setting

oraCmd.BindByName = True