Hello,

I am writing a custom search form on my SS3.x site but any time I run a DB::query command if there are any commands after that I always get "Commands out of sync; you can't run this command now"

Is there a way to call a custom sql query and get around this now in SS3.0

What I am doing is - on the form save action, depending upon what is submitted I am then calling the stored procedure.

     public function SearchEventsByLocation($data, $form){
		if($data['Zip'] == "" && $data['State'] == ""){
			Session::set('LocationSearchFormError', 'Please enter a Zip Code or choose a State');			
			$this->redirectBack();
		} else {
			$data['Events'] = array();
			if($data['State'] != ""){				
				// get events in state
				$sql = "CALL getEventsInState('".Convert::raw2sql($data['State'])."');";		
				$results = DB::query($sql);
				for ($i = 0; $i < $results->numRecords(); $i++) {
		        	$record = $results->nextRecord();
					$data['Events'][] = $record;
				}
			} else {
				//get events by zip and radius
				$sql = "CALL getEventsNearZip(".Convert::raw2sql($data['Zip']).",".Convert::raw2sql($data['Radius']).".0);";		
				$results = DB::query($sql);
				for ($i = 0; $i < $results->numRecords(); $i++) {
		        	$record = $results->nextRecord();
					$data['Events'][] = $record;
				}
			}
			
		}
		return $this->owner->customise($data)->renderWith(array('Calendar_SearchEventsByLocation', 'Page'));
	}

The problem is then when ever I try to render the page - I am always getting the same error message.

If I don't run the queries the page will render fine. the only way I have found to work around this is to render the page first and then use js templating and an ajax call to then make the queries. I don't want to do this if I don't have to as it is not efficient to the user.

Thanks for any help,
Joe

Hi obj63mc,
did you managed to solve this issue?

I'm trying to do the same thing with no success...

Hi Yommy,

What I basically had to do was set some session variables in that function for what was posted, then instead of running the sql query there, redirect to another action on the controller. Then on the template for that new action I would look at the session variables and then run the sql query there. For some reason running this complex query then trying to customize a view with it, just wouldn't work.

I think i understand what you have done, it's pretty much what i thought so i will try that way too.

Thank you so much for your prompt answer!