Quote:
|
Originally Posted by Ktech22
I am familiar with visual basic but have never used MySQL before.
|
This is not surprising.
MySQL tends to be used on Linux (Apache / Tomcat) servers more than Windows servers
and most Linux MySQL setups use php (not VB.Net) to access and query the MySQL database.
Quote:
|
Originally Posted by Ktech22
I have queried a database into MySQL and server connection to the database is working fine.
Furthermore I downloaded MySQL a week ago so I have the latest connector.
|
Establishing the connection is half the battle, but it would have been nice for you to have shown what code you were using,
just so we have a starting point for figuring out how the code needs to be modified
to complete the rest of what you want to do..
Failing that all I can do is give you the basic code for dealing with MySQL under .Net
(once you have the
.Net MySQL connector downloaded and
properly referenced):
Code:
Imports MySql.Data.MySqlClient
Public Class Form1
Private MyConn As MySqlConnection
Private MyComm As MySqlCommand
Private MyCmd As New MySqlCommand
Dim conn As MySqlConnection
Dim SqlConn As String = "Server=xx.xx.xxx.xxx;Port=3306;Database=some_name;Uid=some_name;Pwd=something;"
'cmdLogin is a command button control
Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
'assign the row number based on a the value
'of a trackbar slider control
Dim strRowNum As String = tb1.Value
Try
MyConn = New MySqlConnection
MyConn.ConnectionString = SqlConn
MyConn.Open()
'MessageBox.Show("Debug: Connection to Database has been opened.")
Using MyConn
Dim command As MySqlCommand = New MySqlCommand("SELECT * FROM tablename WHERE field0 =" & strRowNum & ";", MyConn)
Dim reader As MySqlDataReader = command.ExecuteReader()
If reader.HasRows Then
Do While reader.Read
'txt1 is a Textbox control with multiline property set to True
txt1.Text = txt1.Text & reader.GetInt32(0) & ", " & reader.GetString(1) _
& ", " & reader.GetString(2) & ", " & reader.GetString(3) _
& ", " & reader.GetString(4) & ", " & reader.GetString(5) _
& ", " & reader.GetString(6) & ", " & reader.GetString(7) & vbCrLf
Loop
End If
End Using
MyConn.Close()
Catch myerror As MySqlException
MessageBox.Show("Cannot connect to database: " & myerror.Message)
Exit Sub
Finally
MyConn.Dispose()
End Try
End Sub
End Class
Some additional notes:
1.) The SQL connection string formatting is critical.
Here is a good reference
2.) Of course "reader.GetInt32" is used for numerical data (like primary key auto-increment fields),
but "reader.GetInt16" may be substituted in small databases.
3.) Also, of course, "reader.GetString" is used for non-numeric data,
but I use it for UPC numbers as well ("field7") since the database was exported from a Linux
proprietary program and for interchange purposes may be "Null"
Please see the GoDaddy Starfield Technologies screenshot attached below
(other phpMyAdmins may be different, though),
and note "field0" through "field7" are considered columns names for MySQL command purposes.
4.) You would think that the Microsoft MSDN will be totally useless for MySQL
since Microsoft has its own SQL Server setup, but if you take a look at
this page you will notice a striking similarity in the way the reader code is structured.
5.) This is the most super-simple "just-get-it-working" code example.
A more real world example would probably use a datagrid,
or use a loop to sequentially load the data into an array for storage before using it,
for instance, to create (custom draw) graphs and charts.
6.) As regard "mysql.data" I really have no idea what you are talking about
because you failed to adequately cite/reference the YouTube videos you mentioned in passing,
but once the MySQL connection is established all data acquisition (queries)
can be done through feeding the right
SQL commands for what you want.
edit: Maybe you having trouble referencing the dll from within the VB.Net IDE.
In which case these links might be helpful (1, 2, 3)
Can't find mysql on the add reference section
Linear Mode
