{"id":69,"date":"2007-07-27T00:30:50","date_gmt":"2007-07-27T00:30:50","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?page_id=69"},"modified":"2007-07-27T00:30:50","modified_gmt":"2007-07-27T00:30:50","slug":"miisappvb-private-sub-insertdeltaobjects","status":"publish","type":"page","link":"https:\/\/www.wapshere.com\/missmiis\/code-snippets\/miisappvb-private-sub-insertdeltaobjects","title":{"rendered":"MiisApp.vb: Private Sub InsertDeltaObjects"},"content":{"rendered":"<p><font size=\"1\"><br \/>\nPrivate Sub InsertDeltaObjects(ByVal objectType As String)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim sqlQueryConnection As New SqlConnection(MIISSYNC_CONNECTION_STRING)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim instructionText As String<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If objectType = &#8220;person&#8221; Then<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 instructionText = &#8220;Notepad is about to open. Paste in your Staff IDs, one per row, then save and close the file.&#8221;<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 ElseIf objectType = &#8220;group&#8221; Then<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 instructionText = &#8220;Notepad is about to open. Paste in your Group Names, one per row, then save and close the file.&#8221;<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Else<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Exit Sub<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If<\/font><font size=\"1\">\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 &#8216;Open Notepad so the object identifiers can be pasted in<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 MsgBox(instructionText, MsgBoxStyle.ApplicationModal)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim myProcess As Process = System.Diagnostics.Process.Start(&#8220;notepad.exe&#8221;, ID_FILE)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 While Not myProcess.HasExited<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 System.Threading.Thread.Sleep(100)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End While<\/p>\n<p>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 &#8216;Read the identifiers into an array<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim myReader As New StreamReader(ID_FILE)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim ids As String()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim strLine As String<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 i = -1<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 While myReader.Peek &lt;&gt; -1<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 strLine = myReader.ReadLine<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If strLine.Length &gt; 0 Then<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 i = i + 1<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 ReDim Preserve ids(i)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 ids.SetValue(strLine, i)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End While<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 myReader.Close()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If ids Is Nothing Then<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Exit Sub<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If<\/p>\n<p>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 &#8216;Check the last log line<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim logline As String = LastLogLine()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If logline.IndexOf(&#8220;DTS&#8221;) &gt; 0 Then<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 MsgBox(&#8220;Waiting for DTS tasks to complete&#8230;&#8221;, MsgBoxStyle.Critical)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 While logline.IndexOf(&#8220;DTS&#8221;) &gt; 0<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 System.Threading.Thread.Sleep(10000)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 logline = LastLogLine()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End While<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If<\/p>\n<p>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 &#8216;Run SQL inserts<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim msgText As String = &#8220;&#8221;<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 For i = 0 To UBound(ids)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If ids(i) &lt;&gt; &#8220;&#8221; Then<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If objectType = &#8220;person&#8221; Then<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 sqlQueryConnection.Open()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim sqlPeopleInsert As New SqlCommand(&#8220;insert into people_delta select * , &#8216;Modify&#8217; from people_snapshot where staffid='&#8221; &amp; ids(i) &amp; &#8220;&#8216;&#8221;, sqlQueryConnection)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 sqlPeopleInsert.ExecuteNonQuery()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 sqlQueryConnection.Close()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 sqlQueryConnection.Open()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim sqlADMultiValueInsert As New SqlCommand(&#8220;insert into admultivalueobjects_delta values (&#8221; &amp; ids(i) &amp; &#8220;,&#8217;USER&#8217;,NULL,NULL,&#8217;Modify&#8217;,NULL)&#8221;, sqlQueryConnection)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0sqlADMultiValueInsert.ExecuteNonQuery()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0sqlQueryConnection.Close()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 sqlQueryConnection.Open()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim sqlLDAPMultiValueInsert As New SqlCommand(&#8220;insert into ldapmultivalueobjects_delta values (&#8221; &amp; ids(i) &amp; &#8220;,&#8217;USER&#8217;,&#8217;Modify&#8217;,NULL)&#8221;, sqlQueryConnection)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 sqlLDAPMultiValueInsert.ExecuteNonQuery()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 sqlQueryConnection.Close()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 msgText = msgText &amp; &#8221; &#8221; &amp; ids(i)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0ElseIf objectType = &#8220;group&#8221; Then<br \/>\n<em>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 &lt;&lt;similar to person section, with updates relevant to group objects&gt;&gt;<\/em><br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Next<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If msgText &lt;&gt; &#8220;&#8221; Then<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 MsgBox(&#8220;Delta tables updated with &#8221; &amp; msgText &amp; vbCrLf &amp; &#8220;They will be included in the next Delta Imports of SchoolDB.&#8221;,\u00c2\u00a0 MsgBoxStyle.Information)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0End If<br \/>\nEnd Sub<\/p>\n<p>Private Function LastLogLine() As String<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim logline As String<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim rowReader As SqlDataReader<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim sqlQueryConnection As New SqlConnection(MIISSYNC_CONNECTION_STRING)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 sqlQueryConnection.Open()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim sqlQuery As New SqlCommand(&#8220;select top 1 entry from &#8221; &amp; ACTIVITY_LOG &amp; &#8221; order by item desc&#8221;, sqlQueryConnection)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 rowReader = sqlQuery.ExecuteReader<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 While rowReader.Read()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 logline = rowReader.GetString(0)<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End While<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 rowReader.Close()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 sqlQueryConnection.Close()<br \/>\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 LastLogLine = logline<br \/>\nEnd Function<\/p>\n<p><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Private Sub InsertDeltaObjects(ByVal objectType As String) \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim sqlQueryConnection As New SqlConnection(MIISSYNC_CONNECTION_STRING) \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim instructionText As String \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If objectType = &#8220;person&#8221; Then \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 instructionText = &#8220;Notepad is about to open. Paste in your Staff IDs, one per row, then save and close the file.&#8221; \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 ElseIf objectType = &#8220;group&#8221; Then \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 instructionText = &#8220;Notepad&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":30,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-69","page","type-page","status-publish","hentry"],"jetpack_shortlink":"https:\/\/wp.me\/Pkp1o-17","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/69","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=69"}],"version-history":[{"count":0,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/69\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/30"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=69"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}