Question Cannot find the error thrown?

[gate7cy]

I have a form with three textboxes. (users, time, cost). According to what is written on the users box the winform makes some calculations and fills in the cost box. The user inserts the time and multiplies that with an integer pulled from the database according what name was in the users box. The sum is written on the cost box as I already said. When I run the program it throws an exception saying that no data could be found in the row/column. I have triple check my code and could not find anything. Here is code initiated with the text changed event on the time box.

Dim hours As Integer
        Dim cost As Integer
        Dim rate As Integer
        hours = Me.TextBox2.Text
        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=...........................................) 
conn.Open()
        Dim sql As String
        Dim reader As OleDbDataReader
        sql = "SELECT * FROM Users WHERE UserName = '" & Me.techniciansTextBox.Text & "'"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        reader = cmd.ExecuteReader
        rate = reader("HourRate").ToString
        cost = hours * rate

        Me.TextBox5.Text = cost

I am using VB 2008 expess and Access 2007 for database

 

[InertiaM]

1. Turn Option Strict On and correct the problems in your code.

2. Consider using NumericUpDown control (instead of textboxes) when prompting users for numbers.

3. Use parameterised queries - it's much safer. See the link in my signature.

4.

            If reader.HasRows Then
                While reader.Read
                    rate = Convert.ToInt32 (reader.Item("HourRate"))
                End While
            End If

5. If you only need HourRate, why are you retrieving all the other fields that you dont use? You only actually need one value, so use ExecuteScalar.

        Using cmd As New SqlCommand("SELECT HourRate FROM Users WHERE UserName = @TECHNICIAN", conn)
            'add the parameter value here
            Try
                rate = Convert.ToInt32 (reader.ExecuteScalar())
            Catch NRE As NullReferenceException
                MessageBox.Show("No technician with this name")
            End Try
        End Using

 

[gate7cy]

thanks for the reply. But even though I have made your suggested alterations I still have the same error. Any other clues.

 

[InertiaM]

Posting your new code and the exact error description might help us

 

[gate7cy]

here it is. Thanks...

    Private Sub TextBox2_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
        Dim hours As Integer
        Dim cost As Integer
        Dim rate1 As Integer
        hours = CInt(Me.TextBox2.Text)
        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\KoRnHoLiOs\Documents\Database2.accdb;Persist Security Info=False;")
        conn.Open()
        Dim sql As String
        Dim reader As OleDbDataReader
        sql = "SELECT HourRate FROM Users WHERE UserName = '" & Me.techniciansTextBox.Text & "'"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        reader = CType(cmd.ExecuteScalar, OleDbDataReader)
        If reader.HasRows Then
            While reader.Read
                rate1 = Convert.ToInt32(reader.Item("HourRate"))
            End While
        End If
        rate1 = CInt(reader("HourRate").ToString)
        cost = hours * rate1

        Me.TextBox5.Text = CStr(cost)

    End Sub

 

[rcombs4]

 reader = CType(cmd.ExecuteScalar, OleDbDataReader)
        If reader.HasRows Then
            While reader.Read
                rate1 = Convert.ToInt32(reader.Item("HourRate"))
            End While
        End If
        rate1 = CInt(reader("HourRate").ToString)

By using HasRows you are checking to see if the reader has any data, which is good. But then outside of that block of code you are trying to use data from the reader regardless. So the idea is if the query doesn't return any data you bypass the code that reads data. But your code only bypasses some of the code that reads data.

So it looks like your SQL is not returning any data and you are getting an error when you try to read the "HourRate" field(Last Line).

 

[gate7cy]

thanks for all the replies man. I have altered the code removing the the 'if' statement from the code but I still get the same error. Here is the new code.

    Private Sub TextBox2_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
        Dim hours As Integer
        Dim cost As Integer
        Dim rate1 As Integer
        hours = CInt(Me.TextBox2.Text)
        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\KoRnHoLiOs\Documents\Database2.accdb;Persist Security Info=False;")
        conn.Open()
        Dim sql As String
        Dim reader As OleDbDataReader
        sql = "SELECT HourRate FROM Users WHERE UserName = '" & Me.techniciansTextBox.Text & "'"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        reader = cmd.ExecuteReader()
        reader.Read()
        rate1 = Convert.ToInt32(reader.Item("HourRate"))

        rate1 = CInt(reader("HourRate").ToString)
        cost = hours * rate1

        Me.TextBox5.Text = CStr(cost)

    End Sub

 

[rcombs4]

are you sure your query is returning any results?

 

[cjard]

But even though I have made your suggested alterations

No you didn't

 

[gate7cy]

Thanks for te replies. guys. Have resolved the issue. I removed the 'If' statement due to the fact that the reader will always have a result and it works fine now.