How to get a specific data from mysql database vb.net

Question

My code searches for the same value of the selected item of a listview in the database for example: "01" the code searches for "01" in the database, now 01 in the database is equivalent to a name for example 01 = Name, my problem is; If the code founds 01 i want to get the name instead of the 01.

My Code

     Try
        Call DatabaseConnection()
        MySqlConn.Open()
        For Each item As ListViewItem In ListViewAttendance.SelectedItems
            Query = "select * from dtr_database.dtr_entries where dtr_entry_number= '" & item.SubItems(0).Text & "'"
            Command = New MySqlCommand(Query, MySqlConn)
            Reader = Command.ExecuteReader
            Dim Count As Integer
            Count = 0
            While Reader.Read
                Count = Count + 1
            End While
            If Count = 1 Then

                'if 01 is found Get Name of 01. How to do this?

                MessageBox.Show("Record Found")
            ElseIf Count > 1 Then
                MessageBox.Show("Multiple Records Found")
            Else
                MessageBox.Show("Record Found2")
            End If
        Next
        MySqlConn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

dotNET · Accepted Answer · 2015-02-24 04:16:20Z

1

Although your code has many issues, the direct answer to your question is that you should call Reader.GetString(ColumnNumber) inside the While loop to get the value of Name column.

However, there are a few things you could improve:

First and the foremost, you should not execute queries directly by concatenating strings except for the most trivial applications. This can lead to all sorts of troubles, e.g. not the least of which is SQL Injection.
Do not use SELECT * in your queries. This will bring in all of the columns of your table whether you need them in the current scenario or not. Also, you'll not be sure of the order of the columns. Instead specify the column names you need in your query.
Go for ADO.NET DataSets or EF if possible. They can create easy-to-use DataAdapters and wrapper methods for your queries that work just like standard vb.net functions. Plus they use command parameters to safeguard you against the odds of SQL injection.
If you are inclined towards directly using SqlCommand in your code, try using ExecuteScalar() if you simply need to fetch a single column (Name column in your case). That will save you from having to run the Read() loop.

edited Feb 24, 2015 at 4:16

answered Feb 24, 2015 at 4:08

dotNET

35.7k29 gold badges177 silver badges277 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Leo Elvin Lee Over a year ago

I started from adodb and going down to difficult ones, i have to do more researches regarding your recommendations so i can adapt a better and more efficient way, cheers!

Collectives™ on Stack Overflow

How to get a specific data from mysql database vb.net

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related