0

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'.

the problem

I think everyone will understand by looking the code. I can't explain much as my English isn't well enough.

Dmitry Shvedov
  • 3,169
  • 4
  • 39
  • 51
NeverStopLearning
  • 53
  • 1
  • 6
  • 1
    You joined the two tables with an [inner join](https://www.w3schools.com/sql/sql_join_inner.asp) (implicit). If `tabsen` can yield empty data you have to do a [right outer join](https://www.w3schools.com/sql/sql_join_right.asp) (or a left outer join when switching the tables). If both can be empty you have to go with a [full outer join](https://www.w3schools.com/sql/sql_join_full.asp). – Alex B. Mar 20 '17 at 17:42
  • *empty data = no data – Alex B. Mar 20 '17 at 17:55
  • now its fixed.thank you – NeverStopLearning Mar 25 '17 at 03:09

1 Answers1

0
... 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 ...

Or Left (Outer) Join 

... 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.

Alex B.
  • 2,145
  • 1
  • 16
  • 24