如何在 vb.net 中检索 mysql 数据？

how to retrieve mysql data in vb.net?

提问by Sheerwood John Caday

im trying to retrieve mysql data with specific column and show to textbox in vb.net. what should i do in retrieving it?

我试图检索具有特定列的 mysql 数据并显示到 vb.net 中的文本框。我应该怎么做才能取回它？

Dim connect As New MySqlConnection("server=localhost; user id=root; password= ; database=ticketing_system;")
    connect.Open()

    Dim sqladapter As New MySqlDataAdapter
    Dim sqlcmd As New MySqlCommand
    Dim dr As MySqlDataReader
    Dim dt As New DataTable

    sqlcmd = New MySqlCommand("SELECT * complaint WHERE tran_no='" & lbltranno.Text & "'")
    **THEN? WHAT SHOULD I DO TO DISPLAY DATA? PLEASE HELP**

    connect.Close()

采纳答案by user4391303

Imports System.Data.SqlClient
Public Class Form1
Dim c As New SqlConnection("Data Source=SONY;Initial Catalog=msdb;Integrated Security=True")
Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter
Dim dr As SqlDataReader
Dim ds, ds1 As New DataSet
Private Sub btnsub_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles      btnsub.Click
    Try
        ds.Clear()
        c.Open()
        cmd = New SqlCommand("select Sub_Name from Subject_Detail WHERE Course='" + txtcourse.Text + "'", c)
        da = New SqlDataAdapter(cmd)
        da.Fill(ds, "Subject_Detail")
        Label1.Text = ds.Tables(0).Rows(0).Item(0)
        Label2.Text = ds.Tables(0).Rows(1).Item(0)
        Label3.Text = ds.Tables(0).Rows(2).Item(0)
        Label4.Text = ds.Tables(0).Rows(3).Item(0)
        Label5.Text = ds.Tables(0).Rows(4).Item(0)
        Label6.Text = ds.Tables(0).Rows(5).Item(0)
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        c.Close()
    End Try
End Sub
End Class

'This code display all subject name from the table into labels.This might be helped.

'此代码将表中的所有主题名称显示到标签中。这可能会有所帮助。

回答by JRO

You can try this:

你可以试试这个：

Dim connString As String = "server=localhost; user id=root; password= ; database=ticketing_system;"
Dim sqlQuery As String = "SELECT * complaint WHERE tran_no='" & lbltranno.Text & "'";
Using sqlConn As New MySqlConnection(connString)
    Using sqlComm As New MySqlCommand()
        With sqlComm
            .Commandtext = sqlQuery
        End With
        Try
            sqlConn.Open()
            Dim sqlReader As MySqlDataReader = sqlComm.ExecuteReader()
            While sqlReader.Read()
                Label1.Text = sqlReader("Name").ToString()
                Label2.Text = sqlReader("Points").ToString()
            End While
        Catch ex As MySQLException
            ' add your exception here '
        End Try
    End Using
End Using

回答by Nunners

You are simply missing the Execution method. It depends on what kind of result you want. If you only want the first result from the query (first row and first column) then use sqlcmd.ExecuteScalar().

您只是缺少 Execution 方法。这取决于你想要什么样的结果。如果您只想要查询的第一个结果（第一行和第一列），则使用sqlcmd.ExecuteScalar().

If you want all the results you'll have to load that into a MySqlDataReader using the method sqlcmd.ExecuteReader()

如果您想要所有结果，则必须使用该方法将其加载到 MySqlDataReader 中 sqlcmd.ExecuteReader()

Using ExecuteReader():

使用ExecuteReader()：

Dim connect As New MySqlConnection("server=localhost; user id=root; password= ; database=ticketing_system;")
connect.Open()

Dim sqladapter As New MySqlDataAdapter
Dim sqlcmd As New MySqlCommand
Dim dr As MySqlDataReader
Dim dt As New DataTable

sqlcmd = New MySqlCommand("SELECT * complaint WHERE tran_no='" & lbltranno.Text & "'")
dr = sqlcmd.ExecuteReader()
dt.Load(dr)
'Useable datatable in dt variable...

connect.Close()

Using ExecuteScalar():

使用ExecuteScalar()：

