{"id":10207,"date":"2017-04-11T17:36:17","date_gmt":"2017-04-11T09:36:17","guid":{"rendered":"http:\/\/www.vbtutor.net\/?page_id=10207"},"modified":"2018-06-22T15:16:54","modified_gmt":"2018-06-22T07:16:54","slug":"visual-basic-2017-lesson-35-creating-connection-databases","status":"publish","type":"page","link":"https:\/\/www.vbtutor.net\/index.php\/visual-basic-2017-lesson-35-creating-connection-databases\/","title":{"rendered":"Visual Basic 2017 Lesson 35: Creating Connection in a Database"},"content":{"rendered":"<h4 style=\"text-align: center;\"><a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2017-lesson-34-creating-databases\/\">[Lesson 34] <\/a>&lt;&lt; <a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2017-tutorial\/\">[Contents] <\/a>&gt;&gt; <a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2017-lesson-36-browsing-editing-data\/\">[Lesson 36]<\/a><\/h4>\n<h3>35.1 Creating the Connection to a Database using ADO.NET<\/h3>\n<p>In Visual Basic 2017, we need to create a connection to a database before we can access its data. Before we begin, let\u2019s create a new database. Since we are using SQL Server 2016 as the database engine, we will use <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssms\/download-sql-server-management-studio-ssms\">SQL Server Management Studio (SSMS)<\/a>\u00a0to create a database with the mdf extension. We shall name this database file as test.mdf. After creating the database, build a table called Contacts and 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.<br \/>\n<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=\"3393818013\"><\/ins><br \/>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script><br \/>\nNow we are ready to connect to this new database.ADO.NET offers a number of connection objects such as <strong>OleDbConnection<\/strong>, <strong>SqlConnection<\/strong> and more. OleDbConnection is used to access OLEDB data such as Microsoft Access whilst SqlCOnnection 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>To initialize the variable to a new SqlConnection object, we use the following syntax:<\/p>\n<p><strong>Private MyCn As New SqlConnection<\/strong><\/p>\n<p>Having created the instance of the SqlConnecton object, the next step is to establish a connection to the data source using the <strong>SQL ConnectionString<\/strong> property. The syntax is:<\/p>\n<p><strong>MyCn.ConnectionString<\/strong> = \u201c<span style=\"color: #993366;\">Data Source=Data Source=.\\SQLEXPRESS; AttachDbFilename=C:Documents\\vb2017\\data\\Test.mdf; \u201d &amp; _<\/span><br \/>\n<span style=\"color: #993366;\"> \u201cUser Instance=True;Integrated Security=True;\u00a0Connection Timeout=30;&#8221;\u00a0<\/span><\/p>\n<p>You need to change the reference to the SQL server as well as the path to database file <em>Test.mdf <\/em>in your local drive. After establishing the connection to the database, you can open the database using the following syntax:<br \/>\n<strong>MyCn.Open()<\/strong><br \/>\n<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=\"3393818013\"><\/ins><br \/>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script><\/p>\n<h3>35.2 Populating Data in ADO.NET<\/h3>\n<p>Establishing a connection to a database in Visual Basic 2017 using SqlConnection alone will not present anything tangible things to the user to manipulate the data until we add more relevant objects and write relevant codes to the project.<\/p>\n<p>The next step is to create an instance of the<strong> SqlDataAdpater<\/strong> in our code so that we can populate the DataTable with data from the data source. Besides, you also need to create an instance of the <strong>DataTable<\/strong>. Other than that, you should also create an instance of the <strong>SqlCommandBuilder<\/strong> which is used to manipulate data such as updating and deleting data in the data table and send the changes back to the data source. The statements are:<\/p>\n<p><strong>Private MyDatAdp As New SqlDataAdapter<\/strong><br \/>\n<strong> Private MyCmdBld As New SqlCommandBuilder<\/strong><br \/>\n<strong> Private MyDataTbl As New DataTable<\/strong><\/p>\n<p>Besides that, we need to declare a variable to keep track of the user\u2019s current row within the data table. The statement is<\/p>\n<p><strong>Private MyRowPosition As Integer = 0<\/strong><\/p>\n<p>Having created the above of objects, you 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: 100%;\">MyDatAdp = New SqlDataAdapter(\u201cSelect* from Contacts\u201d, MyCn)\r\nMyCmdBld = New SqlCommandBuilder(MyDatAdp)\r\n MyDatAdp.Fill(MyDataTbl)\r\n<\/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. We 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: 100%;\">Dim MyDataRow As DataRow = MyDataTbl.Rows(0)\r\nDim strName As String\r\nDim strState As String\r\nstrName = MyDataRow(\u201cContactName\u201d)\r\nstrState = MyDataRow(\u201cState\u201d)\r\ntxtName.Text = strName.ToString\r\ntxtState.Text = strState.ToStringMe.showRecords()\r\n<\/pre>\n<p>* The two fields being referenced here are ContactName and State. Note Index 0 means first row.<\/p>\n<p>showRecords() is a sub procedure created to show data in the text boxes. The code is as follows:<\/p>\n<pre style=\"font-size: 110%;\">Private Sub showRecords()\r\nIf MyDataTbl.Rows.Count = 0 Then\r\ntxtName.Text = \u201c\u201d\r\ntxtState.Text = \u201c\u201d\r\nExit Sub\r\nEnd If\r\ntxtName.Text = MyDataTbl.Rows(MyRowPosition)(\u201cContactName\u201d).ToString()\r\ntxtState.Text = MyDataTbl.Rows(MyRowPosition)(\u201cState\u201d).ToString()\r\n\r\nEnd Sub\r\n<\/pre>\n<h4><strong>The Code<\/strong><\/h4>\n<pre style=\"font-size: 100%;\">Public Class Form1\r\nPrivate MyDatAdp As New SqlDataAdapter Private MyCmdBld As New SqlCommandBuilder Private MyDataTbl As New DataTable Private MyCn As New SqlConnection Private MyRowPosition As Integer = 0\r\nPrivate Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load\r\n MyCn.ConnectionString = \"Data Source=TOSHIBA-PC\\SQL2012; AttachDbFilename=C:\\Program Files\\Microsoft SQL \r\n Server\\MSSQL11.SQL2012\\MSSQL\\DATA\\Test.mdf; \"&amp;_ \u201cUser Instance=True;Integrated Security=SSPI\u201d\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\n Dim MyDataRow As DataRow = MyDataTbl.Rows(0)\r\n Dim strName As String\r\n Dim 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\nIf MyDataTbl.Rows.Count = 0 Then\r\ntxtName.Text = \"\"\r\ntxtState.Text = \"\"\r\n Exit Sub\r\n End If\r\n txtName.Text = MyDataTbl.Rows(MyRowPosition)(\"ContactName\").ToString()\r\n TxtState.Text = MyDataTbl.Rows(MyRowPosition)(\"State\").ToString()\r\n End Sub\r\n End Class\r\n<\/pre>\n<h4>The output interface<\/h4>\n<p style=\"text-align: center;\"><a href=\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2014\/02\/vb2013_figure34.1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5106\" src=\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2014\/02\/vb2013_figure34.1.jpg\" alt=\"vb2013_figure34.1\" width=\"300\" height=\"300\" \/><\/a><strong>Figure 35.1<\/strong><\/p>\n<p style=\"text-align: left;\">We shall discuss how to navigate the database and manipulate data in next lesson.<\/p>\n<p><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=\"9961025909\"><\/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-2017-lesson-34-creating-databases\/\">[Lesson 34]\u00a0<\/a>&lt;&lt;\u00a0<a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2017-tutorial\/\">[Contents]\u00a0<\/a>&gt;&gt;\u00a0<a href=\"http:\/\/www.vbtutor.net\/index.php\/visual-basic-2017-lesson-36-browsing-editing-data\/\">[Lesson 36]<\/a><\/h4>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>[Lesson 34] &lt;&lt; [Contents] &gt;&gt; [Lesson 36] 35.1 Creating the Connection to a Database using ADO.NET In Visual Basic 2017, we need to create a connection to a database before we can access its data. Before we begin, let\u2019s create a new database. Since we are using SQL Server 2016 as the database engine, we &hellip; <a href=\"https:\/\/www.vbtutor.net\/index.php\/visual-basic-2017-lesson-35-creating-connection-databases\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Visual Basic 2017 Lesson 35: Creating Connection in a 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-10207","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 2017 Lesson 35: Creating Connection in a Database - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB<\/title>\n<meta name=\"description\" content=\"Learn how to create connection in a database in visual basic 2017\" \/>\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\/vb2017\/vb2017_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 2017 Lesson 35: Creating Connection in a Database - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB\" \/>\n<meta property=\"og:description\" content=\"Learn how to create connection in a database in visual basic 2017\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.vbtutor.net\/vb2017\/vb2017_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-22T07:16:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2014\/02\/vb2013_figure34.1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"300\" \/>\n\t<meta property=\"og:image:height\" content=\"300\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\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-2017-lesson-35-creating-connection-databases\/\",\"url\":\"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html\",\"name\":\"Visual Basic 2017 Lesson 35: Creating Connection in a 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\/vb2017\/vb2017_lesson35.html#primaryimage\"},\"image\":{\"@id\":\"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2014\/02\/vb2013_figure34.1.jpg\",\"datePublished\":\"2017-04-11T09:36:17+00:00\",\"dateModified\":\"2018-06-22T07:16:54+00:00\",\"description\":\"Learn how to create connection in a database in visual basic 2017\",\"breadcrumb\":{\"@id\":\"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html#primaryimage\",\"url\":\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2014\/02\/vb2013_figure34.1.jpg\",\"contentUrl\":\"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2014\/02\/vb2013_figure34.1.jpg\",\"width\":300,\"height\":300},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.vbtutor.net\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Visual Basic 2017 Lesson 35: Creating Connection in a 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 2017 Lesson 35: Creating Connection in a Database - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB","description":"Learn how to create connection in a database in visual basic 2017","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\/vb2017\/vb2017_lesson35.html","og_locale":"en_US","og_type":"article","og_title":"Visual Basic 2017 Lesson 35: Creating Connection in a Database - Learn Visual Basic Programming \u2013 VB.NET, VBA &amp; Classic VB","og_description":"Learn how to create connection in a database in visual basic 2017","og_url":"http:\/\/www.vbtutor.net\/vb2017\/vb2017_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-22T07:16:54+00:00","og_image":[{"width":300,"height":300,"url":"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2014\/02\/vb2013_figure34.1.jpg","type":"image\/jpeg"}],"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-2017-lesson-35-creating-connection-databases\/","url":"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html","name":"Visual Basic 2017 Lesson 35: Creating Connection in a 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\/vb2017\/vb2017_lesson35.html#primaryimage"},"image":{"@id":"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html#primaryimage"},"thumbnailUrl":"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2014\/02\/vb2013_figure34.1.jpg","datePublished":"2017-04-11T09:36:17+00:00","dateModified":"2018-06-22T07:16:54+00:00","description":"Learn how to create connection in a database in visual basic 2017","breadcrumb":{"@id":"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html#primaryimage","url":"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2014\/02\/vb2013_figure34.1.jpg","contentUrl":"https:\/\/www.vbtutor.net\/wordpress\/wp-content\/uploads\/2014\/02\/vb2013_figure34.1.jpg","width":300,"height":300},{"@type":"BreadcrumbList","@id":"http:\/\/www.vbtutor.net\/vb2017\/vb2017_lesson35.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.vbtutor.net\/"},{"@type":"ListItem","position":2,"name":"Visual Basic 2017 Lesson 35: Creating Connection in a 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\/10207","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=10207"}],"version-history":[{"count":32,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/pages\/10207\/revisions"}],"predecessor-version":[{"id":13012,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/pages\/10207\/revisions\/13012"}],"wp:attachment":[{"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/media?parent=10207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/categories?post=10207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vbtutor.net\/index.php\/wp-json\/wp\/v2\/tags?post=10207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}