{"id":5116,"date":"2014-02-01T14:44:05","date_gmt":"2014-02-01T06:44:05","guid":{"rendered":"http:\/\/www.vbtutor.net\/?page_id=5116"},"modified":"2018-06-24T17:27:34","modified_gmt":"2018-06-24T09:27:34","slug":"visual-basic-2013-lesson-35-working-databases-browsing-editing","status":"publish","type":"page","link":"https:\/\/www.vbtutor.net\/index.php\/visual-basic-2013-lesson-35-working-databases-browsing-editing\/","title":{"rendered":"Visual Basic 2013 Lesson 35:  Browsing and Editing Database"},"content":{"rendered":"<h4 style=\"text-align: center;\"><a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2013-lesson-34-working-databases-creating-connection\/\">[Lesson 34]<\/a> &lt;&lt; <a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2013-tutorial\/\">[Contents]<\/a>&gt;&gt;<a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2013-made-easy\/\">[Next]<\/a><\/h4>\n<p>In preceding lessons, you have learned how to connect to a database as well as filling up the table with data in Visual Basic 2013, now you shall learn how to manipulate the data in the database. Manipulating data means adding news records, editing records, deleting records, browsing records and more.<\/p>\n<h3><strong>35.1 Browsing Records\u00a0<\/strong><\/h3>\n<p>In the previous lesson, we have learned how to display the first record using the showRecords sub procedure. \u00a0In this lesson, we will create command buttons and write relevant codes to allow the user to browse the records forward and backward as well as fast forward to the last record and back to the first record.The first button we need to create is for the user to browse the first record. We can use button\u2019s text\u00a0<strong>&lt;&lt;\u00a0<\/strong>to indicate to the user that it is the button to move to the first record and button\u2019s text <strong>&gt;&gt;<\/strong> to move to the last record. \u00a0Besides we can use button\u2019s text <strong>&lt;<\/strong> \u00a0for moving to previous record and button\u2019s text &gt; \u00a0for moving to next record.<\/p>\n<p>The code for moving to the first record is:<\/p>\n<pre style=\"font-size: 110%;\">MyRowPosition = 0\r\nMe.showRecords()\r\n<\/pre>\n<p>The code for moving to the previous record is:<\/p>\n<pre style=\"font-size: 110%;\">If MyRowPosition &gt; 0 Then\r\nMyRowPosition = MyRowPosition \u2013 1\r\nMe.showRecords()\r\nEnd If\r\n<\/pre>\n<p><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br \/>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-3033628290023372\"\n     data-ad-slot=\"3914691604\"><\/ins><br \/>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script><br \/>\nThe code for moving to next record is:<\/p>\n<pre style=\"font-size: 110%;\">If MyRowPosition &lt; (MyDataTbl.Rows.Count \u2013 1) Then\r\nMyRowPosition = MyRowPosition + 1\r\nMe.showRecords()\r\nEnd If<\/pre>\n<p>The code for moving to the last record is:<\/p>\n<pre style=\"font-size: 110%;\">If MyDataTbl.Rows.Count &gt;0 Then\r\n MyRowPosition = MyDataTbl.Rows.Count \u2013 1\r\n Me.showRecords()\r\nEnd If<\/pre>\n<h3><strong>35.2 \u00a0Editing, Saving, Adding and Deleting Records<\/strong><\/h3>\n<p>You can edit any record by navigating to the record and change the data values. However, you need to save the data after editing them. You need to use the update method of the SqlDataAdapter to save the data. The code \u00a0is:<\/p>\n<pre style=\"font-size: 110%;\">If MyDataTbl.Rows.Count &lt;&gt; 0 Then\r\n MyDataTbl.Rows(MyRowPosition)(\u201cContactName\u201d) = txtName.Text\r\n MyDataTbl.Rows(MyRowPosition)(\u201cstate\u201d) = txtState.Text\r\n MyDatAdp.Update(MyDataTbl)\r\nEnd If\r\n<\/pre>\n<p>You can also add a new record or new row to the table using the following code:<\/p>\n<pre style=\"font-size: 110%;\">Dim MyNewRow As DataRow = MyDataTbl.NewRow()\r\n MyDataTbl.Rows.Add(MyNewRow)\r\n MyRowPosition = MyDataTbl.Rows.Count \u2013 1\r\n Me.showRecords()\r\n<\/pre>\n<p><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br \/>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-3033628290023372\"\n     data-ad-slot=\"3914691604\"><\/ins><br \/>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script><\/p>\n<p>The code above will present a new record with blank fields for the user to enter the new data. After entering the data, he or she can then click the save button to save the data.<\/p>\n<p>Lastly, the user might want to delete the data. The code to delete the data is:<\/p>\n<pre style=\"font-size: 110%;\">If MyDataTbl.Rows.Count &lt;&gt;0 Then\r\nMyDataTbl.Rows(MyRowPosition).Delete()\r\nMyDatAdp.Update(MyDataTbl)\r\nMyRowPosition = 0\r\nMe.showRecords()\r\nEnd If\r\n<\/pre>\n<p>The Visual Basic 2013 database program interface is shown below:<\/p>\n<p><strong>The Code<\/strong><\/p>\n<pre style=\"font-size: 110%;\">Public Class Form1\r\nPrivate MyDatAdp As New SqlDataAdapter\r\nPrivate MyCmdBld As New SqlCommandBuilder\r\nPrivate MyDataTbl As New DataTable\r\nPrivate MyCn As New SqlConnection\r\nPrivate MyRowPosition As Integer = 0\r\n\r\nPrivate Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed\r\n MyCn.Close()\r\n MyCn.Dispose()\r\nEnd Sub\r\n\r\nPrivate Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load\r\nMyCn.ConnectionString = \"Data Source=TOSHIBA-PC\\SQL2012; AttachDbFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL11.SQL2012\\MSSQL\\DATA\\Test.mdf; \" &amp; _\r\n\"User Instance=True;Integrated Security=SSPI\"\r\n MyCn.Open()\r\n MyDatAdp = New SqlDataAdapter(\"Select* from Contacts\", MyCn)\r\n MyCmdBld = New SqlCommandBuilder(MyDatAdp)\r\n MyDatAdp.Fill(MyDataTbl)\r\n\r\nDim MyDataRow As DataRow = MyDataTbl.Rows(0)\r\nDim strName As String\r\nDim strState As String\r\n strName = MyDataRow(\"ContactName\")\r\n strState = MyDataRow(\"State\")\r\n TxtName.Text = strName.ToString\r\n TxtState.Text = strState.ToString\r\n Me.showRecords()\r\nEnd Sub\r\n\r\nPrivate Sub showRecords()\r\n If MyDataTbl.Rows.Count = 0 Then\r\n  txtName.Text = \"\"\r\n  txtState.Text = \"\"\r\n Exit Sub\r\nEnd If\r\n txtName.Text = MyDataTbl.Rows(MyRowPosition)(\"ContactName\").ToString\r\n TxtState.Text = MyDataTbl.Rows(MyRowPosition)(\"State\").ToString\r\nEnd Sub\r\n\r\nPrivate Sub BtnMoveFirst_Click(sender As Object, e As EventArgs) Handles BtnMoveFirst.Click\r\n MyRowPosition = 0\r\n Me.showRecords()\r\nEnd Sub\r\n\r\nPrivate Sub BtnMovePrev_Click(sender As Object, e As EventArgs) Handles BtnMovePrev.Click\r\n If MyRowPosition &gt; 0 Then\r\n  MyRowPosition = MyRowPosition - 1\r\n  Me.showRecords()\r\nEnd If\r\nEnd Sub\r\n\r\nPrivate Sub BtnMoveNext_Click(sender As Object, e As EventArgs) Handles BtnMoveNext.Click\r\n If MyRowPosition &lt; (MyDataTbl.Rows.Count - 1) Then\r\n  MyRowPosition = MyRowPosition + 1\r\n  Me.showRecords()\r\nEnd If\r\nEnd Sub\r\n\r\nPrivate Sub BtnMoveLast_Click(sender As Object, e As EventArgs) Handles BtnMoveLast.Click\r\n If MyDataTbl.Rows.Count &gt; 0 Then\r\n  MyRowPosition = MyDataTbl.Rows.Count - 1\r\n  Me.showRecords()\r\n End If\r\nEnd Sub\r\n\r\nPrivate Sub BtnAdd_Click(sender As Object, e As EventArgs) Handles BtnAdd.Click\r\nDim MyNewRow As DataRow = MyDataTbl.NewRow()\r\n MyDataTbl.Rows.Add(MyNewRow)\r\n MyRowPosition = MyDataTbl.Rows.Count - 1\r\n Me.showRecords()\r\nEnd Sub\r\n\r\nPrivate Sub BtnDelete_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnDelete.Click\r\n If MyDataTbl.Rows.Count &lt;&gt; 0 Then\r\n  MyDataTbl.Rows(MyRowPosition).Delete()\r\n  MyRowPosition = 0\r\n  MyDatAdp.Update(MyDataTbl)\r\n  Me.showRecords()\r\n End If\r\nEnd Sub\r\n\r\nPrivate Sub BtnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnSave.Click\r\n\r\n If MyDataTbl.Rows.Count &lt;&gt; 0 Then\r\n  MyDataTbl.Rows(MyRowPosition)(\"ContactName\") = TxtName.Text\r\n  MyDataTbl.Rows(MyRowPosition)(\"state\") = TxtState.Text\r\n  MyDatAdp.Update(MyDataTbl)\r\nEnd If\r\nEnd Sub\r\nEnd Class\r\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" src=\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg\" alt=\"\" width=\"341\" height=\"305\" \/><\/p>\n<p><strong>Figure 35.1<\/strong><br \/>\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br \/>\n<ins class=\"adsbygoogle\" style=\"display: block;\" data-ad-format=\"autorelaxed\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"2306771905\"><\/ins><br \/>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script><\/p>\n<h4 style=\"text-align: center;\"><a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2013-lesson-34-working-databases-creating-connection\/\">[Lesson 34]<\/a>\u00a0&lt;&lt;\u00a0<a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2013-tutorial\/\">[Contents]<\/a>\u00a0&gt;&gt;<a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2013-made-easy\/\">[Next]<\/a><\/h4>\n","protected":false},"excerpt":{"rendered":"<p>[Lesson 34] &lt;&lt; [Contents]&gt;&gt;[Next] In preceding lessons, you have learned how to connect to a database as well as filling up the table with data in Visual Basic 2013, now you shall learn how to manipulate the data in the database. Manipulating data means adding news records, editing records, deleting records, browsing records and more. &hellip; <a href=\"https:\/\/www.vbtutor.net\/index.php\/visual-basic-2013-lesson-35-working-databases-browsing-editing\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Visual Basic 2013 Lesson 35:  Browsing and Editing Database<\/span><\/a><\/p>\n","protected":false},"author":23013,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-5116","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.0 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Visual Basic 2013 Lesson 35: Browsing and Editing Database - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB<\/title>\n<meta name=\"description\" content=\"This article discuss browsing and editing a database in visual basic 2013\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Visual Basic 2013 Lesson 35: Browsing and Editing Database - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB\" \/>\n<meta property=\"og:description\" content=\"This article discuss browsing and editing a database in visual basic 2013\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html\" \/>\n<meta property=\"og:site_name\" content=\"Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/Vbtutor\" \/>\n<meta property=\"article:modified_time\" content=\"2018-06-24T09:27:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@liewvk\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.vbtutor.net\/index.php\/visual-basic-2013-lesson-35-working-databases-browsing-editing\/\",\"url\":\"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html\",\"name\":\"Visual Basic 2013 Lesson 35: Browsing and Editing Database - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB\",\"isPartOf\":{\"@id\":\"https:\/\/www.vbtutor.net\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html#primaryimage\"},\"image\":{\"@id\":\"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg\",\"datePublished\":\"2014-02-01T06:44:05+00:00\",\"dateModified\":\"2018-06-24T09:27:34+00:00\",\"description\":\"This article discuss browsing and editing a database in visual basic 2013\",\"breadcrumb\":{\"@id\":\"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html#primaryimage\",\"url\":\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg\",\"contentUrl\":\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg\",\"width\":341,\"height\":305},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.vbtutor.net\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Visual Basic 2013 Lesson 35: Browsing and Editing Database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.vbtutor.net\/#website\",\"url\":\"https:\/\/www.vbtutor.net\/\",\"name\":\"Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB\",\"description\":\"Start learning Visual Basic from beginner to advanced. Includes VB.NET, VBA, and classic VB tutorials for students and professionals.\",\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Visual Basic 2013 Lesson 35: Browsing and Editing Database - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB","description":"This article discuss browsing and editing a database in visual basic 2013","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html","og_locale":"en_US","og_type":"article","og_title":"Visual Basic 2013 Lesson 35: Browsing and Editing Database - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB","og_description":"This article discuss browsing and editing a database in visual basic 2013","og_url":"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html","og_site_name":"Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB","article_publisher":"https:\/\/www.facebook.com\/Vbtutor","article_modified_time":"2018-06-24T09:27:34+00:00","og_image":[{"url":"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_site":"@liewvk","twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.vbtutor.net\/index.php\/visual-basic-2013-lesson-35-working-databases-browsing-editing\/","url":"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html","name":"Visual Basic 2013 Lesson 35: Browsing and Editing Database - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB","isPartOf":{"@id":"https:\/\/www.vbtutor.net\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html#primaryimage"},"image":{"@id":"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html#primaryimage"},"thumbnailUrl":"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg","datePublished":"2014-02-01T06:44:05+00:00","dateModified":"2018-06-24T09:27:34+00:00","description":"This article discuss browsing and editing a database in visual basic 2013","breadcrumb":{"@id":"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html#primaryimage","url":"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg","contentUrl":"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg","width":341,"height":305},{"@type":"BreadcrumbList","@id":"http:\/\/www.vbtutor.net\/vb2013\/vb2013_Lesson35.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.vbtutor.net\/"},{"@type":"ListItem","position":2,"name":"Visual Basic 2013 Lesson 35: Browsing and Editing Database"}]},{"@type":"WebSite","@id":"https:\/\/www.vbtutor.net\/#website","url":"https:\/\/www.vbtutor.net\/","name":"Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB","description":"Start learning Visual Basic from beginner to advanced. Includes VB.NET, VBA, and classic VB tutorials for students and professionals.","inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/pages\/5116","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/users\/23013"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/comments?post=5116"}],"version-history":[{"count":29,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/pages\/5116\/revisions"}],"predecessor-version":[{"id":13089,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/pages\/5116\/revisions\/13089"}],"wp:attachment":[{"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/media?parent=5116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/categories?post=5116"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/tags?post=5116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}