Inserting multiple values from a Multi-Selecet for

Results 1 to 4 of 4

Thread: Inserting multiple values from a Multi-Selecet for

  1. #1
    Jumbeau Guest

    Default Inserting multiple values from a Multi-Selecet for

    Anyone who can provide input on this would make my day. Thank you too much in advance.<BR><BR>Here is my situation,<BR>I orgininally had two tables:<BR>Category<BR>SubCategory<BR>But this creates a many-to-many relationship since a Category can have many SubCategories, and in my situation a SubCategory can belong to one or more Categories. <BR><BR>So to break this many-to-many relationship, I created a gerund table:<BR>SubJoinCat [Primary Key is JunctionID]<BR>Which also contains SubCatIDJoin(relates to SubCatID primary key in the SubCategory table) and CatIDJoin (relates to CatID primary key in the Category table)<BR><BR>All unique identifiers in each table have numeric data types.<BR>My problem occurs when trying to Insert the multiple values retrieved from a Multi-Select drop down (CategoryToSub) into the SubJoinCat table.<BR><BR>Here is the section of code I am having problems with below:<BR><BR>If actionPerformed = "Submit Sub Category Entry" Then<BR> For Each Catx in request("CategoryToSub")<BR> SQLSubCategory = "INSERT INTO SubJoinCat (CatIDJoin, SubCatIDJoin) VALUES (" & int (request("CategoryToSub")) & "," & int(Catx) & ")"<BR> Next Catx<BR>RSName.Open SQLSubCategory,Conn<BR>SQLSubCategory = "SELECT Max(JunctionID) AS maxnum FROM SubJoinCat"<BR>Set RSName= Conn.Execute(SQLSubCategory)<BR>maxnum = RSName("maxnum").Value<BR>Call CheckForErrors(Conn)<BR>End if<BR><BR>Here is the error I receive:<BR><BR>Microsoft VBScript runtime error &#039 800a000d&#039 <BR>Type mismatch: &#039[string: "22, 14, 20"]&#039 <BR>/38th/HDadmin/InsertLookupInfo.asp, line 110 <BR><BR>I am new to updating database records with values from Multiple selections. Please help! <BR><BR>Jumbeau<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Inserting multiple values from a Multi-Selecet

    Your code doesn&#039t make sense to me.<BR><BR>You are doing:<BR><BR>For Each Catx in request("CategoryToSub")<BR>&nbsp;&nbsp;&nbsp;&nbs p;SQLSubCategory = "INSERT INTO SubJoinCat (CatIDJoin, SubCatIDJoin) VALUES (" _<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;& int (request("CategoryToSub")) & "," & int(Catx) & ")"<BR>Next Catx<BR><BR>But you imply from the For Each that request("CategoryToSub") is a collection--I assume it is the multi-valued SELECT you mention. And if it is a collection, then how can <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;int (request("CategoryToSub"))<BR>possibly be meaningful???? Of course you get a TYPE MISMATCH, because you are trying to convert the COLLECTION (which just happens to have the string form "22, 14, 20") to an integer!<BR><BR>I think you are just picking up the wrong field name in that line<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;int (request("CategoryToSub"))<BR>perhaps?<BR><BR>

  3. #3
    athens Guest

    Default Hey thats the same thing i was thinking

    Try this!<BR><BR>For Each Catx in request("CategoryToSub")<BR> Conn.Execute = "INSERT INTO SubJoinCat (CatIDJoin, SubCatIDJoin) VALUES (" _<BR> & int(Your_ID_Value) & "," & int(Catx) & ")"<BR>Next Catx<BR><BR>

  4. #4
    Jumbeau Guest

    Default Thanks Bill and Athens!

    I see what both of you are saying. I knew that when I posted this message that trying to update the database with the collection must be wrong! I&#039ll work on this and let you know how it goes.<BR><BR>Nice to have some sharp people who are willing to help others just starting out!<BR><BR>Jumbeau

Posting Permissions

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