how to retrieve mysql data in vb.net?
45,078
Solution 1
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.
Solution 2
You can try this:
Dim connString As String = "server=localhost; user id=root; password=[REPLACE WITH PASSWORD] ; database=ticketing_system;"
Dim sqlQuery As String = "SELECT * from 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
Solution 3
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()
.
If you want all the results you'll have to load that into a MySqlDataReader using the method sqlcmd.ExecuteReader()
Using 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()
:
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()
Solution 4
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()
Solution 5
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
Author by
Sheerwood John Caday
Updated on July 09, 2022Comments
-
Sheerwood John Caday almost 2 years
im trying to retrieve mysql data with specific column and show to textbox in vb.net. what should i do in retrieving it?
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()