Connect Vb.Net Form with SQL Server Database

Hello Everyone!!!

Want to know how to connect Vb.net form with SQL Server? Well here a solution along with tried and tested example. After this tutorial you will be able to read and write data to SQL server through your vb.net form.

Download Sample Project.

So Lets Start!!!!

This is a Simple task and can be achieved by following these two simple steps.

• Designing
• Coding

Designing.

Open VB.net.

Create a new project.

Create a Form. Name it as “frm_main”

Now on design it like in the picture below along with proper names.

 Note: The "delete data from database" option is included in Sample project.

Coding.

Once designing is done Click on form and press F7 or right-click on it and select “View Code”. The Code section will be displayed.

Add the lines on top (before “Public class your form name here”)

Imports System.Data

Imports System.Data.SqlClient

Explanation:

These are two classes required to make connection between your form and SQL Database.

Once the classes is imported we have to declare some variables which we will use everywhere. So paste this after “Public class your form name here”

    Dim cn As New SqlConnection("Your Connection String Here. If u don’t know how to get connection string and Click here for a Quick Tutorial")

    Dim dr As SqlDataReader

    Dim cmd As New SqlCommand

Your Code Section will be looking something like this

To be Honest this is was one of the hardest parts. Once variables is taken care of

Reading Data from SQL Server and Display it in vb.net Form.

Press shift+F7. Form designer will show up. Double click on

“Read” button (original name: READDATA) and paste the following codes.

If emp_id_read.Text = "" Then

            MsgBox("Please Enter Employe ID")

            emp_id_read.Focus()

            Exit Sub

        End If

        If emp_id_read.Text <> "" Then

            cn.Close()

            cn.Open()

            Dim query As String = "SELECT * FROM frm_emp where id = '" & emp_id_read.Text & "'"

            cmd = New SqlCommand(query, cn)

            dr = cmd.ExecuteReader

            dr.Read()

            If dr.HasRows = True Then

                emp_name_read.Text = dr("name")

                emp_dept_read.Text = dr("dept")

            End If

        Else

            MsgBox("INVALID Emplyee ID ")

            emp_id_read.Focus()

        End If

        cn.Close()

Explanation:

Well most of the above code is self explanatory. Like first we checked if “emp_id_read” is blank or not. I have used two different methods above to check if textbox is empty or not.

After that we have closed connection for safety and then opened it again. This is very important. If the connection is not opened then your form will not be able to establish contact with Sql Server. Then I have given “query” (that is a string) a value which actually is a command of Sql server to select everything from a table.

Next we pass “query” value along with connection (which we have declared in the starting as “cn” so we don’t have to write connection string over and over again) to “cmd” (which also we have declared in the starting as “sqlcommand”)

Then we execute query but we are expecting some thing in return this time so we use

cmd.ExecuteReader

and whatever is in “cmd” is passed to “dr” (which again we have declared in the starting as “datareader” whose job is to read data of course) and we check if “dr” have any rows in it and then we put values from datareader to vb.net form. I have “name” and “dept” column in my database. So I find it in “datareader” and assigned there values to textbox.

In Short you can now read data from Sql database and display it in vb.net form.

Writing Data to Sql Server through Vb.net Form.

Go To Designer view and double click on “Write” (original name: “Write”) and paste this in it.

            cn.Close()

            cn.Open()

            cmd = New SqlCommand("INSERT INTO tablenamehere (id,name,dept) values(" + emp_id_write.Text + ",'" + emp_name_write.Text + "','" + emp_dept_write.Text + "')", cn)

            cmd.ExecuteNonQuery()

            cn.Close()

            MsgBox("Saved")

Explanation:

Just like before, We have closed and opened connection but this time I have passed value (Which is a command in sql to insert data in form) directly to “cmd” values along with connection “cn” in the end. In the above query the “id,name,dept” are names of columns in my table of sql database. In the Reading data method I have used “cmd.executereader” but here I have used “cmd.executenonquery” because this time I am not expecting something in return. After that I have closed the connection and displayed a Message (which is saved).

Now You can are able to write data from your vb.net form to SQl database.

After following complete above procedure your code section will look something like this.