Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 5th, 2003, 05:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default Multiple value insert

How can insert multiple value into sql server by the select option ?
multiple value (software) received from the page 1

Page 1
----------
<SELECT id=software name=software MULTIPLE SIZE="10"><OPTION selected value="">Select Software</OPTION>
<%
ssql="select distinct software from softwarelist2 order by software"

set rs=cn.Execute(ssql)
do while rs.eof=false
Response.Write("<option value='" & rs("software") & "'>")
Response.Write(rs("software") )
rs.movenext
loop
%>
</SELECT>

page 2
-----------
software=Request.Form("software")

software variable received multiple value ie. Microsoft xp, Microsoft 2000, visual studio, etc.
it should be insert ie.

Stn no software
------- ----------------
1 microsoft xp
2 microsoft 2000
3 visual studio


how can insert multiple value into sql server by following insert coding. ?
it insert in one raw.
It is separate value, and it should be save separate field.

isql1="insert into software2 (stn_no,software) values ("
isql2="'" & stn_no & "',"
isql3="'" & software & "')"

isql=isql1+isql2+isql3

cn.Execute isql

Please help

Mateen
 
Old October 6th, 2003, 02:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 111
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If Request.Form("software").Count > 0 then

For i = 0 to Request.Form("software").Count - 1

' Validate input before doing the next line!!!
strSQL = "INSERT INTO myTable (StnNo, Software) VALUES (" & i + 1 & ", '" & Request.Form("Software")(i) & "')"
objConn.Execute strSQL,,adCmdText+adExecuteNoRecords

Next

End If


HTH

Cheers
Ken

Microsoft MVP - Windows Server (IIS)
www.adOpenStatic.com
 
Old October 6th, 2003, 02:52 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Subkeys are the way around this. Just use software=Request.Form("software")(1) to get the 1st item the user selected, or software=Request.Form("software")(2) to get the 2nd item etc.

Request.Form("software").Count will tell you how many items the user selected, so you can use this to control a loop, for example
For n = 1 to Request.Form("software").Count
  '... build the insert statements using Request.Form("software")(n),
  ' separate each one with a ; at the end
Next
'execute all the inserts at once
 
Old October 7th, 2003, 12:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks.

Quote:
quote:Originally posted by KenSchaefer
 If Request.Form("software").Count > 0 then

For i = 0 to Request.Form("software").Count - 1

' Validate input before doing the next line!!!
strSQL = "INSERT INTO myTable (StnNo, Software) VALUES (" & i + 1 & ", '" & Request.Form("Software")(i) & "')"
objConn.Execute strSQL,,adCmdText+adExecuteNoRecords

Next

End If


HTH

Cheers
Ken

Microsoft MVP - Windows Server (IIS)
www.adOpenStatic.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
insert value into multiple table AzlanAziz SQL Language 5 January 12th, 2007 06:01 AM
How t o insert multiple values qazi_nomi Access ASP 2 May 3rd, 2005 12:45 AM
Multiple Insert tsimsha Classic ASP Basics 1 January 19th, 2005 07:48 AM
Multiple Insert Loop HeadMelt Classic ASP Professional 1 November 7th, 2004 09:26 PM
Insert multiple records mateenmohd Access 13 November 2nd, 2004 09:15 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.