CRUD in VB.Net and SQL Server

In this post we will see how to create a CRUD in VB.Net and SQL Server. The full form of CRUD is create, read,update and delete records from the database. By the end of this tutorial you will be able to connect to the SQL server database and insert, update,delete and read database from the database using VB.Net.

To create a VB.Net project about CRUD in VB.Net and SQL Server,first of all you have to design an interface of the form with six labels, five text boxes and six buttons on this form as shown in the image.

Next change the Text property of the labels as Id,Name,Address, Roll No,Email Address and lblmessage and the name property of the textboxes to idTextBox, nameTextBox, addressTextBox, rollnoTextBox and emailTextBox.

Next, change the name property of the buttons as InsertButton, updateButton, DelButton, SearchButton, ClearButton and exitButton respectively.

If you want to learn about CRUD operations in VB.Net and Stored Procedures then click CRUD in VB.Net using Stored Procedures and if you want to learn about SQL  injection attack in VB.Net and SQL Server just simply click SQL Injection attack in VB.Net and SQL Server.

Before you start typing the VB.Net code on the different buttons which you placed on the form of CRUD in VB.Net and SQL Server project first of all you have to create a database in the SQL Server management studio with the name of students and also you need to create a table with the name of class with fields as shown below.

 USE [students]  
 GO  
 CREATE TABLE [dbo].[class](  
      [Id] [int] IDENTITY(1,1) NOT NULL,  
      [st_name] [varchar](50) NULL,  
      [address] [varchar](50) NULL,  
      [Roll_No] [varchar](10) NULL,  
      [Email] [varchar](50) NULL,  
  CONSTRAINT [PK_class] PRIMARY KEY CLUSTERED   
 (  
      [Id] ASC  
 )  
 )  
 GO  

Now type the VB.Net code on different buttons of the form as under:

 Imports System.Data.SqlClient  
 Public Class IUDS_VB_SQL  
   Dim con As New SqlConnection("server = hp;database=students;integrated security=SSPI")  
   Private Sub InsertButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InsertButton.Click  
     Dim insertquery As String = "insert into class (st_name,address,Roll_No,Email) values(' " & nameTextbox.Text.Trim() & " ','" & addressTextbox.Text.Trim() & " ','" & rollnoTextbox.Text.Trim() & "','" & emailTextbox.Text.Trim() & "')"  
     executequery(insertquery)  
     'MessageBox.Show("Record inserted successfully", "Welcome...", MessageBoxButtons.OK, MessageBoxIcon.Information)  
     lblmessage.Text = "Record inserted successfully"  
   End Sub  
   Public Sub executequery(ByVal query As String)  
     Dim cmd As New SqlCommand(query, con)  
     con.Open()  
     cmd.ExecuteNonQuery()  
     con.Close()  
   End Sub  
   Public Sub clear()  
     nameTextbox.Text = ""  
     addressTextbox.Text = ""  
     rollnoTextbox.Text = ""  
     emailTextbox.Text = ""  
     idTextbox.Text = ""  
     lblmessage.Text = ""  
     idTextbox.Focus()  
   End Sub  
   Private Sub DelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DelButton.Click  
     Dim delquery As String = "delete from class where id='" & idTextbox.Text.Trim() & "'"  
     executequery(delquery)  
     lblmessage.Text = "Record Deleted successfully"  
     clear()  
   End Sub  
   Private Sub updateButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles updateButton.Click  
     Dim updatequery As String = "update class set st_name='" & nameTextbox.Text.Trim() & "',address='" & addressTextbox.Text.Trim() & "',roll_no='" & rollnoTextbox.Text.Trim() & "',email='" & emailTextbox.Text.Trim() & "' where id='" & idTextbox.Text.Trim() & "'"  
     executequery(updatequery)  
     lblmessage.Text = "Record updated successfully"  
   End Sub  
   Private Sub SearchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchButton.Click  
     If idTextbox.Text = "" Then  
       MsgBox("Please enter ID to search")  
       Exit Sub  
     End If  
     Dim cmd As New SqlCommand("Select * from class where id=@id", con)  
     cmd.Parameters.Add("@id", SqlDbType.Int).Value = idTextbox.Text  
     Dim ta As New SqlDataAdapter(cmd)  
     Dim table As New DataTable  
     ta.Fill(table)  
     nameTextbox.Text = ""  
     addressTextbox.Text = ""  
     rollnoTextbox.Text = ""  
     emailTextbox.Text = ""  
     If table.Rows.Count > 0 Then  
       nameTextbox.Text = table.Rows(0)(1).ToString()  
       addressTextbox.Text = table.Rows(0)(2).ToString()  
       rollnoTextbox.Text = table.Rows(0)(3).ToString()  
       emailTextbox.Text = table.Rows(0)(4).ToString()  
       lblmessage.Text = "Record found!"  
     Else  
       lblmessage.Text = "Sorry record not found"  
     End If  
   End Sub  
   Private Sub clearButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles clearButton.Click  
     clear()  
   End Sub  
   Private Sub exitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exitButton.Click  
     End  
   End Sub   
 End Class  

Now, finally press F5 and run CRUD in VB.Net and SQL Server project.
If you want to watch the video tutorial of this post then please visit my YouTube channel  sarfarazbhat and watch different programming videos or you can watch this tutorial right here.

SQL Server, VB.Net