1 Replies - 1931 Views - Last Post: 23 July 2014 - 09:52 AM Rate Topic: -----

#1 skyscraper11   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 23-July 14

can't read database records with MySQL Data Reader dr.HasRows

Posted 23 July 2014 - 07:46 AM

when i click the button 2 with the valid ID No. on the text box it always shows the message box "Invalid ID No." but if i remove the IF statement, it shows database records and it works fine, but i need this IF statement, i think the problem here is the dr.HasRows but i don't know what to put.

Imports MySql.Data.MySqlClient

Public Class Form16
    Private Sub Form16_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim con As New MySqlConnection("server=localhost;user id=root;password=root;database=db")
        Dim DataSet1 As New DataSet
        Dim dr As MySqlDataReader
        Dim da As New MySqlDataAdapter
        Dim cmd As New MySqlCommand

        con.ConnectionString = "server = localhost; user id = root;password=root; database = db"
        cmd.Connection = con
        con.Open()
        cmd.CommandText = "select * from voter where idn=" & TextBox1.Text & ""
        dr = cmd.ExecuteReader
        con.Close()
        da.SelectCommand = cmd
        da.Fill(DataSet1, "db")


        If dr.HasRows Then
            Label2.DataBindings.Add("text", DataSet1, "db.fname")
            Label10.DataBindings.Add("text", DataSet1, "db.mi")
            Label11.DataBindings.Add("text", DataSet1, "db.lname")
            Label12.DataBindings.Add("text", DataSet1, "db.yr")
            Label13.DataBindings.Add("text", DataSet1, "db.sec")
            Label14.DataBindings.Add("text", DataSet1, "db.vstatus")
        Else
            MessageBox.Show("Invalid ID No.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

        Label2.DataBindings.Clear()
        Label10.DataBindings.Clear()
        Label11.DataBindings.Clear()
        Label12.DataBindings.Clear()
        Label13.DataBindings.Clear()
        Label14.DataBindings.Clear()
    End Sub
End Class



and when i leave the textbox blank it has an error that says
MysqlException was unhandled
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


and it points at
dr = cmd.ExecuteReader


Is This A Good Question/Topic? 0
  • +

Replies To: can't read database records with MySQL Data Reader dr.HasRows

#2 andrewsw   User is offline

  • Unprocessable Entity
  • member icon

Reputation: 6582
  • View blog
  • Posts: 26,732
  • Joined: 12-December 12

Re: can't read database records with MySQL Data Reader dr.HasRows

Posted 23 July 2014 - 09:52 AM

If you leave the TextBox blank than the sql statement ends abruptly as ".. where idn=", which is invalid syntax. You could use an if-statement to check if the TextBox is empty (IsNullOrEmpty()) and, if so, use an alternative version of the sql-statement that doesn't have the where-clause.

ExecuteReader returns a number of rows. You need to Read, and iterate, the rows as in this example from the MySQL docs:
Public Sub ReadMyData(myConnString As String)
    Dim mySelectQuery As String = "SELECT OrderID, CustomerID FROM Orders"
    Dim myConnection As New MySqlConnection(myConnString)
    Dim myCommand As New MySqlCommand(mySelectQuery, myConnection)
    myConnection.Open()
    Dim myReader As MySqlDataReader
    myReader = myCommand.ExecuteReader()
    ' Always call Read before accessing data.
    While myReader.Read()
        Console.WriteLine((myReader.GetInt32(0) & ", " & myReader.GetString(1)))
    End While
    ' always call Close when done reading.
    myReader.Close()
    ' Close the connection when done with it.
    myConnection.Close()
End Sub 'ReadMyData

This post has been edited by andrewsw: 23 July 2014 - 09:53 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1