[SOLVED] switching btwn DB's...

This topic is locked

[SOLVED] Â switching btwn DB's...	9/10/2019 8:06:40 AM
ASPRunner.NET General questions
H heilmanmd author Trying to switch btwn primary and secondary DB's... Specifically, primary is DB2 and secondary is SQL Srvr using the DB.SetConnection API as documented in help Code have is: ------------------------------------------------------ dynamic datars2; dynamic rs2 ; // switch over to SQL SErver ... DB.SetConnection("ODBC_lims_test"); // this is the name shown in the WIZARD connection and matches name in Web Config connection name as well // have also tried just lims_test sqlcmd = "select count(*) as hits from ( " + " select sampleid,method from dbo.nye_result where processed = 0 group by sampleid,method ) a ;" ; rs2 = DB.Query(sqlcmd); datars2 = rs2.fetchAssoc(); if(datars2 ) { XSession.Session["numassays"] = String.Format("{0}",datars2["HITS"]); } // switch back to primary connection aka DB2 ... DB.SetConnection(""); -------------------------------------------------------- Get error: on datars2 = rs2.fetchAssoc(); Cannot perform runtime binding on a null reference I'm guessing getting above error because it didn't connect to the DB... Anyways, if anybody has an example of switching btwn DB's using the DB API as documented in help Most appreciated. Thanks Mark

Mark as Solved Mark as not solved

2 replies


	admin 9/10/2019
	Your code looks good in general. I would suggest performing a few basic troubleshooting steps first. Make sure you run the latest build of ASPRunner.NET Make sure connection name is correct (case-sensitive) Make sure SQL Query is correct and returns data If you think that connection is not switching try to use a test SQL query that will work with the main connection to verify this hypothesis.


H	heilmanmd author 9/10/2019
	Case SOLVED Root cause case sensitivity Note for DB2 all SQL results are always CAPS regardless i.e. data["HITS"] is what needed even if sql = select count() as hits from yada yada yada.. after having that ingrained into my head for XXX years and now adding another DB i.e. Sql Server one would think same rule applies... Not so.. SQL server sql "IS" case sensitive for the results i.e. data["hits"] is what is needed if sql = select count() as hits from yada yada... Live and learn Below is working CODE for SQL SERVER DB connection... Thanks Mark dynamic datars2; dynamic rs2 ; int hits=-99; XSession.Session["numassays"]=hits.ToString(); // switch over to SQL SErver ... DB.SetConnection("ODBC:lims_test"); sqlcmd = "select count(*) as hits from dbo.nye_result where processed = 0 ;" ; rs2 = DB.Query(sqlcmd); datars2 = rs2.fetchAssoc(); if(datars2 ) { hits = datars2["hits"]; XSession.Session["numassays"]=hits.ToString() ; } // switch back to primary connection aka DB2 ... DB.SetConnection("");

2 replies

Mark as Solved Reply