13. Setting up a Database
using MYSQL from Scratch and Visual Basic (VB Studio.NET 2010, 2008, 2005) by Alan
Hagemeier and Jim Krumm (Modified 12/11/11)
1. Download the MySQL database server/client: at http://www.mysql.com
and select mysql community server>mysqlcommunity
server>windows>windowsx86 zip/setup>pick a mirror>new user>No
thanks just take me to the downloads>
mysql-5.0.41-win32.zip.
Then
install the executable by selecting custom. In the second window (as shown
below) select that developer components should be installed to your hard
drive, but go with the defaults on everything else. To download the VB.NET 2010 verion of the MySQL Database Project click here, for the 2008 version click here, and for the 2005 version click here.
Then
select skip sign-up. Go with Detailed configuration.>Developer
Machine>Multifunctional Database>C: Installation Path>Decision
Support (DSS)/OLAP>Enable TCP/IP Networking>Standard Character
Set>Install as Windows Service and Include Bin Directory in Windows Path
>Modify Security Settings (just done within MySQL…not as settings on
your computer) and type a root password (don’t forget
this…or you will have to reinstall it. Here I set the password to
"student") >Enable root access from remote machines>Execute
2. Download the MySQL ODBC driver (open
database connectivity) at database products>MySQL drivers>ODBC Driver for MySQL
>Download>windows>windows x86 zip/setup. Install as follows by first clicking Next as shown below:
Next Select Typical as shown below and click next:
Then click install as shown below:
Here I am using MySQL Connector/ODBC 3.51.
3. Create
Database and Tables by going to Start>All Programs>MySQL>MySQL Server 5.0 >MySQL command line
client>(you are now at a command line) type the password (I used "student") entered and
hit enter>
Then enter the following given in yellow:
|
It is important to note that a single
database file has not been created (unlike Access), rather it is put in a
collection of database files usually in a common set of files.
4. Making a Program
in Visual Basic:
A. Go to New Project>Visual
Basic Windows Application MyMySQLVBProgram
B. Make the following form:
C. Enter the following code to the form : This
program does not use any of the
built in database tools in the toolbox for Microsoft Visual Basic. It
makes
the link to the database from scratch. When running the program, hit
the open connection button before executing any of the other buttons,
and close the connection when finished.
Private mConnection As
Odbc.OdbcConnection
Private
Sub Form1_Load(ByVal
sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
End
Sub
Private
Sub Button1_Click(ByVal
sender As System.Object, ByVal e As
System.EventArgs) Handles cmdOpen.Click
' create the connection if it doesn't exist
If mConnection Is
Nothing Then
mConnection = New Odbc.OdbcConnection
' check if the connection is already open
If mConnection.State = ConnectionState.Open Then mConnection.Close()
' open the connection to the database
' string to store the connection string
Dim connectionstring As
String
Dim username As String = "root" 'CHANGE THIS
Dim password As String = "" 'CHANGE THIS
Dim server As String '"localhost"
or "127.0.0.1" or "192.164.215.243" or
"www.google.com" or "www.palomasoaps.com" or
"Bill.apollo.net"
Dim driver As String = "{MySQL ODBC 3.51 Driver}"
Dim database As String = "mysampledatabase"
' the connection string object
server = "localhost"
connectionstring = "Driver=" & driver & ";
Server=" & server & "; User=" & username &
"; Password=" & password & ";Database=" &
database & ";"
' try to open the connection
mConnection.ConnectionString = connectionstring
Try
mConnection.Open()
Catch ex As
Exception
MessageBox.Show(ex.ToString)
End Try
' check the state to see if it opened correctly
If mConnection.State <>
ConnectionState.Open Then
MessageBox.Show("Error Opening Connection")
ElseIf mConnection.State = ConnectionState.Open Then
MessageBox.Show("Connection opened!")
End If
End
Sub
Private Sub Button2_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cmdClose.Click
' if the connection hasn't been created
If mConnection Is
Nothing Then
Exit Sub
' if the connection isn't open then exit sub
If mConnection.State <>
ConnectionState.Open Then
MessageBox.Show("Connection not open!")
Exit Sub
End If
' try to close the connection
mConnection.Close()
If
mConnection.State = ConnectionState.Closed Then
MessageBox.Show("Connection closed!")
End If
End
Sub
Private Sub Button3_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cmdNumberRecords.Click
If mConnection.State <> ConnectionState.Open
Then Exit Sub
' create the command object to execute a sql
statement
Dim sqlCommand As
New Odbc.OdbcCommand
'result from sql statement
Dim result As Integer
' set the comamnd's connection
sqlCommand .Connection
= mConnection
' set the command's sql statement
sqlCommand.CommandText = "select count(*) as RecordCount from
nameExample"
' execute the query and get the first column of
the first row
result = sqlCommand.ExecuteScalar()
MessageBox.Show(result & " records in table nameExample!")
sqlCommand.Dispose()
sqlCommand = Nothing
End
Sub
Private Sub Button4_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cmdListRecords.Click
If mConnection.State <>
ConnectionState.Open Then Exit Sub
Dim sqlCommand As
New Odbc.OdbcCommand
Dim result As
Odbc.OdbcDataReader
sqlCommand.Connection = mConnection
sqlCommand.CommandText = "select * from nameExample"
result = sqlCommand.ExecuteReader
lstRecords.Items.Clear()
While result.Read()
lstRecords.Items.Add(result("last") & ", " &
result("first"))
End While
result = Nothing
sqlCommand.Dispose()
sqlCommand = Nothing
End
Sub
Private Sub Button1_Click_1(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cmdFind.Click
Dim lastname As String
Dim firstname As String
lastname = InputBox("Last name?", "Enter Last name",
"")
firstname = InputBox("First name?", "Enter First name",
"")
Dim sqlFind As
String
Dim sqlCommand As
New Odbc.OdbcCommand
Dim result As
Odbc.OdbcDataReader
Dim found As Integer
sqlFind = "select * from nameExample Where last like '%" &
lastname & "%' and first like '%" & firstname &
"%'"
sqlCommand.Connection = mConnection
sqlCommand.CommandText = sqlFind
result = sqlCommand.ExecuteReader
lstRecords.Items.Clear()
found
= 0
While result.Read
lstRecords.Items.Add(result("last") & ", " &
result("first"))
found = found + 1
End While
MessageBox.Show(found & " record(s) found!")
result = Nothing
sqlCommand.Dispose()
sqlCommand = Nothing
End
Sub
Private Sub cmdInsert_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cmdInsert.Click
Dim lastname As String
Dim firstname As String
lastname = InputBox("Last name?", "Enter Last name",
"")
firstname = InputBox("First name?", "Enter First name",
"")
If firstname = "" Or lastname = "" Then
MessageBox.Show("Both name fields are
required!")
Exit Sub
End If
Dim sqlInsert As
String
Dim sqlCommand As
New Odbc.OdbcCommand
Dim result As Integer
sqlInsert = "insert into nameExample(first, last) values ('"
& firstname & "', '" & lastname & "')"
sqlCommand.Connection = mConnection
sqlCommand.CommandText = sqlInsert
result = sqlCommand.ExecuteNonQuery()
If result = 0 Then
MessageBox.Show("No records inserted!")
Else
MessageBox.Show(result & " record inserted!")
End If
result = Nothing
sqlCommand.Dispose()
sqlCommand = Nothing
End
Sub
Private Sub cmdUpdate_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cmdUpdate.Click
Dim lastname As String
Dim firstname As String
Dim newlastname As
String
Dim newfirstname As
String
lastname = InputBox("Last name?", "Enter Last name",
"")
firstname = InputBox("First name?",
"Enter First name", "")
Dim sqlFind As
String
Dim sqlUpdate As String
Dim sqlCommand As
New Odbc.OdbcCommand
Dim sqlUpdateCommand As
New Odbc.OdbcCommand
Dim result As
Odbc.OdbcDataReader
Dim updatecount As
Integer
sqlFind = "select * from nameExample Where last = '" &
lastname & "' and first = '" & firstname &
"'"
sqlCommand.Connection = mConnection
sqlCommand.CommandText = sqlFind
result = sqlCommand.ExecuteReader
If Not
result.HasRows() Then
MessageBox.Show("No records found to modify!")
Else
newlastname = InputBox("New Last name", "Enter Last
name", result("last"))
newfirstname = InputBox("New First name", "Enter First
name", result("first"))
result.Close()
result = Nothing
sqlUpdate = "update nameExample set first = '" & newfirstname
& "', last = '" & newlastname & "' where first =
'" & firstname & "' and last = '" & lastname
& "'"
sqlUpdateCommand.Connection = mConnection
sqlUpdateCommand.CommandText = sqlUpdate
updatecount = sqlUpdateCommand.ExecuteNonQuery
If updatecount = 0 Then
MessageBox.Show("No records
updated!")
Else
MessageBox.Show("Record Updated!")
End If
End If
result = Nothing
sqlUpdateCommand.Dispose()
sqlCommand.Dispose()
sqlUpdateCommand = Nothing
sqlCommand = Nothing
End
Sub
Private Sub cmdDelete_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cmdDelete.Click
Dim lastname As String
Dim firstname As String
lastname = InputBox("Last name?", "Enter Last name",
"")
firstname = InputBox("First name?", "Enter First name",
"")
Dim sqlDelete As
String
Dim sqlCommand As
New Odbc.OdbcCommand
Dim result As Integer
sqlDelete = "delete from nameExample Where last = '" &
lastname & "' and first = '" & firstname &
"'"
sqlCommand.Connection = mConnection
sqlCommand.CommandText = sqlDelete
result = sqlCommand.ExecuteNonQuery
If result = 0 Then
MessageBox.Show("No records deleted!")
Else
MessageBox.Show(result & " record(s) deleted!")
End If
sqlCommand.Dispose()
sqlCommand = Nothing
End
Sub
Private Sub Button1_Click_2(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Button1.Click
If mConnection.State <>
ConnectionState.Open Then Exit Sub
Dim sqlCommand As
New Odbc.OdbcCommand
Dim result As
Odbc.OdbcDataReader
sqlCommand.Connection = mConnection
sqlCommand.CommandText = "select * from nameExample order by last,
first"
result = sqlCommand.ExecuteReader
lstRecords.Items.Clear()
While result.Read()
lstRecords.Items.Add(result("last") & ", " &
result("first"))
End While
result = Nothing
sqlCommand.Dispose()
sqlCommand = Nothing
End
Sub
End Class
0 comments:
Post a Comment