Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access ASP Using ASP with Microsoft Access databases. For Access questions not specific to ASP, please use the Access forum. For more ASP forums, please see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access ASP 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
  #1 (permalink)  
Old March 7th, 2005, 02:03 AM
Friend of Wrox
Points: 1,288, Level: 14
Points: 1,288, Level: 14 Points: 1,288, Level: 14 Points: 1,288, Level: 14
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2004
Location: Stockholm, Sweden
Posts: 331
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to qazi_nomi
Default How t o insert multiple values

I have a data base in access while using asp. I am facing the problem which is that I have to insert multiple values in one feild using one insert statement.

Actualy I have 3 tables 1.Customer, 2. Services, 3.Customer-Services
The third table have 2 feilds Customer-Id and Service-Id. When any customer subscribe for any service it will be inserted in third table but how can we insert it while customer can subscribe for more than one serive at a time. Please help me as I am disturb due to this.
Thnax in advance



(*_*)

Numan
--------------------------------------------------
It is not important what you get But important is how you got it
__________________
(*_*)

Numan
--------------------------------------------------
It is not important what you get But important is  how you got it
  #2 (permalink)  
Old May 2nd, 2005, 06:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You'll need to be thinking fairly creatively about how you make the interface for it, but the theory is pretty simple.
Here's a quick example of how you might like to do it.
Have a form with checkboxes which are dynamically created, then on submission, it goes through all those checkboxes.
Code:
<form method=post action="<% Request.ServerVariables("ScriptName") %>">
<% 
'Grab the customer ID from a queryString
CustomerID = Request.QueryString("CustomerID")
Set Rs = Server.CreateObject("ADODB.RecordSet")
sSQl = "SELECT ServiceID, ServiceDescription FROM tblService ORDER BY ServiceDescription"        
Rs.Open sSQL, sConnStr, 3, 1

If Not Rs.EOF Then
   aServiceList = Rs.GetRows()
    For iRowLoop=0 To UBound(aServiceList, 2) %>
  <label for="chk<% =aServiceList(1, iRowLoop) %>" >
  <% =aServiceList(1, iRowLoop) %></label>
  <input type="checkbox" name="chk<% =aService(1, iRowLoop) %>" id="chk<% =aService(1, iRowLoop) %>" value="True">
  <br>
  <%            Next 'iRowLoop 

End If    
Rs.Close
Set Rs = Nothing

%>
  <input type="hidden" name="ID" value="<% =CustomerID %>">
  <input type="submit" value="Edit" name="CService"></form>
<%
If Not IsEmpty(Request.Form("CService")) Then
  Dim f
        For Each f in Request.Form
            If Left(f,3) = "chk" And Request.Form(f) = "True" Then
                Rs.Open "SELECT ServiceID FROM tblService WHERE name='" & Mid(f, 4) & "'", sConnStr, 3, 1
                If Not Rs.EOF Then
                    iTemp = Rs(0)
                    Rs.Close
                    Rs.Open "tblCustomerServices", sConnStr, 1, 3, &H0002
                    Rs.AddNew
                    Rs("ServiceID") = iTemp
                    Rs("CustomerID") = sID
                    Rs.Update
                End If
                Rs.Close
            End If
        Next
%>
Naturally, I haven't tested this code, so there's bound to be an error or two in it

Notice also, that this will not display the services the customer has subscribed to.
To do that, you'd need the initial query to also return whether or not there's already a value.
That's a fairly intricate query, however. Using UNIONS, and the like.
If you want it, let me know.

Steven

I am a loud man with a very large hat. This means I am in charge
  #3 (permalink)  
Old May 3rd, 2005, 12:45 AM
Friend of Wrox
 
Join Date: Apr 2005
Location: Cochin, , India.
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I feel you can create a coma seperated list the check list(serviceID),
so that you can use a Query something like this :
Code:
 "INSERT INTO Customer-Services " 
& " SELECT " & CustmerID & " , ServicesID " 
& " FROM Services WHERE ServicesID IN (" & ComaSerpatedList & ")"





Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple values insert into one field mateenmohd Classic ASP Basics 0 May 29th, 2007 02:11 AM
insert multiple records into a table from values Deepak Chauhan Oracle 3 May 12th, 2006 10:35 PM
insert multiple checkbox values in to database muthumari_p2003@yahoo.com Pro JSP 0 March 29th, 2006 08:23 AM
Multiple Insert tsimsha Classic ASP Basics 1 January 19th, 2005 07:48 AM
Multiple value insert mateenmohd SQL Server 2000 3 October 7th, 2003 12:55 AM





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