WHERE someField IN ( ) [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 4 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> SELECT subcategoryname FROM Subcategories WHERE Subcategories.subcategoryname IN ( ) [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 15 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> SELECT Distinct App.Part,Positions.position FROM App,BaseVehicle,CodeMaster,Parts,Subcategories,Make,Model,Categories,Positions WHERE BaseVehicle.BaseVehicleID = App.BaseVehicle and App.PartType = Parts.PartTerminologyID and Parts.PartTerminologyID = CodeMaster.PartTerminologyID and Subcategories.SubCategoryID = CodeMaster.SubCategoryID and Categories.CategoryID = CodeMaster.CategoryID and Positions.PositionID = App.Position and BaseVehicle.YearID = #Session.Year# and BaseVehicle.MakeID = Make.MakeID and Make.MakeName = '#Session.Make#' and BaseVehicle.ModelID = Model.ModelID and Model.modelname = '#session.model#' AND Categories.categoryname = '#Form.Cat#' AND Subcategories.subcategoryname IN ( ) [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 17 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> saving multiple values - Coldfusion - Advanced Techniques

saving multiple values - Coldfusion - Advanced Techniques

Hello I have a list of items that drop down in a menu.. now I can select as many as I like... however when I select lets say 2 items in the menu and hit SUBMIT nothing happens... if i select 1 item the parts for that item show up. here is my query I need to be able to display for multiple selections please help.. Thank you in advance! <cfquery name="Recordset3" datasource="euroxml"> Select Distinct App.Part,App.Position From App,BaseVehicle,CodeMaster,Parts,Subcategories,Mak e,Model,Categories WHere BaseVehicle.BaseVehicleID = App.BaseVehicle and App.PartType = Parts.PartTerminologyID and Parts.PartTerminologyID = CodeMaster.PartTerminologyID and Subcategories.SubCategoryID = CodeMaster.SubCategoryID and Categories.CategoryID = CodeMaster.CategoryID and ...

  1. #1

    Default saving multiple values

    Hello I have a list of items that drop down in a menu.. now I can select as
    many as I like... however when I select lets say 2 items in the menu and hit
    SUBMIT nothing happens... if i select 1 item the parts for that item show up.

    here is my query I need to be able to display for multiple selections please
    help..


    Thank you in advance!

    <cfquery name="Recordset3" datasource="euroxml">
    Select Distinct App.Part,App.Position From
    App,BaseVehicle,CodeMaster,Parts,Subcategories,Mak e,Model,Categories
    WHere BaseVehicle.BaseVehicleID = App.BaseVehicle and App.PartType =
    Parts.PartTerminologyID
    and Parts.PartTerminologyID = CodeMaster.PartTerminologyID and
    Subcategories.SubCategoryID
    = CodeMaster.SubCategoryID and Categories.CategoryID = CodeMaster.CategoryID
    and
    BaseVehicle.YearID = #Session.Year# and BaseVehicle.MakeID = Make.MakeID and
    Make.MakeName
    = '#Session.Make#' and BaseVehicle.ModelID = Model.ModelID and Model.modelname
    = '#session.model#' and Categories.categoryname = '#Form.Cat#' and
    Subcategories.subcategoryname
    = '#Form.Sub#'
    </cfquery>

    mactech999 Guest

  2. #2

    Default Re: saving multiple values

    For the fields that can have more than one selected value, change this:
    and databasefield = '#form.field#'
    to this
    and databasefield in ('#form.field#')

    If you don't have debugging turned on, turn it on. Things like this become
    very obvious.


    Originally posted by: mactech999
    Hello I have a list of items that drop down in a menu.. now I can select as
    many as I like... however when I select lets say 2 items in the menu and hit
    SUBMIT nothing happens... if i select 1 item the parts for that item show up.

    here is my query I need to be able to display for multiple selections please
    help..


    Thank you in advance!



    Dan Guest

  3. #3

    Default Re: saving multiple values

    should I be outputting it a different way though I have a table with my fields
    in it here is the code...

    <form name="form1" method="post" action="">
    <div align="center"><cfoutput>#Session.Year#</cfoutput>
    <cfoutput>#session.Make#</cfoutput><cfoutput>#session.Model#</cfoutput><cfoutput 
    <table width="100%" border="1">
    <tr>
    <td width="50%"><div align="center"><strong>Category:
    </strong></div></td>
    <td width="50%"><div align="center"><strong>SubCategory:
    </strong></div></td>
    </tr>
    <tr>
    <td height="42"><div align="center">
    <select name="Cat" onChange="javascript:submit();">
    <cfoutput query="Recordset1">
    <option value="#Recordset1.categoryname#" <cfif
    (isDefined("Form.cat") AND Recordset1.categoryname EQ
    Form.cat)>selected</cfif>>#Recordset1.categoryname#</option>
    </cfoutput>
    </select>
    </div></td>
    <td><div align="center">
    <select name="Sub" size="10" multiple>
    <cfoutput query="Recordset2">
    <option value="#Recordset2.subcategoryname#" <cfif
    (isDefined("Form.sub") AND Recordset2.subcategoryname EQ
    Form.sub)>selected</cfif>>#Recordset2.subcategoryname#</option>
    </cfoutput>
    </select>
    </div></td>
    </tr>
    </table>
    <p>
    <center><input type="submit" name="Submit" value="Submit"
    onClick="page3.cfm"></center>
    </p>
    <p>&nbsp;</p>
    </form>
    <form name="form2" method="post" action="page2.cfm">
    <cfif recordset3.RecordCount GT 0 >
    <table width="750" height="74" border="3">
    <tr bgcolor="#33CC66">
    <td width="65" height="27"><center><font size="4" face="Arial, Helvetica,
    sans-serif">Part Number</font></center></td>
    <td width="65" height="27"><center><font size="4" face="Arial, Helvetica,
    sans-serif">Position</font></center></td>

    </tr>
    <cfoutput query="Recordset3" startrow="#Startrow_Recordset3#"
    maxrows="#Maxrows_Recordset3#">
    <tr bgcolor="##FFFFCC">
    <td height="27"><center><font size="3" face="Arial, Helvetica,
    sans-serif"><strong><cfif Fileexists("images/#Recordset3.Part#.jpg")><a
    href="images/#Recordset3.Part#.jpg">#Recordset3.Part#</a><cfelse>
    #Recordset3.Part#</cfif></strong></font>
    </center></td>
    <td height="27"><center><font size="3" face="Arial, Helvetica,
    sans-serif">#Recordset3.Position#</font></center></td>
    </tr>
    </cfoutput>
    </table>

    </cfif>
    </form>

    mactech999 Guest

  4. #4

    Default Re: saving multiple values

    >Dan Bracuk 

    If you're dealing with a "varchar" or "text" field, you need to put single
    quotes around each value in the list
    WHERE someField IN ('abc', 'efg', 'hij')
    or you can simply use CFQUERYPARAM with the "list" attribute and let it do all
    the work.
    <!--- just an example. not tested --->
    WHERE someField IN ( <cfqueryparam value="#form.yourListOfValues#"
    cfSqlType="cf_sql_varchar" list="yes"> )







    mxstu Guest

  5. #5

    Default Re: saving multiple values

    ok I've tried these solutions however I'm still getting the same problem I did
    a cfdump and that showed me why I'm getting errors still...

    this is how it is comming accross:

    Disc Pads and Brake Shoes,Drums and Rotors

    when using the ('#form.Sub#')

    what i need would be for it to pick up the two still however show them like
    this:

    Disc Pads and Brake Shoes
    Drums and Rotors

    do you know if there is a way to do this?

    mactech999 Guest

  6. #6

    Default Re: saving multiple values

    If mxstu's suggestion didn't work, try
    where somefield in (#listqualify(form.Sub)#)

    List qualify will put single quotes around each list element.



    Originally posted by: mactech999
    ok I've tried these solutions however I'm still getting the same problem I did
    a cfdump and that showed me why I'm getting errors still...

    this is how it is comming accross:

    Disc Pads and Brake Shoes,Drums and Rotors

    when using the ('#form.Sub#')

    what i need would be for it to pick up the two still however show them like
    this:

    Disc Pads and Brake Shoes
    Drums and Rotors

    do you know if there is a way to do this?



    Dan Guest

  7. #7

    Default Re: saving multiple values

    > when using the ('#form.Sub#')

    mactech999,

    If you put single quotes around the variable, the query it will search for
    records where the value of someField is exactly equal to 'Disc Pads and Brake
    Shoes,Drums and Rotors' , which is obviously not what you want.

    Either use the ListQualify function Dan mentioned:

    WHERE someField IN (#listQualify(form.sub, "'")#)

    ... which should produce something like this. Note how each list element is
    enclosed in single quotes:.

    WHERE someField IN ('Disc Pads and Brake Shoes','Drums and Rotors' )


    ... or use cfqueryparam like in the example above. Note: CFQUERYPARAM handles
    the single quoting for you, so you don't need to add single quotes around
    #form.sub#.


    WHERE someField IN
    (
    <cfqueryparam value="#form.sub#" cfSqlType="cf_sql_varchar" list="yes">
    )

    You may also need to add validation, in case form field does not contain at
    least one selection.



    mxstu Guest

  8. #8

    Default Re: saving multiple values

    Hey Guys sorry about this... I can't seem to get it to work it still only gives
    me the results I want when I select just one of the items... when i select 2
    nothing happens... here is the debugger code

    SQL Queries

    Recordset1 (Datasource=euroxml, Time=16ms, Records=23) in
    C:\Inetpub\wwwroot\AcesFormatCatalogPage\page2.cfm 11:03:15.015

    Select categoryname From Categories Order by categoryname

    Recordset2 (Datasource=euroxml, Time=16ms, Records=7) in
    C:\Inetpub\wwwroot\AcesFormatCatalogPage\page2.cfm 11:03:16.016

    SELECT Distinct Subcategories.subcategoryname FROM Subcategories,
    CodeMaster,Categories
    WHERE Subcategories.SubCategoryID = CodeMaster.SubCategoryID and
    Categories.CategoryID
    = CodeMaster.CategoryID and Categories.categoryname = 'Brake
    '

    Recordset3 (Datasource=euroxml, Time=15ms, Records=0) in
    C:\Inetpub\wwwroot\AcesFormatCatalogPage\page2.cfm 11:03:16.016

    SELECT Distinct App.Part,Positions.position FROM
    App,BaseVehicle,CodeMaster,Parts,Subcategories,Mak e,Model,Categories,Positions
    WHERE BaseVehicle.BaseVehicleID = App.BaseVehicle and App.PartType =
    Parts.PartTerminologyID
    and Parts.PartTerminologyID = CodeMaster.PartTerminologyID and
    Subcategories.SubCategoryID
    = CodeMaster.SubCategoryID and Categories.CategoryID = CodeMaster.CategoryID
    and Positions.PositionID = App.Position and
    BaseVehicle.YearID = 1996 and BaseVehicle.MakeID = Make.MakeID and
    Make.MakeName
    = 'Chevrolet ' and BaseVehicle.ModelID
    = Model.ModelID and Model.modelname
    = 'Caprice ' and
    Categories.categoryname = 'Brake
    ' and
    Subcategories.subcategoryname =
    ?


    Query Parameter Value(s) -
    Parameter #1(CF_SQL_CHAR) = Disc Pads and Brake Shoes,Drums and Rotors


    mactech999 Guest

  9. #9

    Default Re: saving multiple values

    mactech99,

    I think your problem is this line:

    and Subcategories.subcategoryname = ?

    You are searching for multiple values, so you need to use WHERE IN (.... some
    list of values here ....) instead of the equals operator =. See the examples
    in my last post.



    mxstu Guest

  10. #10

    Default Re: saving multiple values

    ok sorry about that here is what i have now it's so close to working!!!

    <cfquery name="Recordset3" datasource="euroxml">
    SELECT Distinct App.Part,Positions.position FROM
    App,BaseVehicle,CodeMaster,Parts,Subcategories,Mak e,Model,Categories,Positions
    WHERE BaseVehicle.BaseVehicleID = App.BaseVehicle and App.PartType =
    Parts.PartTerminologyID
    and Parts.PartTerminologyID = CodeMaster.PartTerminologyID and
    Subcategories.SubCategoryID
    = CodeMaster.SubCategoryID and Categories.CategoryID = CodeMaster.CategoryID
    and Positions.PositionID = App.Position and
    BaseVehicle.YearID = #Session.Year# and BaseVehicle.MakeID = Make.MakeID and
    Make.MakeName
    = '#Session.Make#' and BaseVehicle.ModelID = Model.ModelID and Model.modelname
    = '#session.model#' and Categories.categoryname = '#Form.Cat#' and
    Subcategories.subcategoryname IN
    ( <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="'#Form.Sub#'" separator=","
    List="yes"> )

    this is the results the query gives me:

    Query Parameter Value(s) -
    Parameter #1(CF_SQL_VARCHAR) = 'Disc Pads and Brake Shoes
    Parameter #2(CF_SQL_VARCHAR) = Drums and Rotors'

    there is missing the closing quote on the first Parameter and a starting one
    on the second Parameter
    however what i need would be to have this:

    Query Parameter Value(s) -
    Parameter #1(CF_SQL_VARCHAR) = 'Disc Pads and Brake Shoes'
    Parameter #2(CF_SQL_VARCHAR) = 'Drums and Rotors'

    if you have any suggestions please let me know

    thanks


    mactech999 Guest

  11. #11

    Default Re: saving multiple values

    mactech999,

    Remember, you don't need to use any single quotes if you're using
    CFQUERYPARAM. The tag should handle it all for you. Just get rid of the single
    quotes around #Form.Sub#.

    ( <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="'#Form.Sub#'" separator=","
    List="yes"> )

    mxstu Guest

  12. #12

    Default Re: saving multiple values

    ok only problem is when i take out the single quotes it tells me I'm missing an operator in the query expression .
    mactech999 Guest

  13. #13

    Default Re: saving multiple values

    What database are you using?
    mxstu Guest

  14. #14

    Default Re: saving multiple values

    I'm using an access database that is on the coldfusion server...It's called
    euroxml

    this is my query:

    <cfquery name="Recordset3" datasource="euroxml">
    SELECT Distinct App.Part,Positions.position FROM
    App,BaseVehicle,CodeMaster,Parts,Subcategories,Mak e,Model,Categories,Positions
    WHERE BaseVehicle.BaseVehicleID = App.BaseVehicle and App.PartType =
    Parts.PartTerminologyID
    and Parts.PartTerminologyID = CodeMaster.PartTerminologyID and
    Subcategories.SubCategoryID
    = CodeMaster.SubCategoryID and Categories.CategoryID = CodeMaster.CategoryID
    and Positions.PositionID = App.Position and
    BaseVehicle.YearID = #Session.Year# and BaseVehicle.MakeID = Make.MakeID and
    Make.MakeName = '#Session.Make#' and BaseVehicle.ModelID = Model.ModelID
    and Model.modelname = '#session.model#' and Categories.categoryname =
    '#Form.Cat#' and Subcategories.subcategoryname IN
    (<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="'#Form.Sub#'" separator=","
    List="yes">)
    </cfquery>

    mactech999 Guest

  15. #15

    Default Re: saving multiple values

    mactech999,

    A simple test, with no single quotes, seems work fine for me. Can you remove
    the single quotes and post the exact error message (including the actual sql
    statement)?



    <!---successful --->
    <cfset Form.Sub = "Disc Pads and Brake Shoes,Drums and Rotors">
    <cfquery name="test" datasource="myAccessUnicodeDSN">
    SELECT subcategoryname
    FROM Subcategories
    WHERE Subcategories.subcategoryname IN
    (
    <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#Form.Sub#" separator=","
    List="yes">
    )
    </cfquery>
    <cfdump var="#test#">

    mxstu Guest

  16. #16

    Default Re: saving multiple values

    ok here is the exact query only thing is I'm not setting the Form.Sub to be
    anything I need it to be what the person selects from the list... so lets say
    there are 10 items in the list if he picks 5 then I need there to be 5 items
    pulled from form.sub here is the query and the error i get...

    Error Executing Database Query.
    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
    Access Driver] Syntax error (missing operator) in query expression
    'BaseVehicle.BaseVehicleID = App.BaseVehicle and App.PartType =
    Parts.PartTerminologyID and Parts.PartTerminologyID =
    CodeMaster.PartTerminologyID and Subcategories.SubCategoryID =
    CodeMaster.SubCategoryID and Categories.CategoryID = CodeMaster.CategoryID'.

    The error occurred in C:\Inetpub\wwwroot\AcesFormatCatalogPage\page2.cfm : line
    24

    22 : BaseVehicle.YearID = #Session.Year# and BaseVehicle.MakeID = Make.MakeID
    and Make.MakeName = '#Session.Make#' and BaseVehicle.ModelID = Model.ModelID
    23 : and Model.modelname = '#session.model#' and Categories.categoryname =
    '#Form.Cat#' and Subcategories.subcategoryname IN
    24 : (<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#Form.Sub#"
    separator="," List="yes">)
    25 : </cfquery>
    26 :




    --------------------------------------------------------------------------------

    SQL SELECT Distinct App.Part,Positions.position FROM
    App,BaseVehicle,CodeMaster,Parts,Subcategories,Mak e,Model,Categories,Positions
    WHERE BaseVehicle.BaseVehicleID = App.BaseVehicle and App.PartType =
    Parts.PartTerminologyID and Parts.PartTerminologyID =
    CodeMaster.PartTerminologyID and Subcategories.SubCategoryID =
    CodeMaster.SubCategoryID and Categories.CategoryID = CodeMaster.CategoryID and
    Positions.PositionID = App.Position and BaseVehicle.YearID = 1998 and
    BaseVehicle.MakeID = Make.MakeID and Make.MakeName = 'Chevrolet ' and
    BaseVehicle.ModelID = Model.ModelID and Model.modelname = 'Cavalier ' and
    Categories.categoryname = '' and Subcategories.subcategoryname IN ()



    mactech999 Guest

  17. #17

    Default Re: saving multiple values

    mactech999,

    The error occurs because in your test case, no items were selected:
    Subcategories.subcategoryname IN (no values here)

    If your variables are optional, you need to add some validation so that your
    query will skip values that were not entered (ie. if the user did not select a
    category or subcategory name, skip that condition).

    Also, you have a large number of tables in your query. Are they all
    necessary? You might also consider using aliases to make your query more
    readable.




    <!--- not tested --->
    <cfquery name="Recordset3" datasource="euroxml">
    SELECT Distinct App.Part,Positions.position
    FROM
    App,BaseVehicle,CodeMaster,Parts,Subcategories,Mak e,Model,Categories,Positions
    WHERE BaseVehicle.BaseVehicleID = App.BaseVehicle and
    App.PartType = Parts.PartTerminologyID and
    Parts.PartTerminologyID = CodeMaster.PartTerminologyID and
    Subcategories.SubCategoryID = CodeMaster.SubCategoryID and
    Categories.CategoryID = CodeMaster.CategoryID and
    Positions.PositionID = App.Position and
    BaseVehicle.YearID = #Session.Year# and
    BaseVehicle.MakeID = Make.MakeID and
    Make.MakeName = '#Session.Make#' and
    BaseVehicle.ModelID = Model.ModelID and
    Model.modelname = '#session.model#'
    <cfif trim(Form.Cat) neq "">
    AND Categories.categoryname = '#Form.Cat#'
    </cfif>
    <cfif listLen(Form.Sub) gt 0>
    AND Subcategories.subcategoryname IN
    (
    <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="'#Form.Sub#'"
    separator="," List="yes">
    )
    </cfif>
    </cfquery>

    mxstu Guest

Similar Threads

  1. Losing checkbox values when saving PDF
    By poteen@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 30
    Last Post: November 10th, 05:34 PM
  2. Saving/Displaying multiple options for a record
    By Armen in forum Dreamweaver AppDev
    Replies: 3
    Last Post: September 26th, 01:34 PM
  3. Saving PDFs for multiple versions
    By Trina_Holman@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 9
    Last Post: August 6th, 03:23 AM
  4. saving multiple uploaed image names to a db
    By erova in forum Coldfusion - Getting Started
    Replies: 5
    Last Post: February 23rd, 01:43 AM
  5. saving text values of dynamically created textboxes
    By YunusEmre in forum ASP.NET Building Controls
    Replies: 15
    Last Post: January 3rd, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •