This is really driving me crazy! I have a development server at my office where I wrote and tested my code. All is well and the code works fine. When I copied the code to the clients server I get the "Failed to enable constraints" error when running this part of the code:
mytable1 = DAL.GetSQLResults(DDLTest_name.Text, TBPID.Text)
Dim dr As New DataTableReader(mytable1)
mytable1.Load(dr)
Dim dv As DataView = mytable1.DefaultView
Dim mytable2 As New DataTable
mytable2 = DAL.GetOracleResults(DDLTest_name.Text, TBPID.Text)
Dim dr2 As New DataTableReader(mytable2)
mytable1.Load(dr2)
The functions are:
Public Shared Function GetSQLResults(ByVal strTestName As String, ByVal strPTID As String) As DataTable
Dim strCon As String = GetConnectionString("FMC_ACCESSConnectionString")
Dim conn As New SqlConnection(strCon)
Dim cmdSelect As New SqlCommand("SELECT convert(varchar(10),CMPatientDataFile.Run_Date,101) As Date, ROUND(CMPatientDataFile.Result_Numeric,2)As Result,CmPatientDataFile.Test_Name As Test FROM CMPatientDataFile WHERE CMPatientDataFile.Pat_ID = @Pat_ID AND CMPatientDataFile.Test_name = @TestName ORDER BY CMPatientDataFile.Run_Date", conn)
'Add Parameter to the Command
cmdSelect.Parameters.Add("@Pat_ID", SqlDbType.NVarChar, 20).Value = strPTID
cmdSelect.Parameters.Add("@TestName", SqlDbType.NVarChar, 60).Value = strTestName
Dim dtrTable As New DataTable
conn.Open()
dtrTable.Load(cmdSelect.ExecuteReader(CommandBehavior.CloseConnection))
Return dtrTable
End Function
Public Shared Function GetOracleResults(ByVal strTestName As String, ByVal strPTID As String) As DataTable
Dim ostrCon As String = GetConnectionString("XE_ConnectionString")
Dim oconn As New OracleConnection(ostrCon)
Dim ocmdSelect As New OracleCommand("SELECT TRUNC(REQ_PANELS.RUN_DATE) ""Date"",RESULTS.RESULT_NUMERIC ""Result"", Tests.Test_Name ""Test"" FROM REQ_PANELS, REQUISITIONS, RESULTS, TESTS WHERE REQUISITIONS.ACC_ID = REQ_PANELS.ACC_ID AND REQ_PANELS.RP_ID = RESULTS.RP_ID AND RESULTS.TEST_ID = TESTS.TEST_ID AND (REQUISITIONS.PAT_ID =
at_ID) AND (TESTS.TEST_NAME = :TEST_NAME) AND (REQ_PANELS.RUN_DATE IS NOT NULL) ORDER BY REQ_PANELS.RUN_DATE", oconn)
'Add Parameter to the Command
ocmdSelect.Parameters.Add(New OracleParameter("at_ID", OracleDbType.NVarchar2, 20)).Value = strPTID
ocmdSelect.Parameters.Add(New OracleParameter(":TEST_NAME", OracleDbType.NVarchar2, 60)).Value = strTestName
Dim odtrTable As New DataTable
oconn.Open()
odtrTable.Load(ocmdSelect.ExecuteReader(CommandBehavior.CloseConnection))
Return odtrTable
End Function
If I rewrite it to get results from either oracle or mssql it works. So I figure that the problem has to be in combining the tables into one.
Any ideas?
mytable1 = DAL.GetSQLResults(DDLTest_name.Text, TBPID.Text)
Dim dr As New DataTableReader(mytable1)
mytable1.Load(dr)
Dim dv As DataView = mytable1.DefaultView
Dim mytable2 As New DataTable
mytable2 = DAL.GetOracleResults(DDLTest_name.Text, TBPID.Text)
Dim dr2 As New DataTableReader(mytable2)
mytable1.Load(dr2)
The functions are:
Public Shared Function GetSQLResults(ByVal strTestName As String, ByVal strPTID As String) As DataTable
Dim strCon As String = GetConnectionString("FMC_ACCESSConnectionString")
Dim conn As New SqlConnection(strCon)
Dim cmdSelect As New SqlCommand("SELECT convert(varchar(10),CMPatientDataFile.Run_Date,101) As Date, ROUND(CMPatientDataFile.Result_Numeric,2)As Result,CmPatientDataFile.Test_Name As Test FROM CMPatientDataFile WHERE CMPatientDataFile.Pat_ID = @Pat_ID AND CMPatientDataFile.Test_name = @TestName ORDER BY CMPatientDataFile.Run_Date", conn)
'Add Parameter to the Command
cmdSelect.Parameters.Add("@Pat_ID", SqlDbType.NVarChar, 20).Value = strPTID
cmdSelect.Parameters.Add("@TestName", SqlDbType.NVarChar, 60).Value = strTestName
Dim dtrTable As New DataTable
conn.Open()
dtrTable.Load(cmdSelect.ExecuteReader(CommandBehavior.CloseConnection))
Return dtrTable
End Function
Public Shared Function GetOracleResults(ByVal strTestName As String, ByVal strPTID As String) As DataTable
Dim ostrCon As String = GetConnectionString("XE_ConnectionString")
Dim oconn As New OracleConnection(ostrCon)
Dim ocmdSelect As New OracleCommand("SELECT TRUNC(REQ_PANELS.RUN_DATE) ""Date"",RESULTS.RESULT_NUMERIC ""Result"", Tests.Test_Name ""Test"" FROM REQ_PANELS, REQUISITIONS, RESULTS, TESTS WHERE REQUISITIONS.ACC_ID = REQ_PANELS.ACC_ID AND REQ_PANELS.RP_ID = RESULTS.RP_ID AND RESULTS.TEST_ID = TESTS.TEST_ID AND (REQUISITIONS.PAT_ID =
at_ID) AND (TESTS.TEST_NAME = :TEST_NAME) AND (REQ_PANELS.RUN_DATE IS NOT NULL) ORDER BY REQ_PANELS.RUN_DATE", oconn)'Add Parameter to the Command
ocmdSelect.Parameters.Add(New OracleParameter("
at_ID", OracleDbType.NVarchar2, 20)).Value = strPTIDocmdSelect.Parameters.Add(New OracleParameter(":TEST_NAME", OracleDbType.NVarchar2, 60)).Value = strTestName
Dim odtrTable As New DataTable
oconn.Open()
odtrTable.Load(ocmdSelect.ExecuteReader(CommandBehavior.CloseConnection))
Return odtrTable
End Function
If I rewrite it to get results from either oracle or mssql it works. So I figure that the problem has to be in combining the tables into one.
Any ideas?