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.

 

[Estuardo]

G'd evening,
First of all, Welcome to the forum!
Well unless you have intentionally omitted your db path, and the security parameter in your connection string, the error starts there.
I don't use to "tell" people how do they should do the things they do when they don't ask for. This time i'll make an exception because you say you're beginner.
1. Use a coding convention for your variables and objects. Choose any one you like and stick to it.
2. Avoid dynamic parameters in your data access. (check this). There are hundreds of pages talking about this subject.
3. Make your code readable. Is easier to Dim all string variables in the same line, but is more clear one by line

       [COLOR="#0000FF"] Dim[/COLOR] YearG  [COLOR="#0000FF"]As String[/COLOR]
       [COLOR="#0000FF"] Dim[/COLOR] SQLString[COLOR="#0000CD"] As String[/COLOR]
       [COLOR="#0000FF"]Dim[/COLOR] dt [COLOR="#008080"]As New DataTable()[/COLOR]
       [COLOR="#0000FF"] Dim[/COLOR] da [COLOR="#008080"]As OleDbDataAdapter[/COLOR]

       [COLOR="#0000FF"] Dim[/COLOR] ConnectString [COLOR="#0000FF"]As String[/COLOR] = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =[u][b]BlankPupilsdb.mdb[/b];[i]Persist Security Info=False;[/i][/u]"

        ' YearG = Me.txtYearGroup.Text
        SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup = '" & Me.txtYearGroup.text & "'"

        da = New OleDbDataAdapter(SQLString, ConnectString)
        da.Fill(dt)[COLOR="#008000"] ' <------------------------------------------------- Here is where the problem occurs... No, there is where it stops![/COLOR]
        DataGrid1.DataSource = dt

G'd Luck

 

[riccolmars]

Thankyou for the info and all the help. since it is 1.30 in morning and im pretty tired... but i quickly amended the code,

"

​

' YearG = Me.txtYearGroup.Text​

SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup = '" & Me.txtYearGroup.text & "'"

" ' YearG = Me.txt.YearGroup.text" - if you get rid of the ' the First one becomes an Error and

the 2nd " & Me.txtYearGroup.text &" gets and Error on it so the program wont run.

Am i inputting the code in wrong or am i doing something wrong ? or made an error ? the code wont run or will run from last working place, but not with the code you amended wont run at the minute
​

like i said it is early so i maybe doing something wrong and i will set on reading all the info when i wake in morning, but any help ?

​

 

[Estuardo]

Hello again!,
I'm sorry i lead you to made a mistake. In order to make the Me.txtYearGroup.text works you must change the name of your text box within the designer.
But just for this test set the parameter manually something like

SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup='2012'"[COLOR="#008000"] 'This parameter is assuming that the value is a year, You'll need to change it to the right value.

[/COLOR]

If works that way, the problem is just the textbox name.
By the way for me right now are 2:00am but i'l be here a few hours more.

 

[riccolmars]

Bet your sick of me already !!
it seems ur on same time zone as me where are you from ?
back to being a pain ... i amended the code both the " SQLString = "SELECT ..." & the Me.txtYearGroup.text ..because i have it as a listbox instead of textbox and the name as ListYearGroup i changed them the Me.ListYearGroup.Text and you would never guess what .. the problem seems back to square one! at the same point "da.Fill(dt)" and the box that came up to the side had OleDbException was unhandled, No value given for one or more required parameters. Will it help if i say that from the statement "da.fill(dt)" the .Fill(dt) is highligted in a yellow backgroundwhile the "da" was in a greyish background, same as the other da in the code... im sorry if im being over obvious. its i just dnt want to not give you enough info since you are trying to help... its just me i think .. from this to my disability i seem to make everything be really awkward to solve lol

 

[Estuardo]

First off If we're here is because we want to. I don't consider any one a pain mainly when they show interest in what they're doing not just to solve a problem but to learn, that i think is your case.
Like i'd suggested in my previous post: set the parameter manually. If it works we've found where the problem is.
Don't worry about your dt. It breaks because something with what he is working on, is wrong. You supplied two parameters the connection string and the sql query.
If your connection is ok the problem should be with the qry, that in turns needs a parameter. So, we need to look for the error in that part.

For what i see you must be firing this event from the lisbox SelectedValueChanged or something similar. To get a value from your Listbox you should do this Me.ListYearGroup.SelectedItem, so your sql should be look like this

SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup=[b]'[/b]" &  Cstr(Me.ListYearGroup.SelectedItem) & "[b]'[/b]"

Please add this code to your procedure:

        Dim YearG  As String
        Dim 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;Persist Security Info=False;"
    [COLOR="#000080"]    Dim cnn As New OleDb.OleDbConnection(ConnectString)

         cnn.Open[/COLOR]

        ' YearG = Me.txtYearGroup.Text
        SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup = '" & Cstr(Me.ListYearGroup.SelectedItem) & "'"
       [COLOR="#000080"]  msgbox ("Sql String " & SQLString)[/COLOR]
         
        da = New OleDbDataAdapter(SQLString, ConnectString)
        da.Fill(dt) ' <------------------------------------------------- Here is where the problem occurs... No, there is where it stops!
        DataGrid1.DataSource = dt

