SQL not working for numbers but works for text, how can i make change

[ttl32]

Thanks for reviewing my question!

I am trying to output a list of data from my database. and I want make the output changing when click different thing in the combobox.

I have the following code, it is almost the same for both code of them, however I can only run the second one which contains program(TEXT), but not the first one which contains year(INTEGER). I am wondering maybe because
yearcombobox contains number instead of text which make them different.




Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles yearComboBox.SelectedIndexChanged
RankListBox.Items.Clear()
yearcommand.CommandText = "select rank, universityname from program as p, ranking as r, university as u,institution as i where r.insid = i.insid and p.pid = r.pid and u.uid = r.uid and programName = '" & ProgramNameComboBox.Text & "' and i.institutionName = '" & institutionNameComboBox.Text & "' and r.year = " & yearComboBox.Text & " order by rank"


Dim yearcheck As Object = yearcommand.ExecuteScalar()
If yearcheck Is Nothing Then
RankListBox.Items.Add("There is no ranking in current criterias!!!")
Else
Dim yeardatareader As OleDbDataReader = yearcommand.ExecuteReader()
Do While yeardatareader.Read()
RankListBox.Items.Add(yeardatareader.GetValue(0) & "-" & yeardatareader.GetValue(1))
Loop
yeardatareader.Close()
End If


End Sub


Private Sub ProgramNameComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ProgramNameComboBox.SelectedIndexChanged
'clear
RankListBox.Items.Clear()
'populate rank and university into listbox
procommand.CommandText = "select rank, universityname,year from program as p, ranking as r, university as u, institution as i where i.insid = r.insid and p.pid = r.pid and u.uid = r.uid and programName = '" & ProgramNameComboBox.Text & "' and i.institutionName = '" & institutionNameComboBox.Text & "' and r.year = " & CyearComboBox.Text) & " order by rank"


Dim procheck As Object = procommand.ExecuteScalar()
If procheck Is Nothing Then
RankListBox.Items.Add("There is no ranking in current criterias!!!")
Else
Dim prodatareader As OleDbDataReader = procommand.ExecuteReader()
Do While prodatareader.Read()
RankListBox.Items.Add(prodatareader.GetValue(0) & " - " & prodatareader.GetValue(1))
Loop
prodatareader.Close()
End If


End Sub

When I run the code and try to change the program combobox it works fine, however when i changed the year combobox, it will say error, and here is the screeshoot. 17673It said " System.NullReferenceException' and provide additional information: Object reference not set to an instance of an object.

What can I do, I really dont know whats wrong, I think I did the null exception already.

Thanks for your help!

[SamT]

All UserForm Controls contain only Type:=Text Values,

If the Data base is expecting a number Type, (Integer, Long, or Double,) you need to convert the yearcombobox Value to the required numerical type. CInt(yearcombobox); Clng(yearcombobox); Cdbl(yearcombobox).

If the DB Year field is Type:=Date, Convert with CDate(yearcombobox)

You might have to precede Controls with the "Me" Object Keyword. Me.yearcombobox