VB Sql Command Class


The SqlCommand Class represents an SQL command to execute against a data store(actual database). This command is usually a select, insert,update, or delete query, or we can also use Stored Procedures.

Declaring an Object of the SqlCommand Class:-

Dim x as SqlCommand =New SqlCommand()

Various Properties of SqlCommand Class

Dim x As SqlConnection=New SqlConnection("Data Source=PC\SQLEXPRESS; Initial Catalog=college;User ID=sa;Password=123")

Dim x1 as SqlCommand =New SqlCommand()
  1. Connection Property:-
  2. x1.Connection=x

  3. CommandText Property:-
  4. This property specifies the SQL string or Stored Procedure to be executed.x1.CommandText=

"insert into student (stud_id,stud_name,stud_section) values(1,'Sachin Yadav','JK201')"
  1. CommandType Property:-
  2. This property specifies what is provided in commandText- SQL query or StoredProcedure. Values for command Type are: CommandType.Text or CommandType.StoredProcedure.


Retrieving values from Textboxes in Insert Statement

Dim x As SqlConnection = New SqlConnection("Data Source=PC\SQLEXPRESS;Initial Catalog=college;User ID=sa;Password=123")

Dim x1 As SqlCommand = New SqlCommand()

x1.Connection = x

x1.CommandText = "insert into student (stuid, stuname) values(" + Textbox1.Text +", ' "+ Textbox2.Text + " ' "

x1.CommandType= CommandType.Text

The Parameters Collection

Dim x As SqlConnection = New SqlConnection("Data Source=PC\SQLEXPRESS;Initial Catalog=college;User ID=sa;Password=123")

Dim x1 As SqlCommand = New SqlCommand()

x1.Connection = x

x1.CommandText = "insert into student values(@var1,@var2,@var3)".

Var1,var2,var3 are the Placeholders or parameter variables, which will take values from the controls, because the values are not known at design time.


Sample Code for Inserting the Data in the Database

Dim x As SqlConnection = New SqlConnection("Data Source=PC\SQLEXPRESS;Initial Catalog=college;User ID=sa;Password=123")

Dim x1 As SqlCommand = New SqlCommand()

x1.Connection = x

x1.CommandText = "insert into books values(@var1,@var2,@var3)"

x1.Parameters.AddWithValue("@var1", TextBox1.Text)

x1.Parameters.AddWithValue("@var2", TextBox2.Text)

x1.Parameters.AddWithValue("@var3", TextBox3.Text)

x.Open()

If (x1.ExecuteNonQuery()) Then

MessageBox.Show("Record is Inserted Successfully")

Else

MessageBox.Show("There is Problem")

End If

x.Close()

Share this article on