If there is anything wrong with your connection string it will break. The messagebox is yo actually see what the full sql qry is. Again, try first setting the parameter manually, then try with your listbox.
Before i forget, i'm Portugal.

 

[riccolmars]

Ive not managed to get to reading the info you sent me YET, but i will eventually. what i have been doing is trying to sort this out and its always the same problem what ever i do and i messed about with it so much that i had to delete everything on the form and start again .. so i copied the code u sent me to amend the situation and same problem .. and i think it is the parameters situation.. which i cant seem to get my head around. ive tryed looking it up online and even on youtube and its just not sicking in and im getting stuck over and over again.. how do i set the parameters manually ?? and try what with the listbox ?

What happens is even when the message box comes up with the SQL String stuff inside.. i click ok and the error appears again and again .. i just stuck and dnt know what to do?
is there anything u can suggest to help?
maybe a step by step to set the parameters ?
or would u like to finish the fing for me ? lol (joke)
again, sorry bet its something really simple and i look a total incompetent tool :\

 

[Estuardo]

Ricco,
You can bet is really simple. let's try a step by step.
Line 6 we've created a connection object and it gets the connection string
Line 8 If the connection is wrong the code will break and you will need to fix the connection string.
Line 11 The query needs a parameter and is supplied by your listbox. Because we need to check the SQL String First we'll run the query without parameter, then we'll set the parameter manual instead of the one from the listbox and finally we'll use the parameter from the listbox. What kind of values do you have in your listbox? Years? like (2000, 2001,2002, 2003, etc.) if so the parameter will be one of those values
Line 12 will display the SQL String just before being executed. This is something you will have to remove later.

[COLOR="#006400"]'First Test: No parameters. Your datagrid should be filled[/COLOR]
[B]SQLString = "SELECT * FROM BlankPupilsdb"[/B]

[COLOR="#2F4F4F"]' Second Test: The manual parameter[/COLOR]
[B]SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup =[COLOR="#FF0000"] '2001'[/COLOR]"[/B] 'Here you type one of the values you have in your listbox

[COLOR="#2F4F4F"]'Finally [/COLOR]
SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup =[COLOR="#0000FF"] '" & Cstr(Me.ListYearGroup.SelectedItem) & "'"[/COLOR]

Dim YearG  As String
        Dim 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;Persist Security Info=False;"
        Dim cnn As New OleDb.OleDbConnection(ConnectString)

         cnn.Open

        ' YearG = Me.txtYearGroup.Text
        SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup = '" & Cstr(Me.ListYearGroup.SelectedItem) & "'"
         msgbox ("Sql String " & SQLString)
         
        da = New OleDbDataAdapter(SQLString, ConnectString)
        da.Fill(dt) 
        DataGrid1.DataSource = dt

Run each test at a time and Let me know the result

 

[riccolmars]

Im trying lol .. the first code im not sure brought up the database cause the database appeared due to me clicking the lil arrow thing on the top right corner in the datagrid. And chose DataSource "BlankPupilsDbBindingSource" so when i hit f5 the database appeared already .
im guessing this has something to do with it now i have written it out and i can see what im doing, but i still dnt know why it wont work,
just to double check .. the code that u amended for me, that is for the ListBox dode section ?
the second code brings up the database but when i select they "Year Group" it Errors.. this time it was a OleDbException was unhandled..Syntax error in GROUP BY clause. What does this mean ?
The Final Code, well exactly back where we started :\ .. so what does that mean ?
As you can tell it aint going to well still even with all your help ! which i really do appreciate !


SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup = '" & Me.txtYearGroup.text & "'" 'Where it says YearGroup, in the database there is a space, should i have a space in the code ? i have tried it with, and without and there has been no difference but i for i would ask ?

 

[Estuardo]

Sorry to back to you just now.
Regarding your questions:
"...the code that u amended for me, that is for the ListBox dode section ?" Yes. i had to fix the sintax in all posts, now they're ok
"the code that u amended for me, that is for the ListBox dode section ? " That's weird... unless YearGroup is not a table but a query .....
Syntax error in GROUP BY clause. What does this mean? This means that your query has something wrong. You should run your qrys inside MS Access to verify that they are flawless.

he Final Code, well exactly back where we started :\ .. so what does that mean ? This is the oldest technique: Break the code. The goal (right now) is to get data from your db into the grid. Testing the SQL String is the only way to find the problem. Once is solved you would want to keep your existent structure, right?

The good news are that we're in the right track. The bad news is that to query your db with a query the data acess is different.

 

[riccolmars]

