ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

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”.

  1. CREATE TABLE IF NOT EXISTS `nameofweb` (
  2.   `ID` int(11) NOT NULL AUTO_INCREMENT,
  3.   `WEBSITE` text NOT NULL,
  4.   PRIMARY KEY (`ID`)

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.

  1. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2.         'SET AND ADD THE COLIMN IN THE DATAGRIDVIEW FOR THE FIRST LOAD
  3.         'THE WEBNAME REPRESNT A TEXTBOX COLUMN FOR INPUTING DATA
  4.         Dim WEBNAME As New DataGridViewTextBoxColumn
  5.         With WEBNAME
  6.             'SET THE HEADER NAME OF A COLIMN
  7.             .Name = "WEBSITE"
  8.         End With
  9.         With DataGridView1
  10.             'ADD THE COLUMN THAT YOU HAVE CREATED IN THE DATAGRIDVIEW
  11.             .Columns.Add(WEBNAME)
  12.             'SET THE PROPERTIES OF THE DATAGRIDVIEW FOR FILLING THE SIZE OF THE COLUMN.
  13.             .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
  14.         End With
  15.     End Sub

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.

  1. 'SET YOUR IMPORTS
  2. Imports MySql.Data.MySqlClient
  3. Public Class Form1
  4.     'SET UP A CONNECTION OF MYSQL DATABASE AND VISUAL BASIC.
  5.     Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;database=dbweb")
  6.     'A SET OF COMMANDS IN MYSQL
  7.     Dim cmd As New MySqlCommand
  8.     'SET THE BRIDGE BETWEEN THE DATABASE AND THE DATASET FOR SAVING AND RETRIEVING DATA.
  9.     Dim da As New MySqlDataAdapter
  10.     'a specfic table in the database
  11.     'A SPECIFIC TABLE IN THE DATABASE
  12.     Dim dt As New DataTable
  13.     'SET A VARIABLE FOR THE QUERY IN IT
  14.     Dim sql As String
  15.     'SET A VARIABLE FOR STORING RESULT IN IT
  16.     Dim result As Integer
  17. End Class

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.

  1. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  2.         Try
  3.             'OPENING THE CONNECTION
  4.             con.Open()
  5.             'DATAGRIDVIEWRO REPRESENTS A ROW IN THE DATAGRIDVIEW CONTROL
  6.             'ROWS IS A COLLECTION OF ROWS IN THE DATAGRIDVIEW
  7.             For Each row As DataGridViewRow In DataGridView1.Rows
  8.                 'CELL IS A COLLECTION OF CELL THAT POPULATE THE ROW
  9.                 'FORMATTEDVALUE IS GETTING THE VALUE OF THE CELL AS A FORMATED DISPLAY
  10.                 If row.Cells(0).FormattedValue  "" Or row.Cells(1).FormattedValue  "" Then
  11.                     'STORE YOUR QUERY IN A VARIABLE THAT YOU HAVE DECLARED
  12.                     sql = "INSERT INTO NameOfWeb (WEBSITE) VALUES ('" _
  13.                                 & CStr(row.Cells(0).FormattedValue) & "')"
  14.                     'SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
  15.                     'AND ONCE IT'S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
  16.                     With cmd
  17.                         .Connection = con
  18.                         .CommandText = sql
  19.                     End With
  20.                     'EXECUTE THE DATA
  21.                     result = cmd.ExecuteNonQuery
  22.                 End If
  23.             Next
  24.             'CHECKING IF THE DATA ARE EXECUTED OF NOT.
  25.             If result > 0 Then
  26.                 MsgBox("All Records Saved.")
  27.             Else
  28.                 MsgBox("No saved Record.")
  29.             End If
  30.         Catch ex As Exception
  31.             MsgBox(ex.Message)
  32.         End Try
  33.         'CLOSING THE CONNECTION
  34.         con.Close()
  35.     End Sub

Download the complete Source Code and run it on your computer.
DOWNLOAD HERE

Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!