Getting error for Data Reader in vb.net

Question

Im facing the error when execute the data reader command in vb.net. it throw handling. This field like when you enter employee id in textbox then it will capture in database for other field name,department.

here is my code

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

Dim conn As New MySql.Data.MySqlClient.MySqlConnection

Dim strConnectionString As String =ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString

Dim sqlQuery As String = "SELECT * hr_record WHERE Emplid='" & txt1.Text & "'"

Using sqlConn As New MySqlConnection(strConnectionString)

      Using sqlComm As New MySqlCommand()

      With sqlComm

        .CommandText = sqlQuery

          End With

              Try
                  sqlConn.Open()

                    Dim sqlReader As MySqlDataReader = sqlComm.ExecuteReader()

                    While sqlReader.Read()

                        txt1.Text = sqlReader("Emplid").ToString()

                        TextBox1.Text = sqlReader("Nama").ToString()

                        TextBox2.Text = sqlReader("DeptDesc").ToString()

                    End While

                Catch ex As MySqlException

                    MessageBox.Show(ex.Message)
                End Try

            End Using

        End Using

    End Sub

@Arion i get the error "Connection must be valid and open." at the line 'Dim sqlReader As MySqlDataReader = sqlComm.ExecuteReader()' — kolapopo, Mar 31 '14 at 06:24
From the error it sounds like you connection string is a possibly incorrect. Separately you need to consider SQL Injection (see http://www.troyhunt.com/2013/07/everything-you-wanted-to-know-about-sql.html) your code is ripe for exploitation. — Simon, Mar 31 '14 at 07:13

Rahul · Accepted Answer · 2014-03-31T07:09:02.143

Try to change your select query like

Dim sqlQuery As String = "SELECT * from hr_record WHERE Emplid='" & txt1.Text & "'"

Note:- Cannot use code like that in Page_Load.Try to make one Function and call that function from Page_Load and always use Parameterized query.

Updated answer:

Page_Load

 Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
 DataBind()
 End Sub

Outside Method:

Public Sub DataBind()
Dim sConnection As String = "server=(local);uid=sa;pwd=PassWord;database=DatabaseName"
    Using Con As New MySqlConnection(sConnection)
        Con.Open()
        Using Com As New MySqlCommand("SELECT * from hr_record WHERE Emplid='"txt1.Text
        "'", Con)
            Using RDR = Com.ExecuteReader()
                If RDR.HasRows Then
                    Do While RDR.Read
                    txt1.Text = RDR.Item("Emplid").ToString()
                    TextBox1.Text = RDR.Item("Nama").ToString()
                    TextBox2.Text = RDR.Item("DeptDesc").ToString()
                    Loop
                End If
            End Using
        End Using
        Con.Close()
    End Using
End Sub

Note:Try to implement your logic like that and also modified as per your requirements.

Hope it works.

i just overwrite the code but still have same error. by the way what i should do?? icant write in the page load?? so where is the right location i must write it. — kolapopo, Mar 31 '14 at 06:45
You need to bind that `Page_Load` code in one method/Function and call that method/Function from `Page_Load`..and also pass your connection and query to `MySqlCommand`... — Rahul, Mar 31 '14 at 06:53
i have done what you have told me but right now seem it like no error but it nof functioning when i enter the ID ,the another field not fill the data from database. — kolapopo, Mar 31 '14 at 07:00

score 0 · Answer 2 · answered Mar 31 '14 at 07:07

0

You try to get Emplid in page load,but it's still nothing you should use button to check if Emplid exist

answered Mar 31 '14 at 07:07

Hussein Al-Obaidy

58
1
7

score 0 · Answer 3 · edited Mar 31 '14 at 11:30

Thanks to all thanks helping me..finally this code it works thanks to all . this the code will be function

 Dim conn As New MySql.Data.MySqlClient.MySqlConnection

 Dim strConnectionString As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString


    Using sqlConn As New MySqlConnection(strConnectionString)
        sqlConn.Open()
        Using sqlComm As New MySqlCommand()

            sqlComm.Connection = sqlConn
            With sqlComm


                .CommandText = "SELECT * from hr_record WHERE Emplid='" & txt1.Text & "'"


            End With

            Try


                Dim sqlReader As MySqlDataReader = sqlComm.ExecuteReader()

                While sqlReader.Read()

                    txt1.Text = sqlReader("Emplid").ToString()

                    TextBox1.Text = sqlReader("Nama").ToString()

                    txtdep.Text = sqlReader("DeptDesc").ToString()

                End While

            Catch ex As MySqlException

                MessageBox.Show(ex.Message)
            End Try
            sqlConn.Close()
        End Using

    End Using

End Sub

Getting error for Data Reader in vb.net

3 Answers3