Here are a few steps to build a connection with SQL Express database.
1. Create a new project
Select Windows Application from the New Project Dialog
2. Add Database to Your Project
To add a database to your project, select 'Add New Item' from the project menu
You will be presented with all the items you can add to your project. Select 'SQL Database' from the available items
After the database is added to your project, an entry will be added in the Solution Explorer
3. Creating Tables
After you have added the database to your project, the next step is to create tables in the newly created database. To create a new table right click on the tables icon in the server explorer window and click 'Add New Table'.
4. Adding fields in the Table.
The new table screen will allow you to add fields to your table. The bottom section (Column Properties) allows you to change data type and other attributes of table fields.
After you are done adding fields to your table, click 'Save' from the File menu and give you table a name
5. The interface to add records
The next step is to design a form to accept data from the user and store it in our database. Design your form as shown blow.
6. Connecting to Database
Its now time to connect to our database. It good to add database related code in a separate module. To add a Module to your project, click 'Add Module' from the project menu
7. Add the following code to your Module.
Imports System.Data.SqlClient
Module Module1
Public cn As SqlConnection
Public Sub connect()
Dim s As String
s = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\Database1.mdf;Integrated Security=True;User Instance=True"
cn = New SqlConnection(s)
cn.Open()
MsgBox("Connected to the database", MsgBoxStyle.Information)
End Sub
Public Sub addRecord(ByVal name, ByVal address, ByVal phone)
execute("insert into Contacts (name,address,phone) values('" & name & "','" & address & "','" & phone & "')")
End Sub
Public Sub execute(ByVal q As String)
Dim cmd As SqlCommand = cn.CreateCommand()
cmd.CommandText = q
cmd.ExecuteNonQuery()
End Sub
Public Function getRecords(ByVal q As String) As SqlDataReader
Dim reader As SqlDataReader, cmd As SqlCommand
cmd = cn.CreateCommand
cmd.CommandText = q
reader = cmd.ExecuteReader
Return reader
End Function
End Module
8. Add the following code in your form
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
connect()
End Sub
Private Sub bttAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttAdd.Click
addRecord(txtName.Text, txtAddress.Text, txtPhone.Text)
txtName.Text = ""
txtAddress.Text = ""
txtPhone.Text = ""
MsgBox("Record Added")
End Sub
End Class
Download Sample Code
mary