How to Save Multiple Data in VB.Net and MySQL Database

0
3194

This tutorial is all about How to Save Multiple Data in VB.Net and MySQL Database.
If you are tired of saving data in the database one at a time, this tutorial will teach you how to save multiple data using DataGridView in VB.Net and MySQL Database. This will help make your work fast in a short period of time.

So let’s begin:
Create a database and name it “dbweb”. After that, set this query for creating a table in it and name it ”nameofweb”.
[mysql] CREATE TABLE IF NOT EXISTS nameofweb (
ID int(11) NOT NULL AUTO_INCREMENT,
WEBSITE text NOT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[/mysql] After creating a database, let’s go to create an application. Now, open the Visual Basic and create a new Windows Form Application. After that, set the Form just like this.
savingmultipledataform
After setting up the Form, double click it and add a column in the DataGridView. Then, name the header of the column that you have created in it.
[vbnet] Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
‘SET AND ADD THE COLIMN IN THE DATAGRIDVIEW FOR THE FIRST LOAD
‘THE WEBNAME REPRESNT A TEXTBOX COLUMN FOR INPUTING DATA
Dim WEBNAME As New DataGridViewTextBoxColumn
With WEBNAME
‘SET THE HEADER NAME OF A COLIMN
.Name = “WEBSITE”
End With
With DataGridView1
‘ADD THE COLUMN THAT YOU HAVE CREATED IN THE DATAGRIDVIEW
.Columns.Add(WEBNAME)
‘SET THE PROPERTIES OF THE DATAGRIDVIEW FOR FILLING THE SIZE OF THE COLUMN.
.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
End With
End Sub
[/vbnet] After adding the column in the DataGridView, set your connection of MySQL Database to Visual Basic and declare the classes and variables that are needed. Set it above the Form1_Load.
[vbnet] ‘SET YOUR IMPORTS
Imports MySql.Data.MySqlClient
Public Class Form1
‘SET UP A CONNECTION OF MYSQL DATABASE AND VISUAL BASIC.
Dim con As MySqlConnection = New MySqlConnection(“server=localhost;user id=root;database=dbweb”)
‘A SET OF COMMANDS IN MYSQL
Dim cmd As New MySqlCommand
‘SET THE BRIDGE BETWEEN THE DATABASE AND THE DATASET FOR SAVING AND RETRIEVING DATA.
Dim da As New MySqlDataAdapter
‘a specfic table in the database
‘A SPECIFIC TABLE IN THE DATABASE
Dim dt As New DataTable
‘SET A VARIABLE FOR THE QUERY IN IT
Dim sql As String
‘SET A VARIABLE FOR STORING RESULT IN IT
Dim result As Integer
End Class
[/vbnet] Note : Don’t forget to add MySql.Data.dll as your reference, so that it won’t encounter errors when you’re declaring MySQL.

Go back to to the Form Design, double click the Button and add this code for saving the data in the MySQL Database.
[vbnet] Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
‘OPENING THE CONNECTION
con.Open()
‘DATAGRIDVIEWRO REPRESENTS A ROW IN THE DATAGRIDVIEW CONTROL
‘ROWS IS A COLLECTION OF ROWS IN THE DATAGRIDVIEW
For Each row As DataGridViewRow In DataGridView1.Rows
‘CELL IS A COLLECTION OF CELL THAT POPULATE THE ROW
‘FORMATTEDVALUE IS GETTING THE VALUE OF THE CELL AS A FORMATED DISPLAY
If row.Cells(0).FormattedValue “” Or row.Cells(1).FormattedValue “” Then
‘STORE YOUR QUERY IN A VARIABLE THAT YOU HAVE DECLARED
sql = “INSERT INTO NameOfWeb (WEBSITE) VALUES (‘” _
& CStr(row.Cells(0).FormattedValue) & “‘)”
‘SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
‘AND ONCE IT’S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
With cmd
.Connection = con
.CommandText = sql
End With
‘EXECUTE THE DATA
result = cmd.ExecuteNonQuery
End If
Next
‘CHECKING IF THE DATA ARE EXECUTED OF NOT.
If result > 0 Then
MsgBox(“All Records Saved.”)
Else
MsgBox(“No saved Record.”)
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
‘CLOSING THE CONNECTION
con.Close()
End Sub
[/vbnet] Download the complete Source Code and run it on your computer.
DOWNLOAD HERE

 

Readers might read also:

 

 

Facebook Comments

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.