Got an issue I want assist with. Essentially I am gonna create a form (a part of a larger web application) that lists a lot of clients there business contact, tech contact 1, and tech contact 2.

The concept is rapid data entry. So one form shows each client using their contacts in dropdowns and that i we are able to change each one of these then click a save button to perform a mass save.

the database appears like this:

tblClient

ClientID

ClientName

BusinessContact

Tech1

Tech2

My idea was to utilize a repeater to format the information such as this:

Client

Business Contact

Tech1

Tech2

Client2

Business Contact

Tech1

Tech2

What I am stuck on is how you can perform the mass update? Can One make a move like for every item in Repeater1 then do an update SQL statement? -- Jonesy

If you are using the GUI tools in Versus to construct a datasource with DataAdaptors and hook these right into a gridview, all of the code is written for you personally, apart from calling update in your save button.

A manual approach is always to format the information into an XML block and pass it to some saved procedure, where one can then create an UPDATE statement that JOINS towards the XML to do the update over night.

The XML you would like to create could be much like:

<ClientData>
    <Client ClientID="1" BusinessContact="..." Tech1="..." Tech2="..." />
    <Client ClientID="2" ... />
    ...
</ClientData>

One method to create this while on an XmlWriter and looping over your repeater to reap the information. In VB.Internet:

Dim stream As New System.IO.MemoryStream
Dim settings As New System.Xml.XmlWriterSettings

settings.Encoding = Encoding.GetEncoding("iso-8859-1") 'This encoding handles special characters pasted in from MS Word

Dim writer As System.Xml.XmlWriter = System.Xml.XmlWriter.Create(stream, settings)

With writer
    .WriteStartElement("ClientData")
    For Each item As RepeaterItem In myRepeater.Items
        .WriteStartElement("Client")
        .WriteAttributeString("ClientId", CType(item.FindControl("ClientIdHidden"), HtmlInputHidden).Value)
        .WriteAttributeString("BusinessContact", CType(item.FindControl("BusinessContact"), TextBox).Text)
        ...
        .WriteEndElement()
    Next
    .WriteEndElement()
    .Flush()
End With

Dim xmlString As String = writer.Settings.Encoding.GetString(stream.ToArray())

Then produce a saved method that requires a parameter where one can pass within the XML:

CREATE PROCEDURE [dbo].[BulkUpdateClients]
(
    @xmlInput AS text
)
AS

DECLARE @xmlHandle int

EXEC sp_xml_preparedocument @xmlHandle output, @xmlInput

UPDATE c
SET
    BusinessContact = x.BusinessContact,
    Tech1 = x.Tech1,
    Tech2 = x.Tech2
FROM tblClient c
    JOIN 
    (
        SELECT
            ClientId,
            BusinessContact,
            Tech1,
            Tech2
        FROM
           OPENXML (@xmlHandle, '/ClientData/Client', 1)
           WITH
           (
               ClientId int,
               BusinessContact varchar(50),
               Tech1 varchar(50),
               Tech2 varchar(50)
           )
   ) x ON c.ClientId = x.ClientId

The code above has not been examined, however i think the overall pattern can there be. I have used this method for bulk card inserts on numerous occasions. I love it since it will get the task completed in just one database operation.