Consulting

Results 1 to 2 of 2

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

  1. #1
    VBAX Newbie
    Joined
    Nov 2016
    Posts
    1
    Location

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

    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. screenshot.jpgIt 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!

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •