Beginner needs help with OleDbException was unhandled, No Value given for one ...

[riccolmars]

Hi,

i have recently started using visual basic, and done very simple things such as create a word/notepad style program and other things. i then decided to make something more complex. Luckily enough, my mother who required something to help assist with her classes and programs she teaches she asked would it be possible to make such a program.
it is going well so far but it pulls up this problem while trying to load the database after i try to filter it.

Here is the code...

Private Sub Year_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ListYearGroup.SelectedIndexChanged


Dim YearG, SQLString As String
Dim dt As New DataTable()
Dim da As OleDbDataAdapter
Dim ConnectString As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = BlankPupilsdb.mdb"
YearG = YearGroup.Text
SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup = " & " ' " & YearG & " ' " & " "


da = New OleDbDataAdapter(SQLString, ConnectString)
da.Fill(dt) <------------------------------------------------- Here is where the problem occurs
DataGrid1.DataSource = dt
End Sub

in the form, which is called LessonForm there is :-

• Data Grid called DataGrid1
• label named YearGroup and the text inside is "Year Group
• List Box called ListYearGroup with "7,8,9,10,11" Inside
• BlankPupilsDataSet
• BlankPupilsDataSetBindingSource
• BlankPupilsDbTableAdapter

The Microsoft Access Database is called BlankPupilsdb.mdb

That is all the information i think i have to help solve the problem, knowing me it will be something very simple and all the info was not needed but better be safe. would it also be possible to ask not only for help to the problem but like reasoning why it happened and how and why the solution happens.

Hope someone could help, Thanks.

 

[riccolmars]

I have figured it out !!!!!

SQLString = "SELECT FirstName, LastName, YrGroup, Phase, Teacher, WeekDay, Period, ID " & _
"FROM tblPupils " & _
"WHERE YrGroup = '" & Me.ListYrGroup.SelectedItem & "'" 'that is how it use to look. this is how it looks now ...

SQLString = "SELECT FirstName, LastName, YrGroup, Phase, Teacher, WeekDay, Period, ID " & _
"FROM tblPupils " & _
"WHERE YrGroup = " & Me.ListYrGroup.SelectedItem 'Can you see where i have taken the extra " and ' out of the code there was just to many i don't understand why but i think the single quotes are telling SQL that the value is a varchar (or char) data type, thus causing the data type mismatch problem where as single " tells SQL that the value is an Integer/ Number. I know this will definitely make sense to you but i hope i explained it well enough. how i understand it as .. i was 'asking; it to take the number i was giving it, and return the rows of data, but unfortunately due to the quote marks in the place they were it was only accepting characters, while changing the quote marks when i sent the numbers it could receive numbers and return the row values ..

again thank you so so so much for all your help i feel i have learnt alot due to your help ..
and i appreciate it so much all i can say thank you & keep alook out for any more posts from me, you know what you are talking about so i would be grateful if you could help me out in the future!
Again thank you

Richard