Reading from a database and using an if statement

Question

The program currently reads from the database, but what I am trying to do is try to get the program to read from the database and if the field is empty then output "TBC" and if not then it will show the grade. I'm unsure of how to check what dr.Read is and use an if statement with it.

  Sub GradeResult()
         Dim dr As OleDbDataReader
         Dim cm As New OleDbCommand
         Dim cn As New OleDbConnection

        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=login.accdb"
        cn.Open()

        cm.CommandText = "SELECT ArGrade FROM loginDetails WHERE UserName = '" & username & "'"
        cm.Connection = cn
        dr = cm.ExecuteReader
        dr.Read()
        Label6.Text = dr.Item("ArGrade")

    End Sub

See https://stackoverflow.com/questions/576431/is-there-a-conditional-ternary-operator-in-vb-net — PM 77-1, Jan 03 '20 at 20:38
It doesn't look like you should be using a data reader at all. Use the right tool for the job. If you are executing a query to retrieve a single value then call `ExecuteNonScalar` and it will return that value. If your query has no matching records then the result is `DBNull.Value`, so you simply test for that. — jmcilhinney, Jan 04 '20 at 00:14

keenthinker · Answer 1 · 2020-01-05T20:44:30.493

0

dr is an instance of the OleDbDataReader class, and Read is one public method of this class. You need to call it in order to start reading the results after a query is executed against the database.

From the OleDbDataReader.Read documentation page:

Advances the OleDbDataReader to the next record.

You need to call it at least once, to get some results.

Returns Boolean true if there are more rows; otherwise, false.

Use it to check if you have any results at all or more results as one.

The default position of the OleDbDataReader is before the first record. Therefore, you must call Read to start accessing any data.

In your case you can check if there is any result like this:

   Label6.Text = "TBC" ' standard value is no value found      
   if dr.Read() then    ' DB contains any rows
    dim arGrade = dr.Item("ArGrage")
    if not IsDbNull(arGrade) then  ' and the ArGrade has a value
      Label6.Text = arGrade
     end if
   end if

And don't forget to close the reader with dr.Close.

edited Jan 05 '20 at 20:44

answered Jan 03 '20 at 20:52

keenthinker

7,645
2
35
45

I tried that but I keep getting: System.InvalidCastException: 'Conversion from type 'DBNull' to type 'String' is not valid.' – H97 Jan 04 '20 at 15:48
Did you checked if your query executed against the database for the specified username returns any results at all? – keenthinker Jan 04 '20 at 15:59
I did, I think the problem is because the DataAdapter is still reading something even if there isn't anything in the field. – H97 Jan 04 '20 at 17:08
DBNull means the query is returning no results (`null`for ArGrade) - does the database has any data at all? – keenthinker Jan 04 '20 at 17:14
Yes there is data in the database just not in the field that I am trying to access – H97 Jan 05 '20 at 16:14
I have edited and enhanced the code in my answer. If I understand correctly, then there are entries in the database for the specified username, but the `ArGrade` field does not contain a value! So a correct handling could look like this: check if there are records with `dr.Read`; check if `ArGrade` contains a value; if there is a value take it; otherwise take the default value `TBC`. – keenthinker Jan 05 '20 at 20:46

score 0 · Answer 2 · answered Jan 05 '20 at 17:35

0

Instead of using an if statement, when the user registers, the program writes TBC into the field.

answered Jan 05 '20 at 17:35

H97

31
4

Reading from a database and using an if statement

2 Answers2