Visual Basic Online Courses DataGridView CRUD SQL Server

CRUD Operations in VB.NET 

with SQL Server

In this guide, you'll learn how to perform basic CRUD operations — Create, Read, Update, and Delete — using VB.NET and SQL Server. We'll also cover how to generate reports using Crystal Reports.

Our sample project is called StudentManager. It handles a student database with fields like StudentID, FullName, BirthDate, and Major.

🔧 Database Table Structure

• Table Name: Students
• Fields: StudentID (int), FullName (varchar), BirthDate (date), Major (varchar)

💡 Project Setup

• Create a new VB.NET Windows Forms Application named StudentManager.
• Add a DataGridView and textboxes for input.
• Add buttons for: Add, Update, Delete, Search, Print, and Exit.

📦 Naming Conventions

• txtName, txtMajor, dtpBirthDate, dgvStudents
• btnAdd, btnUpdate, btnDelete, btnSearch, btnReport

🧑‍💻 CRUD Code Snippets (PrismJS Ready)

🍀SQL Declaration

  Imports MySql.Data.MySqlClient

🧼SQL Server ConnectionString in VB.NET

Dim connString As String = "Server=evry1falls;Port=3307;Database=ProductDB;User Id=root;Password=AhmedSamir1_;"
	Dim conn As New MySqlConnection(connString)

🟢 INSERT Code

Sub CreateData()
    Try
        conn.Open()
        Dim query As String = "INSERT INTO Products (ProductName, Price) VALUES (@name, @price)"
        Dim cmd As New MySqlCommand(query, conn)
        cmd.Parameters.AddWithValue("@name", "Sample Product")
        cmd.Parameters.AddWithValue("@price", 100.0)
        cmd.ExecuteNonQuery()
        conn.Close()
        MessageBox.Show("Data inserted successfully!")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

🔵 SELECT / READ Code

Sub ReadData()
    Try
        conn.Open()
        Dim query As String = "SELECT * FROM Products"
        Dim cmd As New MySqlCommand(query, conn)
        Dim reader As MySqlDataReader = cmd.ExecuteReader()
        While reader.Read()
            Console.WriteLine(reader("ProductName") & " - $" & reader("Price"))
        End While
        conn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

🟠 Modify/UPDATE Code

Sub UpdateData()
    Try
        conn.Open()
        Dim query As String = "UPDATE Products SET Price=@price WHERE ProductName=@name"
        Dim cmd As New MySqlCommand(query, conn)
        cmd.Parameters.AddWithValue("@name", "Sample Product")
        cmd.Parameters.AddWithValue("@price", 120.0)
        cmd.ExecuteNonQuery()
        conn.Close()
        MessageBox.Show("Data updated successfully!")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

🔴 DELETE Code

Sub DeleteData()
    Try
        conn.Open()
        Dim query As String = "DELETE FROM Products WHERE ProductName=@name"
        Dim cmd As New MySqlCommand(query, conn)
        cmd.Parameters.AddWithValue("@name", "Sample Product")
        cmd.ExecuteNonQuery()
        conn.Close()
        MessageBox.Show("Data deleted successfully!")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Crystal Reports Code in VB.NET

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Dim rpt As New ReportDocument()
rpt.Load(Application.StartupPath & "\StudentReport.rpt")

' Set DB login info (if needed)
rpt.SetDatabaseLogon("username", "password")

' Assign to CrystalReportViewer
CrystalReportViewer1.ReportSource = rpt
CrystalReportViewer1.Refresh()

📄 Crystal Reports Integration

After creating a Crystal Report, you can use the following placeholder to display it in a viewer.

Note: You'll need to add a .rpt file to your project and configure the database connection using the ReportDocument object.

🎓 Conclusion

With this structure, you can easily develop and manage VB.NET applications connected to SQL Server and include reporting features. Stay tuned for the full implementation of each code block!

♥  Here are some online Visual Basic lessons and courses:

📘 Desktop application Development - VB.NET WebView2 🌐 Desktop application Development - Internet connectivity 📩 Desktop application Development - POP3 mail Server 🎲 Desktop application Development - VB.NET Strings 🔊 Desktop application Development - VB.NET Sound and Music 📊 Desktop application Development - VB.NET ProgressBar Control 🚫 Desktop application Development - TroubleShooting 💾 Microsoft Access Developer - VB.NET Best Practice Guide 🔁 Desktop application Development - VB.NET DataReader 📚 Database Developer - SyBase Advantage 🌄 Desktop application Development - Images ⌨️ Desktop application Development - VB.NET KeyPress vs KeyDown 💻 Desktop App Developer - Install SSL on Windows 🔬 Programming - Object Oriented Programming OOP 💬 Desktop App Developer - Text Files 🎁 Desktop App Developer - VB.NET DeopBox API 🚗 Desktop App Developer - VB.NET Google Drive API

Sitemap for this BLOG