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
