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.
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.
