Data corruption issues with .Net and Oracle
I am in the process of learning VB.net and ASP.net to perform a conversion from Coldfusion. I have run into a rather puzzling issue with Oracle queries. When I run a simple Select statement in Coldfusion
SELECT DISTINCT(INCIDENT_ID) FROM INCIDENTSM1 WHERE CLOSE_TIME >= To_Date('06-Mar-2007','DD-MON-YYYY')
1500 Records are returned in a string format. The data appears correctly.
When I run the same query in vb.net the first 50 records appear correctly, then the remain 1450 records appear as special characters and asian text.
I have tried this via 2 seperate .Net 2.0 installations / machines and have updated all Oracle drivers. I have also tried using the
Oracle.DataAccess.Client library and the
System.Data.Oledb librabry and have received the same results.
Here is the current code that I am using:
Dim strSQL3 AsString = "SELECT DISTINCT(INCIDENT_ID) FROM INCIDENTSM1 WHERE CLOSE_TIME >= To_Date('06-Mar-2007','DD-MON-YYYY')"
Dim objConnection3 AsNew OleDbConnection(strConnectionPRGNSCP)
Dim objCommand3 AsNew OleDbDataAdapter(strSQL3, objConnection3)
Dim objDataSet AsNew DataSet()
Dim objDataView AsNew DataView(objDataSet.Tables("SC_TICKETS"))
dgNameList.DataSource = objDataView
Are there any known issues with extracting data from an Oracle 10g database to vb.net? My assumption is that the error is occuring on the .Net side as Coldfusion processes the request correctly.
Any assistance is greatly appreciated.
I dont see anything significantly wrong with the setup.. You can try doing the data access the .NET 2.0 way for a quick doublecheck..
Follow the DW2 link in my signature and set up a data access layer in .NET 2 style.. It should only take about 5 minutes. Feel free to post if you get stuck..
Thanks for the post, cjard. I actually gave you some incorrect information. I was working under the impression that this database had been upgraded to Oracle 10g and it has not. It is version 188.8.131.52.0 of Oracle. After some research on the web last night I found an obscure bug with this version that returns junk data when you execute a query against it that returns multiple rows of data.
The fix appears to be upgrading to a new version of the Oracle DB. A work around is to Select all columns in the query instead of limiting the column returns and then parsing the data in vb.net.
SELECT * FROM INCIDENTSM1 WHERE CLOSE_TIME >= To_Date('06-Mar-2007','DD-MON-YYYY')
Thanks for looking.