Skip to main content

Connecting to a Database with Visual Studio Tools

VISUAL BASIC 2010 EXPRESS & SQL SERVER 2008 EXPRESS

Two great tools that go great together 

VB.Net and SQL Server are powerful, industry-standard development tools from Microsoft.  It’s sometimes surprising to realize that anyone can download and use these programs for free.

Getting them installed and working together took longer than I expected.  Google searches showed that many people were even questioning whether a VB Express program could could access a SQL Server Express database.  It can, but there’s a trick to it.

So if you’re trying to get started with your first VB+SQL program, here’s a small app to start you off.  I hope it saves you a couple hours!
______________________________________________

First, download and install Visual Basic 2010 Express and Sql Server 2008 Express from www.microsoft.com/express. 

SQL Server

Next, start Sql Server Management Studio, and log in.
 (Start à All Programs à Microsoft SQL Server 2008 à SQL Server 2008)

Create a new database by right-clicking Databases and selecting New Database

Name the new database MyTestDB and click OK.

Expand the Databases folder to find your new database:

Click New Query and run this script to create an empty table named tblTest:

CREATE TABLE [MyTestDB].[dbo].[tblTest] (
   [tstNum_PK] [int] IDENTITY(1,1) NOT NULL ,
   [tstData] [varchar] (100) NOT NULL
) ON [PRIMARY]

Visual Basic 
Now start Visual Basic and create a new Windows Form Application named DatabaseTest. 
(Start à All Programs à Microsoft Visual Studio 2010 Express à Visual Basic 2010)

On your VB Form, add two TextBoxes and two Buttons.  For simplicity, we’ll leave the their properties at their default settings and change them in the code.


à Here’s the trick: with Visual Basic 2010 Express, you have to connect directly to the database file.  You may be used to connecting to remote database servers – you can’t do that here.  When you created MyTestDB above, SQL Server created a file on your PC named MyTestDB.mdf.  Find it, and if the full path name is different from:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MyTestDB.mdf

Then you’ll need to change two lines in this sample code to match your location.

Add the code below to your program.  Now you’re ready to run.

Start the program and type something into the first text box.  Then click Add to DB. 
Unless you see an error message, a new record was just written to your database.

To read and display all the records in this table, click Show DB.
The ‘1’ at the beginning is the Primary Key for your record – a field you generally want to include, but would rarely display.

That’s it.  If you got all the way through, you now have a working (though bare-bones) program to begin developing your own applications.  Good luck!

 
Imports System.Data.SqlClient 
Public Class Form1

   'at program startup, set the control properties
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Button1.Text = "Add to DB"
      Button2.Text = "Show DB"
      TextBox2.Multiline = True
   End Sub 

   'write a record to the database table
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      Dim sqCon As New SqlClient.SqlConnection("Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MyTestDB.mdf;Database=MyTestDB; Trusted_Connection=Yes;")
      Dim sqCmd As New SqlClient.SqlCommand

      sqCmd.Connection = sqCon            'create the DB connection
      sqCmd.CommandText = "INSERT INTO [MyTestDB].[dbo].[tblTest] VALUES ('" & TextBox1.Text & "')"
      sqCon.Open()                        'open the connection
      sqCmd.ExecuteNonQuery()             'execute the SQL command
      sqCon.Close()                       'close the connection
   End Sub 

   'read and display all records from the database table
   Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
      Dim sqCon As New SqlClient.SqlConnection("Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MyTestDB.mdf;Database=MyTestDB; Trusted_Connection=Yes;")
      Dim sqCmd As New SqlClient.SqlCommand
      Dim sdrRow As SqlClient.SqlDataReader

      'define the DB connection and search string
      sqCmd.Connection = sqCon
      sqCmd.CommandText = "SELECT * FROM tblTest"

      'open the DB connection
      sqCon.Open()                        'open the DB connection
      sdrRow = sqCmd.ExecuteReader()      'read the entire table

      'extract and display each field
      TextBox2.Text = ""                  'clear the text box
      Do While sdrRow.Read()
         TextBox2.Text = TextBox2.Text & sdrRow.GetValue(0) & vbTab     'get the primary key
         TextBox2.Text = TextBox2.Text & sdrRow.GetValue(1) & vbCrLf    'get the string
      Loop

      'close up and return
      sdrRow.Close()
      sqCon.Close()

   End Sub
End Class

Comments

Popular posts from this blog

The War between Iconic Emeka Ike and Segun Arinze

The war between iconic actors Segun Arinze and Emeka Ike seems to have no end in sight. In a recent interview, Arinze had said Ike should stop embarrassing the Actors Guild of Nigeria by parading himself as its president. According to Segun: ‘Emeka should grow up. As far as I am concerned, Ibinabo Fiberesima is the bonafide president. Even in the recent judgement that came out recently telling us to return to the status quo, what does it mean? We were told to go back to the way we were. There was no time Emeka Ike was pronounced the president of AGN. Let him bring out the copy of the judgement and show us if he is man enough. He should wake up and stop embarrassing the guild.” Well, these words did not go down well with Ike who believes Segun Arinze “is an illiterate who should have been in jail.” He told Saturday Beats: “I heard Segun Arinze was interviewed and he said so many things about me and nobody asked me for my own side. I don’t think it was a balanced report. Se...

Jonathan, Abiara ask Nigerians to pray for 2015 polls

                                       President Goodluck Jonathan on Thursday felicitated with Nigerian Muslims as they commemorate the birth of Prophet MuMuhammad. In a statement by his Special Adviser on Media and Publicity, Dr. Reuben Abati, the President urged Muslims to use the opportunity offered by the Eid-el-Maulud to offer special prayers for successful and peaceful elections in the country next month. Also, the General Evangelist, Christ Apostolic Church Worldwide, Prophet Samuel Abiara, has appealed to politicians to embrace peace and avoid any act that could lead to violence ahead of the 2015 general elections. The clergy, who spoke in Lagos during the crossover service, decried insecurity and economic decline as a result of falling oil prices. He said, “We are deeply concerned over the continuous killing of the innocent people of Nigeria. The land is becoming...

400-YEAR Old Church Emerge From Receding Water

No biggie, but a Colonial-era church has emerged from receding waters in Mexico. Leonel Mendoza fishes every day in a reservoir surrounded by forest and mountains in the southern Mexico state of Chiapas. But in recent days, he has been ferrying curious passengers out to see the remains of a colonial-era church that has emerged from the receding waters. Pretty impressive, right? A drought this year means the level of the Grijalva River, which flows into the Nezahualcoyotl reservoir, has dropped by 25 meters (82 feet). It is the second time a drop in the reservoir has revealed the church since it was flooded when the dam was completed in 1966. In 2002, the water was so low visitors could walk inside the church. ‘The people celebrated. They came to eat, to hang out, to do business. I sold them fried fish. They did processions around the church,’ Mendoza recalled. The church near Quechula was built by a group of monks headed by Friar Bartolome de l...