How t o insert multiple values

qazi_nomi · March 7th, 2005, 02:03 AM

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

Steven · May 2nd, 2005, 06:46 PM

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

Prashant.k.m · May 3rd, 2005, 12:45 AM

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 & ")"