No worries, i needed early night, meds arnt working so wellthis week so lots of pain :\.. anyway .. ive stated it again and going frew and its seems to make more sense and im looking out for small things tht make the difference such as ..

"SQLString = "SELECT * FROM BlankPupilsdb WHERE YearGroup = ..." Well as i started again i renamed a few fings to make it easier. so the db is called BlankPupilsdb, the TABLE is called PupilsDb & most importantly i think the * means select all from the table ?? well im only selecting certain items from the table. is this the major problem ? and the WHERE what is that trying to do ? like what info is it trying to get so i no what to put instead of YearGroup .. unless it actually mean what column in the table then thats fine i can just change it to YrGroup instead.

If i manage to figure fings out before you have chance to reply i shall let you know but i think ill go ahead with it for now and see what happens.

Again thanks for the help it is really appreciated!

 

[Estuardo]

G'd morning!
I think that finally we're going somewhere. If i understood your Db Name is BlankPupilsdb and your table PublishDb, right? if so. that's why your query will never run. Please before we keep going, let's do things right and please prefix your tables' name with tbl your queries with qry and the name or your db do not use it for anything else.
In all your project the only one place where you can type the name or your db is in your connection string and no where else.
As soon as you can get a SQL tutorial you'll need it.
A quick review of your sql string.

• SELECT: You will use this command to choose column names. The * means all/any.
• FROM: Here is where you explicitly describe the tables/queries that holds the the columns' names
• WHERE: This is the filter. Here you will place all rules and conditions you need to filter the result of the whole query.

So, you sql means:
I want all columns that exist in tblPublisDb but just those rows where the column YearGroup match exactly x
SELECT * FROM tblPublisDb WHERE YearGroup= x
As a rule of thumb in your queries just include the columns you really need, and the less possible rows.

Back to your project. Your datagrid is filled from a table or a query? in any case what is the object's name?

 

[riccolmars]

i think i could almost be there cause now the error says the same old OleDb bla bla but this time Data Type mismatch in Criteria expression.

I can guess what tht means, i have the wrong data type sumwhere but i cant seem to figure out where the eck im spose to check to solve it ? any ideas

 

[Estuardo]

We can't see your query and paramter, but err comes up when you send a wrong type to the query. Ex. your field is boolean and you send strings

 

[riccolmars]

Hi sorry about not contacting you back, my disability had me in hospital over night so no internet or anything, but while in i wasnt 100% sure what u ment by "We can't see your query and paramter, but err comes up when you send a wrong type to the query. Ex. your field is boolean and you send strings" how i understand it is for example im asking for them to give me the answer in NUMBERS but they are programmed to answer in LETTERS only ? that sort of thing ? of i completely misunderstood ?

What i have done is ... in the query builder when you select the items from the tick box i selected "FirstName, LastName, YrGroup, Phase, Teacher, WeekDay, Period, ID" then underneath in the bit that has the columns such as 'Alias' 'Tables' and eventually 'Filter' which i have seen on videos and photos also to be 'Criteria'.. in the Row that is YrGroup.. it has tblPupils in the Table Column, a tick in the Output column, nothing in the 'Sort type' or 'Sort Order', but then in the 'Filter' column i have tried many things that i have seen online from '= YrGroup' , '= @YrGroup' That doesnt work alltogether.. and then i tried '=7,8,9,10,11' and the i tried '= ?' ... but what happens then is ...


"Imports System.Data.OleDb
Public Class LessonForm


Private Sub LessonForm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
OleDbDataAdapter1.Fill(PupilsDS1) '<---------------- This is now the problem.. it says "Parameter ?_1 has no default value"
End Sub


Private Sub ListYrGroup_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ListYrGroup.SelectedIndexChanged


Dim 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;Persist Security Info=False;"
Dim cnn As New OleDb.OleDbConnection(ConnectString)


cnn.Open()


SQLString = "SELECT FirstName, LastName, YrGroup, Phase, Teacher, WeekDay, Period, ID " & _
"FROM tblPupils " & _
"WHERE YrGroup = '" & Me.ListYrGroup.SelectedItem & "'"


MsgBox("Sql String " & SQLString)


da = New OleDbDataAdapter(SQLString, ConnectString)
da.Fill(dt)
DataGridView1.DataSource = dt


End Sub


Private Sub TblPupilsBindingSource_CurrentChanged(sender As System.Object, e As System.EventArgs)


End Sub


End Class"

so having looked around i cant figure out how to sort this out, i presume by giving Parameter ?_1 a value but what value would that be ?

Also the yr groups are 7,8,9,10,11 .. there school years for high school like 7th, 8th 9th grade in the USA and so on, an not year groups such as 2007 2008 so on..

Sorry for the whole code but i fought that having it all you may be able to double check and be like ye that should work when ya sort the problem out .. or .. no you need he change this and change that cause it wont work.

again thank you for all this effort you putting in just to help it is so much appreciated!!