thenewbie123
Laugh Riot
2
MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1
200 XP
This tutorial is a continuation of our our previous topic called “Creating MySQL Database tables using Visual Basic. Net”. This time, we're going to focus on displaying and dropping of MySQL Database Tables using Visual Basic.Net.
In this application, we need to add two buttons and a datagridview. Then, rename the first button as “btndescribe” and change the text property to “Describe”, and the other button change the name to “btndrop” and the text property to “Drop”. Next, for the Datagridview1 change the name as “dtgstructure” and place it over the first datagridview.
Take note folks, the data will be lost and could not be recovered after deleting a table. Although it is very easy to drop an existing MySQL table, but you need to be very careful while deleting any existing table.
This time, let’s start adding functionality to our “Describe” button. To do this, add the following code:
Then we can test our application by pressing “F5”. Then after clicking the “Describe” button the expected output will look like as shown below.
Next, for the “Drop” button. Add the following code.
This code will drop the existing table in any database.
And here’s all the code use for this application.
After reviewing all the codes above, you can now test your application by pressing “F5”.
Download
In this application, we need to add two buttons and a datagridview. Then, rename the first button as “btndescribe” and change the text property to “Describe”, and the other button change the name to “btndrop” and the text property to “Drop”. Next, for the Datagridview1 change the name as “dtgstructure” and place it over the first datagridview.
Take note folks, the data will be lost and could not be recovered after deleting a table. Although it is very easy to drop an existing MySQL table, but you need to be very careful while deleting any existing table.
This time, let’s start adding functionality to our “Describe” button. To do this, add the following code:
- Private
Sub
btndescribe_Click(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
btndescribe.
Click
- txttblName.
ReadOnly
=
True
- btnAddtbl.
Visible
=
True
- btnDroptbl.
Visible
=
True
- btnCreateTbl.
Visible
=
False
- dtgstructure.
BringToFront
(
)
- Dim
sql As
String
=
"DESC "
&
cbdb.
Text
&
"."
&
cbtable.
Text
- Dim
publictable As
New
DataTable
- Try
- 'bind the connection and query
- With
cmd
- .
Connection
=
con
- .
CommandText
=
sql
- End
With
- da.
SelectCommand
=
cmd
- da.
Fill
(
publictable)
- ' publictable.Rows.Add("Please Select...")
- dtgstructure.
DataSource
=
publictable
- ' dtgrd.Columns(1).Visible = False
- da.
Dispose
(
)
- Catch
ex As
Exception
- MsgBox
(
ex.
Message
)
- End
Try
- con.
Clone
(
)
- End
Sub
Then we can test our application by pressing “F5”. Then after clicking the “Describe” button the expected output will look like as shown below.
Next, for the “Drop” button. Add the following code.
This code will drop the existing table in any database.
- Private
Sub
btnDroptbl_Click(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
btnDroptbl.
Click
- Dim
sql As
String
=
"DROP TABLE "
&
cbdb.
Text
&
"."
&
cbtable.
Text
- Dim
result As
Integer
- Try
- con.
Open
(
)
- With
cmd
- .
Connection
=
con
- .
CommandText
=
sql
- result =
cmd.
ExecuteNonQuery
- If
result >
0
Then
- MsgBox
(
"Error in dropping Field!"
)
- Else
- MsgBox
(
cbtable.
Text
&
" has Successfully dropped!"
)
- With
Me
- .
txttblName
.
ReadOnly
=
True
- .
btnCreateTbl
.
Visible
=
False
- .
btnAddtbl
.
Visible
=
True
- .
btnDroptbl
.
Visible
=
True
- .
dtgStructNewTbl
.
SendToBack
(
)
- dtgstructure.
Columns
.
Clear
(
)
- End
With
- End
If
- End
With
- Form1_Load(
sender, e)
- Catch
ex As
Exception
- MsgBox
(
ex.
Message
)
- End
Try
- con.
Close
(
)
- Call
Form1_Load(
sender, e)
- End
Sub
And here’s all the code use for this application.
- Imports
MySql.
Data
.
MySqlClient
- Public
Class
Form1
- 'Represents an SQL statement or stored procedure to execute against a data source.
- Dim
cmd As
New
MySqlCommand
- Dim
da As
New
MySqlDataAdapter
- Public
total As
Integer
- Dim
publictable As
New
DataTable
- 'declare conn as connection and it will now a new connection because
- 'it is equal to Getconnection Function
- Dim
con As
MySqlConnection =
jokenconn(
)
- Public
Function
jokenconn(
)
As
MySqlConnection
- Return
New
MySqlConnection(
"server=localhost;user id=root;password=;database="
)
- End
Function
- Private
Sub
Form1_Load(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
MyBase
.
Load
- GroupBox1.
Text
=
"Create new table on database "
- Dim
sql As
String
=
"SHOW DATABASES"
- Dim
publictable As
New
DataTable
- Try
- 'bind the connection and query
- With
cmd
- .
Connection
=
con
- .
CommandText
=
sql
- End
With
- da.
SelectCommand
=
cmd
- da.
Fill
(
publictable)
- With
cbdb
- .
DataSource
=
publictable
- .
DisplayMember
=
"Database"
- .
ValueMember
=
"Database"
- End
With
- da.
Dispose
(
)
- Catch
ex As
Exception
- MsgBox
(
ex.
Message
)
- End
Try
- con.
Clone
(
)
- End
Sub
- Private
Sub
btntest_Click(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
btntest.
Click
- GroupBox1.
Text
=
"Create new table on database "
&
cbdb.
Text
- Dim
sql As
String
=
"SHOW TABLES FROM "
&
cbdb.
Text
- Dim
publictable As
New
DataTable
- Try
- 'bind the connection and query
- With
cmd
- .
Connection
=
con
- .
CommandText
=
sql
- End
With
- da.
SelectCommand
=
cmd
- da.
Fill
(
publictable)
- ' publictable.Rows.Add("Please Select...")
- With
cbtable
- .
DataSource
=
publictable
- .
DisplayMember
=
"Tables_in_"
&
cbdb.
Text
- .
ValueMember
=
"Tables_in_"
&
cbdb.
Text
- End
With
- ' dtgrd.Columns(1).Visible = False
- da.
Dispose
(
)
- Catch
ex As
Exception
- MsgBox
(
ex.
Message
)
- End
Try
- con.
Clone
(
)
- End
Sub
- Private
Sub
btnAddtbl_Click(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
btnAddtbl.
Click
- txttblName.
ReadOnly
=
False
- btnCreateTbl.
Visible
=
True
- btnAddtbl.
Visible
=
False
- btnDroptbl.
Visible
=
False
- dtgstructure.
SendToBack
(
)
- End
Sub
- Private
Sub
btnCreateTbl_Click(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
btnCreateTbl.
Click
- Dim
field As
String
- Dim
type As
String
- Dim
nlenght As
Integer
- Dim
nNull As
String
- Dim
nIndex As
String
- Dim
nAI As
String
- Dim
alltxt As
String
- Dim
result As
Integer
- Dim
tblAndDB As
String
=
cbdb.
Text
&
"."
&
txttblName.
Text
- Dim
TBLCREATE As
String
=
"CREATE TABLE "
&
tblAndDB &
" ( "
- For
i As
Integer
=
0
To
Me
.
dtgStructNewTbl
.
Rows
.
Count
-
2
- ' field.Add(Me.dtgStructNewTbl.Rows(i).Cells("n_field").Value)
- field =
Me
.
dtgStructNewTbl
.
Rows
(
i)
.
Cells
(
"n_field"
)
.
Value
- type =
Me
.
dtgStructNewTbl
.
Rows
(
i)
.
Cells
(
"n_type"
)
.
Value
- nlenght =
Me
.
dtgStructNewTbl
.
Rows
(
i)
.
Cells
(
"n_lenght"
)
.
Value
- nNull =
Me
.
dtgStructNewTbl
.
Rows
(
i)
.
Cells
(
"n_null"
)
.
Value
- nIndex =
Me
.
dtgStructNewTbl
.
Rows
(
i)
.
Cells
(
"n_index"
)
.
Value
- nAI =
Me
.
dtgStructNewTbl
.
Rows
(
i)
.
Cells
(
"n_ai"
)
.
Value
- alltxt +=
field &
" "
&
type &
"("
&
nlenght &
")"
&
" "
&
nNull &
" "
&
nIndex &
" "
&
nAI &
","
&
vbNewLine
- Next
- Try
- 'it removes all the newline and whitespaces
- alltxt =
alltxt.
Substring
(
0
, alltxt.
Length
-
Environment.
NewLine
.
Length
)
- Dim
cleanText As
String
- 'it removes the the last comma ","
- cleanText =
alltxt.
Remove
(
alltxt.
Length
-
1
)
- Dim
finalText As
String
- 'combination of finalText with table name and Mysql ENGINE
- finalText =
TBLCREATE &
cleanText &
")ENGINE = MYISAM ;"
- con.
Open
(
)
- With
cmd
- .
Connection
=
con
- .
CommandText
=
finalText
- result =
cmd.
ExecuteNonQuery
- If
result >
0
Then
- MsgBox
(
"No Table has created!"
)
- Else
- MsgBox
(
txttblName.
Text
&
" has created Successfully!"
)
- With
Me
- .
txttblName
.
ReadOnly
=
True
- .
btnCreateTbl
.
Visible
=
False
- .
btnAddtbl
.
Visible
=
True
- .
btnDroptbl
.
Visible
=
True
- .
dtgStructNewTbl
.
SendToBack
(
)
- '.dtgStructNewTbl.Columns.Clear()
- End
With
- End
If
- End
With
- Form1_Load(
sender, e)
- Catch
ex As
Exception
- MsgBox
(
ex.
Message
)
- End
Try
- con.
Close
(
)
- End
Sub
- Private
Sub
btndescribe_Click(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
btndescribe.
Click
- txttblName.
ReadOnly
=
True
- btnAddtbl.
Visible
=
True
- btnDroptbl.
Visible
=
True
- btnCreateTbl.
Visible
=
False
- dtgstructure.
BringToFront
(
)
- Dim
sql As
String
=
"DESC "
&
cbdb.
Text
&
"."
&
cbtable.
Text
- Dim
publictable As
New
DataTable
- Try
- 'bind the connection and query
- With
cmd
- .
Connection
=
con
- .
CommandText
=
sql
- End
With
- da.
SelectCommand
=
cmd
- da.
Fill
(
publictable)
- ' publictable.Rows.Add("Please Select...")
- dtgstructure.
DataSource
=
publictable
- ' dtgrd.Columns(1).Visible = False
- da.
Dispose
(
)
- Catch
ex As
Exception
- MsgBox
(
ex.
Message
)
- End
Try
- con.
Clone
(
)
- End
Sub
- Private
Sub
btnDroptbl_Click(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
btnDroptbl.
Click
- Dim
sql As
String
=
"DROP TABLE "
&
cbdb.
Text
&
"."
&
cbtable.
Text
- Dim
result As
Integer
- Try
- con.
Open
(
)
- With
cmd
- .
Connection
=
con
- .
CommandText
=
sql
- result =
cmd.
ExecuteNonQuery
- If
result >
0
Then
- MsgBox
(
"Error in dropping Field!"
)
- Else
- MsgBox
(
cbtable.
Text
&
" has Successfully dropped!"
)
- With
Me
- .
txttblName
.
ReadOnly
=
True
- .
btnCreateTbl
.
Visible
=
False
- .
btnAddtbl
.
Visible
=
True
- .
btnDroptbl
.
Visible
=
True
- .
dtgStructNewTbl
.
SendToBack
(
)
- dtgstructure.
Columns
.
Clear
(
)
- End
With
- End
If
- End
With
- Form1_Load(
sender, e)
- Catch
ex As
Exception
- MsgBox
(
ex.
Message
)
- End
Try
- con.
Close
(
)
- Call
Form1_Load(
sender, e)
- End
Sub
- End
Class
After reviewing all the codes above, you can now test your application by pressing “F5”.
Download
You must upgrade your account or reply in the thread to view hidden text.