CODEWITHSUNDEEP
vb.net
Leo Elvin Lee
Leo Elvin Lee

Reputation: 189

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

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

Upvotes: 0

Views: 969

Answers (1)

dotNET
dotNET

Reputation: 35380

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.

Upvotes: 1

Related Questions