Select from mysql put into variable VB.NET

Question

I am trying to Select data from MySQL database using VB.NET

Dim conn As New MySqlConnection
    Dim cmd As New MySqlCommand
    conn.ConnectionString = "Server=localhost; user id=root; password=; database=aplikasi_store_testing;"
    cmd.Connection = conn
    conn.Open()

    Dim Number As Integer
    cmd.CommandText = "SELCECT nama_student  FROM student where Id_student ='" & id & "'"

but i dont know how to put selected query into variable, anybody can help me ?

Your code is vulnerable to sql injection. It is not okay to use string concatenation like that to substitute a value into an sql query. — Joel Coehoorn, Apr 16 '13 at 03:32

score 7 · Answer 1 · answered Apr 30 '13 at 15:02

 Dim StrVar as String
 Dim rd As MySqlDataReader 
 Dim cmd as New MySqlcommand


 cmd.commandtext = "Select student_name from student_table where student_id = @ID"
 cmd.connection = conn
 rd = cmd.ExecuteReader

if rd.read then

    StrVar = rd.GetString(1)

end if
rd.close

Using the Data Reader it will let you assign the result of the query to your variable StrVar and this will come in handy. I use GetString because I assume it is a string type and GetValue for integer. The value "1" represent the column you want to pass to your variable.

Let me know if this works. Cheers..Happy Coding..

score 5 · Accepted Answer · edited May 23 '17 at 12:29

you can use ExecuteScalar method as below

object nama_studentObj = cmd.ExecuteScalar()
if nama_studentObj != null then
  string nama_student= nama_studentObj .ToString()

Full example code

    Dim cs As String = "Database=testdb;Data Source=localhost;" _
        & "User Id=testuser;Password=test623"

    Dim stm As String = "SELECT VERSION()"
    Dim version As String
    Dim conn As MySqlConnection

    Try
        conn = New MySqlConnection(cs)
        conn.Open()

        Dim cmd As MySqlCommand = New MySqlCommand(stm, conn)

        version = Convert.ToString(cmd.ExecuteScalar())

        Console.WriteLine("MySQL version: {0}", version)

    Catch ex As MySqlException
        Console.WriteLine("Error: " & ex.ToString())
    Finally
        conn.Close()
    End Try

Note :

Better to use parameters when you call database, like below

cmd.CommandText = "SELCECT nama_student  FROM student where Id_student = @Id_student"

then you have to add the parameter as

cmd.Parameters.AddWithValue("Id_student", id )

How do I create a parameterized SQL query? Why Should I?

Not a fan of "AddWithValue". It has to infer the sql type. Sometimes it gets it wrong, preferring nvarchar instead of varchar, nchar, or char, and this can break index use and force a conversion in each row in a table, resulting in horrible performance. — Joel Coehoorn, Apr 16 '13 at 19:09

score 2 · Answer 3 · answered Apr 16 '13 at 03:50

You can put it into DataSet

Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
conn.ConnectionString = "Server=localhost; user id=root; password=; database=aplikasi_store_testing;"
cmd.Connection = conn
conn.Open()

Dim id As Integer
cmd.CommandText = "SELECT nama_student  FROM student where Id_student ='" & id & "'" 

Dim da As New MySqlDataAdapter 'DataAdapter can be used to fill DataSet
Dim ds As New DataSet
da.SelectCommand = cmd
da.Fill(ds, "student") 'you can change student with the table name

From above command, your data will be stored in a DataSet.

Sample to use:

ds.Tables("student").Rows.Count 'Get the number of rows in the DataTable
ds.Tables("student").Rows(0).Item("nama_student").ToString 'Get first row of the nama_student field

You can check MSDN for further information:

DataSet: http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx

DataTable: http://msdn.microsoft.com/en-sg/library/system.data.datatable.aspx

DataRow: http://msdn.microsoft.com/en-sg/library/system.data.datarow.aspx

Note:

As mentioned by @Joel Coehoorn, try to look at Command Parameter http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlparameter.html

thanks for your help, but i think manually connect to MySQL make taste be diffirent — Vinra Gunanta Pandia, Apr 16 '13 at 05:10
there is no "manually connect to MySQL" and the way to connect to DB depends on your need. If you feel reading one by way is better for you, then use it. — Ruly, Apr 16 '13 at 05:42

Select from mysql put into variable VB.NET

3 Answers3

Note :

Linked

Related