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:
My idea was to utilize a repeater to format the information such as this:
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.