inserting more than one value--previously posted elsewhere

Discussion in 'ASP General' started by middletree, Apr 29, 2004.

  1. middletree

    middletree Guest

    I know this is a SQL101 question, but darned if my fried brain can figure it
    out right now.

    I want to insert rows into a table which only has 2 fields. It comes from an
    ASP multiple select form element; users can choose to select more than one
    selection. If they only select one, it works fine, because it produces this
    SQL statement:
    INSERT INTO TKT_ASSIST (TicketID, EmployeeID) VALUES ('15167','200')

    But if they select more than one, the value of the form is a comma-delimited
    value, for example 200,300, which then yields:

    INSERT INTO TKT_ASSIST (TicketID, EmployeeID) VALUES ('15167','200, 331')

    As you can imagine, I get a SQL error:
    Syntax error converting the varchar value '200, 331' to a column of data
    type int.

    So I should do some ASP script which will do a split of the values and
    insert them one at a time. But I have to ask first, is there a SQL way to
    insert these, so that one row will be inserted for each value given for the
    EmployeeID?
     
    middletree, Apr 29, 2004
    #1
    1. Advertising

  2. middletree wrote:
    > I know this is a SQL101 question, but darned if my fried brain can
    > figure it out right now.
    >
    > I want to insert rows into a table which only has 2 fields. It comes
    > from an ASP multiple select form element; users can choose to select
    > more than one selection. If they only select one, it works fine,
    > because it produces this SQL statement:
    > INSERT INTO TKT_ASSIST (TicketID, EmployeeID) VALUES ('15167','200')
    >
    > But if they select more than one, the value of the form is a
    > comma-delimited value, for example 200,300, which then yields:
    >
    > INSERT INTO TKT_ASSIST (TicketID, EmployeeID) VALUES ('15167','200,
    > 331')
    >
    > As you can imagine, I get a SQL error:
    > Syntax error converting the varchar value '200, 331' to a column of
    > data type int.
    >
    > So I should do some ASP script which will do a split of the values and
    > insert them one at a time. But I have to ask first, is there a SQL
    > way to insert these, so that one row will be inserted for each value
    > given for the EmployeeID?



    The only way to do it in a single statement is to use a UNION query:

    INSERT INTO TKT_ASSIST (TicketID, EmployeeID)
    SELECT '15167','200'
    UNION ALL
    SELECT '15167','331'

    This isn't a bad way to do it, and it's not that hard to code with a loop.
    Let us know if you need details.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Apr 29, 2004
    #2
    1. Advertising

  3. middletree

    middletree Guest

    Well, I was trying to figure out how to break up the string 200,300 into two
    different values (Actually, n different values). Not being a strong array
    guy, I wanted to avoid it. But now it looks like I'll have to do as much
    research for that as for this method you select, so I might as well use the
    array.

    I know that's basic VBScript, so I have no right to ask for help with that
    code, but if you're bored, then feel free to offer some sample code. If not,
    I'll try and figure it out.

    thanks


    "Bob Barrows [MVP]" <> wrote in message
    news:#...
    > middletree wrote:
    > > I know this is a SQL101 question, but darned if my fried brain can
    > > figure it out right now.
    > >
    > > I want to insert rows into a table which only has 2 fields. It comes
    > > from an ASP multiple select form element; users can choose to select
    > > more than one selection. If they only select one, it works fine,
    > > because it produces this SQL statement:
    > > INSERT INTO TKT_ASSIST (TicketID, EmployeeID) VALUES ('15167','200')
    > >
    > > But if they select more than one, the value of the form is a
    > > comma-delimited value, for example 200,300, which then yields:
    > >
    > > INSERT INTO TKT_ASSIST (TicketID, EmployeeID) VALUES ('15167','200,
    > > 331')
    > >
    > > As you can imagine, I get a SQL error:
    > > Syntax error converting the varchar value '200, 331' to a column of
    > > data type int.
    > >
    > > So I should do some ASP script which will do a split of the values and
    > > insert them one at a time. But I have to ask first, is there a SQL
    > > way to insert these, so that one row will be inserted for each value
    > > given for the EmployeeID?

    >
    >
    > The only way to do it in a single statement is to use a UNION query:
    >
    > INSERT INTO TKT_ASSIST (TicketID, EmployeeID)
    > SELECT '15167','200'
    > UNION ALL
    > SELECT '15167','331'
    >
    > This isn't a bad way to do it, and it's not that hard to code with a loop.
    > Let us know if you need details.
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    middletree, Apr 30, 2004
    #3
  4. Well, assuming the 15167 comes from one form variable, and the other comes
    from another form variable, and that all of these values are strings (why is
    that? they seem like numbers to me). Here is some sample code without any
    forms to get you started:

    When you understand it, simple swap out the manual assignments with:

    <%
    commonNumber = "15167"
    multipleNumbers = "200,300,465,700"

    preStatement = "SELECT TicketID = '" & _
    commonNumber & "', EmployeeID = '"

    ' look ma, no loops! ;-)

    postStatement = join(split(multipleNumbers, ","), _
    "' UNION ALL SELECT '" & commonNumber & "','")

    sql = "INSERT TKT_ASSIST(TicketID, EmployeeID) " & _
    preStatement & postStatement & "'"

    response.write sql
    %>

    commonNumber = request.form("whateverYields_15167")
    multipleNumbers = request.form("whateverYields_200,300")

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/


    "middletree" <> wrote in message
    news:...
    > Well, I was trying to figure out how to break up the string 200,300 into
    > two
    > different values (Actually, n different values). Not being a strong array
    > guy, I wanted to avoid it. But now it looks like I'll have to do as much
    > research for that as for this method you select, so I might as well use
    > the
    > array.
    >
    > I know that's basic VBScript, so I have no right to ask for help with that
    > code, but if you're bored, then feel free to offer some sample code. If
    > not,
    > I'll try and figure it out.
    >
    > thanks
     
    Aaron Bertrand [MVP], Apr 30, 2004
    #4
  5. Sorry I got the order of that post messed up. If you're having trouble
    following, let me know.

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/
     
    Aaron Bertrand [MVP], Apr 30, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UCBvZiBEaHVtcA==?=

    Data repeating 2 times from .CSV file. (Previously posted in the s

    =?Utf-8?B?UCBvZiBEaHVtcA==?=, Aug 19, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    369
    =?Utf-8?B?UCBvZiBEaHVtcA==?=
    Aug 19, 2005
  2. Replies:
    1
    Views:
    275
    markspace
    Apr 10, 2013
  3. Bob Rashkin
    Replies:
    5
    Views:
    85
    Dennis Lee Bieber
    Dec 23, 2013
  4. Steven D'Aprano
    Replies:
    0
    Views:
    117
    Steven D'Aprano
    Dec 23, 2013
  5. Replies:
    3
    Views:
    98
    Gary Herron
    Dec 23, 2013
Loading...

Share This Page