I'm trying to collect data from joining table and currently there is a null displaying (no error found). I think my code isn't efficient tough. But here it is what I'm trying:
conn.Open()
sqlcmd = New MySqlCommand("select tabsen.id_absen, tsiswa.NIS, tsiswa.nama, tabsen.alpa, tabsen.izin, tabsen.sakit, tabsen.tahun_ajaran from tabsen join tsiswa on tabsen.NIS = tsiswa.NIS where tsiswa.NIS like '%" & txtnis.Text & "%'", conn)
dr = sqlcmd.executereader()
dr.Read()
If dr.HasRows Then
txtid.Text = dr.Item("id_absen")
txtnis.Text = dr.Item("NIS")
txtnama.Text = dr.Item("nama")
txta.Text = dr.Item("alpa")
txti.Text = dr.Item("izin")
txts.Text = dr.Item("sakit")
cmbtahun.Text = dr.Item("tahun_ajaran")
txta.Focus()
btnsave.Text = "UPDATE"
btndelete.Enabled = True
txtjumlah.Enabled = True
cmbpredikat.Enabled = True
cmbtahun.Enabled = True
txtnis.Enabled = False
dr.Close()
Else
While dr.Read()
txtnama.Text = dr("nama")
End While
dr.Close()
MsgBox("data absensi belum diisi")
End If
conn.Close()
If row found, it works like a charm. But when its empty on table 'tabsen', there is no error but txtnama.Text didn't show their 'nama'.
I think everyone will understand by looking the code. I can't explain much as my English isn't well enough.
... from tabsen join tsiswa on ...
This produces an Inner Join on the two tables which will only provide results if both tables have matching data.
Use either Right (Outer) Join
... from tabsen right join tsiswa on ...
... from tsiswa left join tabsen on ...
Having said that, be aware that if tabsen does not contain matching rows all of its attributes are NULL values.
So you have to check that when assigning values from the datareader to the textboxes:
Dim id_absen = dr.Item("id_absen")
txtid.Text = If(id_absen<> DBNull.Value, id_absen.ToString(), String.Empty)
...
Btw...I would recommend you set Option Strict On at the very top of your VB file. Will give you some compile errors but save you alot of mean bugs in the future.