-1

While using the given below code showing one error. the error is: Operator '=' is not defined for type 'DBNull' and string "True". Help me to find a proper solution. Thank you.

Code :

cmd1.CommandText = "select * FROM attendance where academic_year='" & yearTextBox.Text & "' and School_Name='" & courseDropDownList.Text & "' and Class='" & semesterDropDownList.Text & "' and batch='" & batchDropDownList.Text & "' and hour='" & DropDownList6.Text & "' and date_present='" & TextBox1.Text & "'"
sdr1 = cmd1.ExecuteReader
While sdr1.Read
  dr("student_name") = sdr1("student_name")
  dr("rollnumber") = sdr1("roll_number")
  dr("comment") = sdr1("comment")
Dim status As String = ""
  If sdr1("present") = "True" Then // ***Error popup here***
     status = "Present"
  ElseIf sdr1("Absent") = "True" Then
     status = "Absent"
  ElseIf sdr1("od") = "True" Then
     status = "OD"
  End If
  If sdr1("late") = "True" Then
     dr("status_late") = ", Latecomer"
  End If
     dr("status") = status
     dt.Rows.Add(dr)
     dr = dt.NewRow
End While
sdr1.Close()
Improve this question
2
  • 2
    break and see what values comes is sdr1("Present") .probably if its null,use .tostring there. Commented Apr 9, 2015 at 7:25
  • 2
    or check Isdbnull before using comparison operator. Commented Apr 9, 2015 at 7:27

3 Answers 3

Reset to default
3

Why are you doing this laboriously through code when SQL can do the work for you:

select
    student_name,
    roll_number,
    comment,
    CASE
       WHEN present='true' THEN 'present'
       WHEN absent ='true' THEN 'absent'
       WHEN od='true' THEN 'od'
    END as status,
    CASE
       WHEN late ='true' THEN ', Latecomer'
    END as status_late
FROM attendance where academic_year=@year

And also switch to using parameterized queries to supply @year rather than constructing strings together.

If you make the above change, you may also be able to switch to directly filling your DataTable with the result set rather than using ExecuteReader and looping and copying the results across.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

the problem is, that many programmers do not understand SQL and then make the fuzzy logic in the client
1

it means one of the retrieved value is NULL in the DB, therefore you get a value of type DBNull. you should first check whether the retrieved value is NOT NULL using .IsDBNull, then you can compare it using =.

Improve this answer

Comments

1

Actually, when reading data from the database, you must ensure that the value being read is nullable or compulsory.

For Nullable value, you are not allow to set it into a string for example. So, the best way when reading data from a database, you need to check for IsDBNull before setting the value, as follows;

If (sdr1("present") IsNot DBNull.Value) AndAlso sdr1("present") = "True" Then 
     status = "Present"
ElseIf (sdr1("Absent") IsNot DBNull.Value) AndAlso sdr1("Absent") = "True" Then
     status = "Absent"
ElseIf (sdr1("od") IsNot DBNull.Value) AndAlso sdr1("od") = "True" Then
     status = "OD"
End If

If (sdr1("late") IsNot DBNull.Value) AndAlso sdr1("late") = "True" Then
     dr("status_late") = ", Latecomer"
End If
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.