{"id":3608,"date":"2013-07-15T10:04:36","date_gmt":"2013-07-15T02:04:36","guid":{"rendered":"http:\/\/www.vbtutor.net\/?page_id=3608"},"modified":"2018-06-24T18:26:36","modified_gmt":"2018-06-24T10:26:36","slug":"visual-basic-2012-lesson-30-working-with-databases-part-2","status":"publish","type":"page","link":"https:\/\/www.vbtutor.net\/index.php\/visual-basic-2012-lesson-30-working-with-databases-part-2\/","title":{"rendered":"Visual Basic 2012 Lesson 30- Working with ADO.NET"},"content":{"rendered":"<h4 style=\"text-align: center;\"><strong><a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2012-lesson-29-working-with-databases-part-1\/\">[Lesson 29]<\/a>\u00a0<\/strong>&lt;&lt;\u00a0<strong><a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2012-tutorial\/\">[CONTENTS]<\/a>\u00a0&gt;&gt;<a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2012-lesson-31-working-with-databases-part-3\/\">[Lesson 31]<\/a><a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2010-lesson-30\/\"><br \/>\n<\/a><\/strong><\/h4>\n<h2><strong>30.1 Creating Connection to a Database using ADO.NET\u00a0<\/strong><\/h2>\n<p>In Visual Basic 2012, we need to create a connection to a database before we can access its data. Before we begin, let&#8217;s create a new database. Since we are using SQL Server 2012 as the database engine, we shall use <strong>Microsoft Studio Management Express<\/strong> to create a database with the mdf extension. We shall name this database file test.mdf.<\/p>\n<p>After creating the database, build a table called Contacts. Subsequently, we create two fields and name them ContactName and State respectively. Enter a few data in the table and click Save All to save the data. Now we are ready to connect to this new database. ADO.NET offers a number of connection objects such as\u00a0<strong>OleDbConnection, SqlConnection<\/strong>\u00a0and more. OleDbConnection is used to access OLEDB data such as Microsoft Access. \u00a0SqlCOnnection is used to access data provided by Microsoft SQL Server. Since we will work with SQL database in our example, we will use the SqlConnection object.<\/p>\n<p><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br \/>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"1777484012\"><\/ins><br \/>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script><\/p>\n<p>To initialize the variable to a new SqlConnection object, we use the following syntax:<\/p>\n<pre style=\"font-size: 110%; width: 70%;\">Private MyCn As New SqlConnection<\/pre>\n<p>Having created the instance of the SqlConnecton object, the next step is to establish a connection to the data source using the \u00a0<strong>SQL\u00a0ConnectionString<\/strong>\u00a0property. The syntax is:<\/p>\n<pre style=\"font-size: 110%;\">MyCn.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\"<\/pre>\n<p>* You need to change the reference to the SQL server (TOSHIBA-PC\\SQL2012)\u00a0as well as the path to database file <em>Test.mdf .<\/em><\/p>\n<p>After establishing the connection to the database, you can open the database using the following syntax:<\/p>\n<pre style=\"font-size: 110%; width: 70%;\">MyCn.Open()<\/pre>\n<h2><strong>\u00a030.2 Populating Data in ADO.NET<\/strong><\/h2>\n<p>The next step is to create an instance of the <strong>SqlDataAdpater<\/strong> in our visual Basic 2012 code to\u00a0populate the <strong>DataTable<\/strong> with data from the data source. Besides, you also need to create an instance of the DataTable. Other than that, you should also create an instance of the <strong>SqlCommandBuilder<\/strong>\u00a0which is used to manipulate data such as updating and deleting data in the Datatable and send the changes back to the data source.\u00a0The statements are:<\/p>\n<pre style=\"font-size: 110%; width: 70%;\">Private MyDatAdp As New SqlDataAdapter\r\nPrivate MyCmdBld As New SqlCommandBuilder\u00a0\r\nPrivate MyDataTbl As New DataTable<\/pre>\n<p>Besides that, we need to declare a variable to keep track of the user&#8217;s current row within the data table. The statement is<\/p>\n<pre style=\"font-size: 110%; width: 70%;\">\u00a0Private MyRowPosition As Integer = 0<\/pre>\n<p>Having created the above of objects, \u00a0you need to include the following statements in the Sub Form_Load event to start filling the DataTable with data from the data source. The statements are as follows:<\/p>\n<pre style=\"font-size: 110%;\">MyDatAdp = New SqlDataAdapter(\"Select* from Contacts\", MyCn)\r\nMyCmdBld = New SqlCommandBuilder(MyDatAdp)\r\nMyDatAdp.Fill(MyDataTbl)<\/pre>\n<p>After filling up the DataTable , we need to write code to access the data. To access data in the DataTable means that we need to access the rows in the table. \u00a0We can achieve this by using the DataRow object. For example, we can write the following to access the first row of the table and present the data via two text boxes with the name txtName and txtState respectively:<\/p>\n<pre style=\"font-size: 110%; width: 80%;\">Dim 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.ToStringMe.showRecords()\r\n<\/pre>\n<p><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br \/>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"1777484012\"><\/ins><br \/>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script><\/p>\n<p>* The two fields being referenced here are ContactName and State. Note Index 0 means first row.<\/p>\n<pre style=\"font-size: 110%; width: 70%;\">showRecords()<\/pre>\n<p>is a sub procedure created to show data in the textboxes? The code is as follows:<\/p>\n<pre style=\"font-size: 110%;\">Private Sub showRecords()\r\nIf MyDataTbl.Rows.Count = 0 Then\r\n txtName.Text = \"\"\r\n txtState.Text = \"\"\r\n Exit Sub\r\nEnd If\r\n\r\n txtName.Text = MyDataTbl.Rows(MyRowPosition)(\"ContactName\").ToString\r\n txtState.Text = MyDataTbl.Rows(MyRowPosition)(\"State\").ToString\r\n\r\nEnd Sub\r\n<\/pre>\n<p>Take a look at the output interface:<\/p>\n<p><a href=\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3610\" src=\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg\" alt=\"vb2012_fig30\" width=\"341\" height=\"305\" \/><\/a><\/p>\n<p>We will discuss how to manipulate data in the next visual basic 2012 lesson.<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=\"6598395509\"><\/ins><br \/>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script><\/p>\n<h4 style=\"text-align: center;\"><strong><a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2012-lesson-29-working-with-databases-part-1\/\">[Lesson 29]<\/a>\u00a0<\/strong>&lt;&lt;\u00a0<strong><a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2010-tutorial\/\">[CONTENTS]<\/a>&gt;&gt;\u00a0<a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2012-lesson-31-working-with-databases-part-3\/\">[Lesson 31]<\/a><\/strong><\/h4>\n","protected":false},"excerpt":{"rendered":"<p>[Lesson 29]\u00a0&lt;&lt;\u00a0[CONTENTS]\u00a0&gt;&gt;[Lesson 31] 30.1 Creating Connection to a Database using ADO.NET\u00a0 In Visual Basic 2012, we need to create a connection to a database before we can access its data. Before we begin, let&#8217;s create a new database. Since we are using SQL Server 2012 as the database engine, we shall use Microsoft Studio Management &hellip; <a href=\"https:\/\/www.vbtutor.net\/index.php\/visual-basic-2012-lesson-30-working-with-databases-part-2\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Visual Basic 2012 Lesson 30- Working with ADO.NET<\/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-3608","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 2012 Lesson 30- Working with ADO.NET - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB<\/title>\n<meta name=\"description\" content=\"This article explains how to create database application in Visual Basic 2012\" \/>\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\/vb2012\/vb2012_Lesson30.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Visual Basic 2012 Lesson 30- Working with ADO.NET - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB\" \/>\n<meta property=\"og:description\" content=\"This article explains how to create database application in Visual Basic 2012\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.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-24T10:26:36+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=\"3 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-2012-lesson-30-working-with-databases-part-2\/\",\"url\":\"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.html\",\"name\":\"Visual Basic 2012 Lesson 30- Working with ADO.NET - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB\",\"isPartOf\":{\"@id\":\"https:\/\/www.vbtutor.net\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.html#primaryimage\"},\"image\":{\"@id\":\"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.html#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg\",\"datePublished\":\"2013-07-15T02:04:36+00:00\",\"dateModified\":\"2018-06-24T10:26:36+00:00\",\"description\":\"This article explains how to create database application in Visual Basic 2012\",\"breadcrumb\":{\"@id\":\"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.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\/vb2012\/vb2012_Lesson30.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.vbtutor.net\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Visual Basic 2012 Lesson 30- Working with ADO.NET\"}]},{\"@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 2012 Lesson 30- Working with ADO.NET - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB","description":"This article explains how to create database application in Visual Basic 2012","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\/vb2012\/vb2012_Lesson30.html","og_locale":"en_US","og_type":"article","og_title":"Visual Basic 2012 Lesson 30- Working with ADO.NET - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB","og_description":"This article explains how to create database application in Visual Basic 2012","og_url":"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.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-24T10:26:36+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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.vbtutor.net\/index.php\/visual-basic-2012-lesson-30-working-with-databases-part-2\/","url":"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.html","name":"Visual Basic 2012 Lesson 30- Working with ADO.NET - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB","isPartOf":{"@id":"https:\/\/www.vbtutor.net\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.html#primaryimage"},"image":{"@id":"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.html#primaryimage"},"thumbnailUrl":"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2013\/07\/vb2012_fig30.jpg","datePublished":"2013-07-15T02:04:36+00:00","dateModified":"2018-06-24T10:26:36+00:00","description":"This article explains how to create database application in Visual Basic 2012","breadcrumb":{"@id":"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.vbtutor.net\/vb2012\/vb2012_Lesson30.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\/vb2012\/vb2012_Lesson30.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.vbtutor.net\/"},{"@type":"ListItem","position":2,"name":"Visual Basic 2012 Lesson 30- Working with ADO.NET"}]},{"@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\/3608","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=3608"}],"version-history":[{"count":40,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/pages\/3608\/revisions"}],"predecessor-version":[{"id":13122,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/pages\/3608\/revisions\/13122"}],"wp:attachment":[{"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/media?parent=3608"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/categories?post=3608"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/tags?post=3608"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}