inserting more than one value--previously posted elsewhere

[middletree]

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?

 

[Bob Barrows [MVP]]

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

 

[middletree]

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]]

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 [MVP]]

Sorry I got the order of that post messed up. If you're having trouble
following, let me know.