Geek Answers Handbook

Choose from mysql put into VB.NET variable

I am trying to select data from a 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 don’t know how to put the selected query into a variable, can anyone help me?

+4
source share
3 answers

you can use the ExecuteScalar method as below

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

Full code example

  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:

It is better to use parameters when calling the database, for example below

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

then you should add the parameter as

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

How to create a parameterized SQL query? Why should I?

+4
source 
  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 will allow you to assign the query result to your StrVar variable, and this will come in handy. I use GetString because I assume it is a string type and GetValue for the whole. The value "1" represents the column that you want to pass to your variable.

Let me know if that works. Greetings .. Good encoding ..

+6
source

You can put it in a 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 the above command, your data will be saved in the DataSet .

Sample for 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 the MSDN for more 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 @Joel Coehoorn mentioned, try looking at the command parameter http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlparameter.html

+2
source

More articles:


All Articles