Free Information Technology Magazines and eBooks

Tuesday, June 09, 2009

VB.NET: How to INSERT record to SQL Database

This topic is part of VB.NET SQL Database Basics Tutorial series. On this article, you will learn how to incorporate SQL INSERT statements on your VB.NET application. An SQL INSERT statement adds one or more records to any single table in a relational database. For this article, we will focus particularly on Microsoft SQL Server.



Note: I assumed that you already installed your SQL Server and Visual Studio .NET.

Here is the step-by-step procedure to INSERT record to SQL server:
1. Create your VB.NET project.
2. Include the following namespaces.

Imports System.Data
Imports System.Data.SqlClient

The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.

3. Declare and instantiate your SQLConnection object and Command object as shown below

Dim con As New SqlConnection
Dim cmd As New SqlCommand

4. Pass the SQL connection string to ConnectionString property of your SqlConnection object.

con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"


5. Invoke the Open Method to connect to SQL Server.

con.Open()

6. Set the connection of the command object.

cmd.Connection = con

7. Pass the INSERT SQL statement to the command object commandtext as shown below

cmd.CommandText = "INSERT INTO table (field1, [field2, ... ]) VALUES (value1, [value2, ...])"

8. Use the ExecuteNonQuery() method to run INSERT SQL that has no return value.

cmd.ExecuteNonQuery()


If you expect a return value use ExecuteScalar() instead. A good example of ExecuteScalar is to get the Identity column after inserting record
(ExecuteScalar return the first column of the first row from the result).


The full sample source code of inserting record to SQL Database:

Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO table([field1], [field2]) VALUES([Value1], [Value2])"
cmd.ExecuteNonQuery()

Catch ex As Exception
MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
Finally
con.Close()
End Try





7 comments:

coakley said...

thanx man
it works!

Anonymous said...

many many tnx!

Shashank said...

Thank u dude

darren said...

thanks :)))

Anonymous said...

Thank you for this little series on SQL and vb.net. I have been a PROFESSIONAL programmer since 1978 and I usually can't understand a single word spoken by most other so called tutorials. However you have the ability to just say it like it is. No big ego showing off like most other people trying to give examples. THANK YOU AGAIN! You have made an old coder very happy !!

Anonymous said...

invalid comumn name'carlo' bro.. the word carlo is the one that i want to insert to datbase

vb.2010 express Dim connection As New SqlConnection
Dim command As New SqlCommand
Dim adaptor As New
Try
connection.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\carloboy\documents\visual studio 2010\Projects\jglcc\jglcc\user.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
command.CommandText = "INSERT INTO jc([name],[surname]) VALUES([" & TextBox1.Text & "],[" & TextBox2.Text & "]);"
connection.Open()
command.Connection = connection
adaptor.InsertCommand = command
command.ExecuteNonQuery()


MsgBox("success!")



Catch EX As Exception
MsgBox(EX.ToString)
End Try

Anonymous said...

command.CommandText = "INSERT INTO Users(username, pwd) VALUES('" + TextBox1.Text + "','" + TextBox2.Text + "')"

Thats Work :)