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
Post a Comment