Error Occurring

I dont know why

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 1236 Views - Last Post: 14 April 2009 - 02:31 PM Rate Topic: -----

#1 Anthaas  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 26-March 09

Error Occurring

Posted 13 April 2009 - 02:34 PM

I am getting this error, and I do not know why, so some help to sort it would be very much appreciated.

"OleDbException was unhandled - No value given for one or more required parameters."

This is my code:

 Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE cboField.selected = txtsearch.text ", con)
		myDA = New OleDb.OleDbDataAdapter(search)
		Dim builder As New OleDb.OleDbCommandBuilder(myDA)
		mydataset = New DataSet()
		myDA.Fill(mydataset, "FilmTable")


The error is occuring on this line:

myDA.Fill(mydataset, "FilmTable")


All things which are undeclared in the code have been declared publicly so that isnt the problem.

mydataset is getting a value, and "FilmTable" is a table of the underlying database.

Is This A Good Question/Topic? 0
  • +

Replies To: Error Occurring

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Error Occurring

Posted 13 April 2009 - 04:45 PM

Hi, Anthaas
Wow, that error could come from all kinds of things and you might not even think they're related. As an example, here's one user's experience with this msg:

"The error message led me to look at the lone parameter for the DataAdapter's
SelectCommand, which did indeed have a value, so after much digging around,
I looked back at the query for the SelectCommand and realized that a few
columns had been moved to another table, so what the message really meant is
that I was trying to SELECT non-existent columns! Why didn't it just say so
in the first place?!

Thanks to Oliver of the Austin (TX) .NET User's Group (www.adnug.org) for
steering me in the right direction."
Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Error Occurring

Posted 13 April 2009 - 05:47 PM

Your SQL statement is wrong, change this

Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE cboField.selected = txtsearch.text ", con)



To this

Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE cboField.selected='" & txtsearch.Text & "'", con)


Was This Post Helpful? 0
  • +
  • -

#4 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Error Occurring

Posted 13 April 2009 - 06:17 PM

Of course, the obvious, concatenate the variable and don't forget those pesky delimiters!

This post has been edited by June7: 13 April 2009 - 06:17 PM

Was This Post Helpful? 0
  • +
  • -

#5 Anthaas  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 26-March 09

Re: Error Occurring

Posted 13 April 2009 - 07:34 PM

View PostPsychoCoder, on 13 Apr, 2009 - 04:47 PM, said:

Your SQL statement is wrong, change this

Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE cboField.selected = txtsearch.text ", con)



To this

Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE cboField.selected='" & txtsearch.Text & "'", con)




Ok, so I have changed some of my coding because it was getting quite a mess, trying a new method which seems to be working better, but still using the SQL statement which you gave me.

This is my code now:

con.Open()
		AEDFilm.FilmTableTableAdapter.Fill(AEDFilm.DVDDatabaseDataSet.FilmTable)
		Dim SQL As String
		SQL = "SELECT * FROM FilmTable WHERE cboField.selected='" & txtSearch.Text & "'"
		Dim com As New OleDb.OleDbCommand(SQL, con)
		Dim builder As New OleDb.OleDbCommandBuilder(myDA)
		myDA = New OleDb.OleDbDataAdapter
		mydataset = New DataSet
		myDA.Fill(mydataset, "FilmTable")
		con.Close()


and I am getting an error on this line:

myDA.Fill(mydataset, "FilmTable")


saying this:

"InvalidOperationException was unhandled - The SelectCommand property has not been initialized before calling 'Fill'."

Any ideas?
Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Error Occurring

Posted 13 April 2009 - 07:49 PM

Before calling Fill() add

myDA.SelectCommand = com


Was This Post Helpful? 0
  • +
  • -

#7 Anthaas  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 26-March 09

Re: Error Occurring

Posted 13 April 2009 - 07:52 PM

View PostPsychoCoder, on 13 Apr, 2009 - 06:49 PM, said:

Before calling Fill() add

myDA.SelectCommand = com



Thanks! Thats that sorted, but now it is giving me a new error on the same line.

This time it is saying: "OleDbException was unhandled - No value given for one or more required parameters."
Was This Post Helpful? 0
  • +
  • -

#8 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Error Occurring

Posted 13 April 2009 - 07:58 PM

I should have seen this the first time I looked at your code. In this line

Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE cboField.selected = txtsearch.text ", con)