Dim connect As New MySqlConnection("server=localhost; user id=root; password= ; database=ticketing_system;")
connect.Open()

Dim sqladapter As New MySqlDataAdapter
Dim sqlcmd As New MySqlCommand
Dim dr As String
Dim dt As New DataTable

sqlcmd = New MySqlCommand("SELECT [COLUMN NAME] complaint WHERE tran_no='" & lbltranno.Text & "'")
dr = sqlcmd.ExecuteScalar()
'dr now contains the value of [COLUMN NAME] for the first returned row.

connect.Close()

回答by chiellie013

Try this one, i always use this code, that's why i'm pretty sure that this will work...

试试这个，我总是使用这个代码，这就是为什么我很确定这会起作用......

Imports MySql.Data
Imports MySql.Data.MySqlClient

Dim connect As New MySqlConnection("server=localhost;uid=root;database=ticketing_system;pwd=;")

connect.Open()

Dim sqlcmd As New MySqlCommand("SELECT * complaint WHERE tran_no='" & lbltranno.Text & "'")
Dim sqladapter As New MySqlDataAdapter(sqlcmd)
Dim dt As New DataTable
sqladapter.Fill(dt)

Datagridview1.Datasource = dt

connect.Close()

回答by Ma Amelita Alcansare

Imports MySql.Data

Imports MySql.Data.MySqlClient

Dim dbConnection As New MySqlConnection("server=localhost;uid=youruser;database=yourdb;pwd=password")

try
dbConnection.open
catch ex as exception
debug.print(ex.message)
end try

using Adapter as new mysqldataadapter("select * from yourtable where yourcolumn = 'yourvalue'", dbConnection)
   using Table as new datatable
     Adapter.fill(Table)
         Datagridview.datasource = table
   end using
end using

dbConnection.close

回答by Thurman Jenner

    Dim MysqlConn As MySqlConnection
    Dim connectionString As String = "Server=-host-ip-;Database=Mysqldb;Uid=user;Pwd=password"
    MysqlConn = New MySqlConnection(connectionString)
    Try

        Dim Mysqlcmd As New MySqlCommand("SELECT * FROM Mysqldb.table WHERE col='" & Label6.Text & "'", MysqlConn)
        Dim dt As New DataTable
        Dim Mysqladapter As New MySqlDataAdapter()
        MysqlConn.Open()
        Mysqladapter.SelectCommand = Mysqlcmd

        Mysqladapter.Fill(dt)

        DataGridView1.DataSource = dt
        MessageBox.Show("Connection to Database has been opened.")

        MysqlConn.Close()

    Catch myerror As MySqlException

        MessageBox.Show("Cannot connect to database: " & myerror.Message)

    Finally

        MysqlConn.Dispose()

    End Try

The key is to add the connection at the end of the mysql query as in line 6

关键是在mysql查询的末尾添加连接，如第6行

回答by Geneous42

I used a variation of the code given in this thread with Visual Basic 2015. I found I needed a 2nd argument in the MySqlCommand statement, namely the MySqlConnection variable ("connect" in some of the examples). See thread: Connection must be valid and open VB.Net

我在 Visual Basic 2015 中使用了此线程中给出的代码的变体。我发现我需要 MySqlCommand 语句中的第二个参数，即 MySqlConnection 变量（在某些示例中为“connect”）。请参阅线程：连接必须有效并打开 VB.Net

Thanks for the help.

谢谢您的帮助。

回答by Carlos H

Imports MySql.Data.MySqlClient 


''--- Data Reader Example 

private _cn as New MySqlConnection( _ 
"data source=server; database=databaseName; user id=username; password=password") 
private cmd as new MySqlCommand 
private dr as MySqlDataReader 

With cmd 
.Command = "select Name, Address from Clients" 
.CommandType = CommandType.Text 
.Connection = cn 
End With 

cn.Open() 

dr = cmd.ExecuteReader 

While dr.Read 
   '' use dr(0) for get Name, and dr(1) to get Address for this example
   Console.WriteLine(dr(0) & " - " & dr(1)) 
   Console.WriteLine(dr("Name").ToString() & " - " & dr("Address").ToString())       

End While 

dr.Close 
cn.Close