VB.Net/Database ADO.net/SqlCommand Insert
Execute NonQuery To Insert Data and report how many rows affected
<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient public class MainClass
Shared Sub Main() Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _ "integrated security=sspi;database=MyDatabase") "Create Command object Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand() Try " Open Connection thisConnection.Open() Console.WriteLine("Connection Opened") " Execute NonQuery To Create Table nonqueryCommand.rumandText = "CREATE TABLE MyTable (ID integer)" Console.WriteLine("Executing {0}", nonqueryCommand.rumandText) Console.WriteLine("Number of rows affected : {0}", nonqueryCommand.ExecuteNonQuery())
" Execute NonQuery To Insert Data nonqueryCommand.rumandText = "INSERT INTO MyTable VALUES (37)" Console.WriteLine("Executing {0}", nonqueryCommand.rumandText) Console.WriteLine("Number of rows affected : {0}",nonqueryCommand.ExecuteNonQuery())
" Execute NonQuery To Drop Table nonqueryCommand.rumandText = "DROP TABLE MyTable" Console.WriteLine("Executing {0}", nonqueryCommand.rumandText) Console.WriteLine("Number of rows affected : {0}", nonqueryCommand.ExecuteNonQuery()) Catch ex As SqlException " Display error Console.WriteLine("Error: " & ex.ToString()) Finally " Close Connection thisConnection.Close() Console.WriteLine("Connection Closed") End Try End Sub
End Class
</source>
Insert Data and Check result
<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient public class MainClass
Shared Sub Main() Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _ "integrated security=sspi;database=MyDatabase") "Create Command object Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand() Try " Open Connection thisConnection.Open() Console.WriteLine("Connection Opened") "Create Command objects Dim scalarCommand As New SqlCommand("SELECT COUNT(*) FROM Employee", thisConnection) " Execute Scalar Query Console.WriteLine("Before INSERT, Number of Employees = {0}", _ scalarCommand.ExecuteScalar())
" Set up and execute INSERT Command nonqueryCommand.rumandText ="INSERT INTO Employee(ID, FirstName,LastName) " & _ "VALUES (9,"A","A")" Console.WriteLine("Executing {0}", nonqueryCommand.rumandText) Console.WriteLine("Number of rows affected : {0}", _ nonqueryCommand.ExecuteNonQuery())
" Execute Scalar Query Console.WriteLine("After INSERT, Number of Employees = {0}", _ scalarCommand.ExecuteScalar()) Catch ex As SqlException " Display error Console.WriteLine("Error: " & ex.ToString()) Finally " Close Connection thisConnection.Close() Console.WriteLine("Connection Closed") End Try End Sub
End Class
</source>
Insert data using SqlCommand
<source lang="vbnet"> Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Windows.Forms Imports System.Data.SqlClient
Public Class MainClass
Shared Dim WithEvents con As SqlConnection Shared Sub Main() con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI") Dim cmd As New SqlCommand() cmd.rumandText = "Insert into Employee (ID, FirstName, LastName) values(" & _ "1, "Joe", "Yin")" cmd.Connection = con Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try End Sub
End Class
</source>