Multiple Select Issue again

[jenmer]

Hi

If a sector has more than two items assigned to it, it omits it from the search result

Back again.. This multiple select has been a real pain one thing solved to find another thing wrong with it.

In the search results it omits any entry that has two items or more assigned to it.

For example If a Student has more than two Sectors that he/she is interested in it omits it from the search results.

In the Database the Field MBASectorID within MBACVBook, has values 1,2,3

Because it's has more than one item selected it knocks it out.

The main Query is below. Do i need a CFloop to sort this out?

<!--- The query to gets the data to generate the list --->
<cfquery name="qCVBookSelect" datasource="#request.dsName#">
select CVBook.CVBookID ,
CVBook.MBASectorID ,
MBASector.MBASectorName ,
CVBook.MBAFunctionID ,
MBAFunction.MBAFunctionName ,
CVBook.MBALocationID ,
MBALocation.MBALocationName ,
CVBook.MBALanguageID ,
MBALanguage.MBALanguageName ,
CVBook.Firstname ,
CVBook.Lastname ,
CVBook.CVPictureFilename ,
CVBook.Telephone ,
CVBook.Email ,
CVBook.Citizenship ,
CVBook.Brief ,
CVBook.CVFilename ,
CVBook.DateCreated ,
CVBook.DateLastUpdated ,
CVBook.Updatedby ,
CVBook.Createdby
from CVBook
left join MBASector on MBASector.MBASectorID = CVBook.MBASectorID
left join MBAFunction on MBAFunction.MBAFunctionID = CVBook.MBAFunctionID
left join MBALocation on MBALocation.MBALocationID = CVBook.MBALocationID
left join MBALanguage on MBALanguage.MBALanguageID = CVBook.MBALanguageID
where 1 = 1


<cfif form.mBASectorID gt "">
and CVBook.mBASectorID in ('#form.mbasectorid#')
</cfif>
<cfif form.mBAFunctionID gt "">
and CVBook.mBAFunctionID in ('#form.mBAFunctionID#')
</cfif>
<cfif form.mBALocationID gt "">
and CVBook.mBALocationID in ('#form.mBALocationID#')
</cfif>
<cfif form.mBALanguageID gt "">
and CVBook.mBALanguageID in ('#form.mBALanguageID#')
</cfif>
</cfquery>

Thanking you in advance

Jennifer

 

[r937]

In the Database the Field MBASectorID within MBACVBook, has values 1,2,3

you mean a single value is a comma-delimited string of numbers?

r937.com | rudy.ca

 

[jenmer]

Hi

Yes i think so, the Field MBASectorID has 1,2,3 entered, which is a Look up for the MBASector Table.
The values of 1,2,3 are Accounting, Advertising, Consulting
which are belong to MBASector Table.

I hope this makes sense

Jennifer

 

[r937]

did you try this suggestion from your previous thread

and CVBook.mBASectorID in
(<cfqueryparam cfsqltype="cf_sql_integer" value="#Form.mbasectorid#" list="Yes">)


r937.com | rudy.ca

 

[jenmer]

Hi

and CVBook.mBASectorID in
(<cfqueryparam cfsqltype="cf_sql_integer" value="#Form.mbasectorid#" list="Yes">)

gives me the following error


[Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value '10,12,13,3,4,6,7,9' to a column of data type int.


The column cannot be an INT as i need to enter 10,12,13 etc into the column and wont accept it when the datatype is an INT.

Really under pressure to get this solved..

Jennifer

 

[r937]

in your original post, you wrote...

and CVBook.mBASectorID in ('#form.mbasectorid#')


did you remove the quotes, or keep them?

r937.com | rudy.ca

 

[jenmer]

I use the quotes Like

and CVBook.mBASectorID in ('#form.mbasectorid#')

But when i use

and CVBook.mBASectorID in (#form.mbasectorid#)

It gives me this error

[Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value '10,12,13,3,4,6,7,9' to a column of data type int.