for your WHERE code you're referencing a ComboBox on your form and the database doesnt know what to do with that. for your WHERE clause you should have the name of the column you're looking in. Something like

Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE YourColumnName ='" &  txtsearch.text & "'", con)



Of course change YourColumnName to the name of the column you're looking in. I hope that makes sense :)
Was This Post Helpful? 0
  • +
  • -

#9 Anthaas  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 26-March 09

Re: Error Occurring

Posted 13 April 2009 - 08:00 PM

View PostPsychoCoder, on 13 Apr, 2009 - 06:58 PM, said:

I should have seen this the first time I looked at your code. In this line

Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE cboField.selected = txtsearch.text ", con)



for your WHERE code you're referencing a ComboBox on your form and the database doesnt know what to do with that. for your WHERE clause you should have the name of the column you're looking in. Something like

Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE YourColumnName ='" &  txtsearch.text & "'", con)



Of course change YourColumnName to the name of the column you're looking in. I hope that makes sense :)


Thats what the combo box was supposed to be referring to.
My combo box contains: Film Title, Leading Actor 1, Leading Actor 2, Director, Genre, Running Time, Age Classification.
They are also the names of fields in the database
So the textbox contains the term to be searched, and the combo box contains the field to look in if that makes sense. Correct me if I am wrong, but I dont think that fix would work?
Was This Post Helpful? 0
  • +
  • -

#10 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Error Occurring

Posted 13 April 2009 - 08:06 PM

You want to use the value of the combo box which will be what the user has selected? Assume combo box is one column containing all the table field names you listed. Treat the combo box as a variable in the SQL:
Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE '" me.cboField & "'='" &  me.txtsearch.text & "'", con)


This post has been edited by June7: 13 April 2009 - 08:12 PM

Was This Post Helpful? 0
  • +
  • -

#11 Anthaas  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 26-March 09

Re: Error Occurring

Posted 13 April 2009 - 08:10 PM

View PostJune7, on 13 Apr, 2009 - 07:06 PM, said:

You want to use the value of the combo box which will be what the user has selected? Assume combo box is one column containing all the table field names you listed. Treat the combo box as a variable in the SQL:
Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE '" cboField & "'='" &  txtsearch.text & "'", con)



Ive changed it from a command to a string, but that SQL doesnt work, end of statement expected?
Was This Post Helpful? 0
  • +
  • -

#12 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Error Occurring

Posted 13 April 2009 - 08:27 PM

Rats, left an ampersand out of my example:
Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE '" & me.cboField & "'='" & me.txtsearch.text & "'", con)
Was This Post Helpful? 0
  • +
  • -

#13 Anthaas  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 26-March 09

Re: Error Occurring

Posted 13 April 2009 - 08:32 PM

View PostJune7, on 13 Apr, 2009 - 07:27 PM, said:

Rats, left an ampersand out of my example:
Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE '" & me.cboField & "'='" & me.txtsearch.text & "'", con)


Still doesn't work.

Says: "Operator "&" is not defined for types 'String' and 'System.Windows.Forms.ComboBox'
Was This Post Helpful? 0
  • +
  • -

#14 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Error Occurring

Posted 13 April 2009 - 08:41 PM

Not familiar with that error. Can you step debug or put code break on line following the Dim to pause code. Check that the variables have values. The only other thing I can think of is if maybe the me.cboField might need .Selected or .Value or .Text although I have never found it so. The Me. is probably not required but should not hurt. I might not be able to help on this. The SQL string looks good now.

EDIT:
Maybe not so good. Try this:
Dim search As New OleDb.OleDbCommand("SELECT * FROM FilmTable WHERE [" & me.cboField & "]='" & me.txtsearch.text & "'", con)
[] instead of apostrophes can signify a table field, it has seemed to help me before.

This post has been edited by June7: 13 April 2009 - 08:53 PM

Was This Post Helpful? 0
  • +
  • -

#15 Anthaas  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 26-March 09

Re: Error Occurring

Posted 13 April 2009 - 08:47 PM

View PostJune7, on 13 Apr, 2009 - 07:41 PM, said:

Not familiar with that error. Can you step debug or put code break on line following the Dim to pause code. Check that the variables have values. The only other thing I can think of is if maybe the me.cboField might need .Selected or .Value or .Text although I have never found it so. The Me. is probably not required but should not hurt. I might not be able to help on this. The SQL string looks good now.


I ignored the me. bit because im referring to another form to show the details but you werent to know.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2