[Anthaas]

I want to search a table called "FilmTable" for a string (txtSearch.text), which is set by the user, in a field/column which is set by cboField.

I also have declared this:

 Dim selected As String = cboField.SelectedItem 

SQL = "SELECT * FROM FilmTable WHERE " & selected & " = " & txtSearch.Text & ""

[kasbaba]

Anthaas, on 14 Apr, 2009 - 12:18 PM, said:

I want to search a table called "FilmTable" for a string (txtSearch.text), which is set by the user, in a field/column which is set by cboField

Hi,
Since you are comparing a string in the database with a string (txtSearch.text)...therefore you will need Apostrophe to tell the SQL that the comparison has to be treated as String comparison.

try this...

SQL = "SELECT * FROM FilmTable WHERE " & selected & "  = '" & txtSearch.Text & "'"

hope this helps.

[Anthaas]

kasbaba, on 14 Apr, 2009 - 12:29 PM, said:

Anthaas, on 14 Apr, 2009 - 12:18 PM, said:

I want to search a table called "FilmTable" for a string (txtSearch.text), which is set by the user, in a field/column which is set by cboField

Hi,
Since you are comparing a string in the database with a string (txtSearch.text)...therefore you will need Apostrophe to tell the SQL that the comparison has to be treated as String comparison.

try this...

SQL = "SELECT * FROM FilmTable WHERE " & selected & "  = '" & txtSearch.Text & "'"

hope this helps.

Ahh, I deleted that, still doesnt work with it though.

Keep getting this error:

I am performing a search in a database. I try to search "Peter Segal" in "Director" field from the table "FilmTable". It is saying:

OleDbException was unhandled - Syntax error (missing operator) in query expression '= 'Peter Segal''.

[Ian2009]

Also make sure always take care of the real apostrophe characters in the text

SQL = "SELECT * FROM FilmTable WHERE " & selected & "  = '" & txtSearch.Text.Replace("'","''") & "'"

[krum110487]

he had that in another post (which reminds me) DON'T DOUBLE POST if you are on the same topic.

you could have easily posted that within the same thread.

I think the problem may be with selected, it may depend on what you have selected, as I said in the other thread, print out the SQL with a msgbox(SQL) or textbox or something, and see if it is what you expected...

[Anthaas]

krum110487, on 14 Apr, 2009 - 12:35 PM, said:

he had that in another post (which reminds me) DON'T DOUBLE POST if you are on the same topic.

you could have easily posted that within the same thread.

I think the problem may be with selected, it may depend on what you have selected, as I said in the other thread, print out the SQL with a msgbox(SQL) or textbox or something, and see if it is what you expected...

Sorry, no need for capitals, I am competant in reading lower case too :-)

I tried that, and all the msgbox came up with was the SQL. "SELECT * FROM FilmTable WHERE Director = 'Peter Segal'

Im guessing thats right...

[Anthaas]

Anthaas, on 14 Apr, 2009 - 12:46 PM, said:

krum110487, on 14 Apr, 2009 - 12:35 PM, said:

he had that in another post (which reminds me) DON'T DOUBLE POST if you are on the same topic.

you could have easily posted that within the same thread.

I think the problem may be with selected, it may depend on what you have selected, as I said in the other thread, print out the SQL with a msgbox(SQL) or textbox or something, and see if it is what you expected...

Sorry, no need for capitals, I am competant in reading lower case too :-)

I tried that, and all the msgbox came up with was the SQL. "SELECT * FROM FilmTable WHERE Director = 'Peter Segal'

Im guessing thats right...

Ok, I seem to have it working, but rather than only displaying films which fit the search criteria, it is displaying all of them?

[krum110487]

sorry for the uppercase, it was there to stand out, not to insult you, some just skim posts. :-D

it shouldn't display all of them, unless within your database all of the directors are "Peter Segal"

hmmm... weird...

This post has been edited by krum110487: 14 April 2009 - 02:41 PM

[Anthaas]

krum110487, on 14 Apr, 2009 - 01:40 PM, said:

sorry for the uppercase, it was there to stand out, not to insult you, some just skim posts. :-D

it shouldn't display all of them, unless within your database all of the directors are "Peter Segal"

hmmm... weird...

Which they arent.

Here is my code, I am well and truly baffled.

con.Open()
		AEDFilm.FilmTableTableAdapter.Fill(AEDFilm.DVDDatabaseDataSet.FilmTable)
		Dim SQL As String
		Dim selected As String = cboField.SelectedItem
		If selected = "Leading Actor" Then selected = "Leading Actor 1"
		If selected = "Leading Actress" Then selected = "Leading Actor 2"
		SQL = "SELECT * FROM FilmTable WHERE " & selected & "  = '" & txtSearch.Text.Replace("'", "''") & "'"
		Dim com As New OleDb.OleDbCommand(SQL, con)
		Dim builder As New OleDb.OleDbCommandBuilder(myDA)
		myDA = New OleDb.OleDbDataAdapter
		mydataset = New DataSet
		myDA.SelectCommand = com
		myDA.Fill(mydataset, "FilmTable")
		con.Close()

The two IF statements are there because of a cock-up. I misnamed two of my fields in Access, but as you can see, it is easily fixed.

[krum110487]

I see your problem...

AEDFilm.FilmTableTableAdapter.Fill(AEDFilm.DVDDatabaseDataSet.FilmTable)

you need to fill the table at the end with "myDA"

[Anthaas]

krum110487, on 14 Apr, 2009 - 01:49 PM, said:

I see your problem...

AEDFilm.FilmTableTableAdapter.Fill(AEDFilm.DVDDatabaseDataSet.FilmTable)

you need to fill the table at the end with "myDA"

So replace "AEDFilm.DVDDatabaseDataSet.FilmTable" with "myDA"?

If so, doesnt allow it.

[krum110487]

well you need to have it after the myDA is filled, not at the beginning.

I have some sample code take a gander at this:

Try
	 con.Open()

	 Dim SQL As String
	 Dim selected As String = cboField.SelectedItem
	 If selected = "Leading Actor" Then selected = "Leading Actor 1"
	 If selected = "Leading Actress" Then selected = "Leading Actor 2"
	 SQL = "SELECT * FROM FilmTable WHERE " & selected & "  = '" & txtSearch.Text.Replace("'", "''") & "'"

	 Dim cmd As OleDbCommand = New OleDbCommand(SQL, con)

	 Dim sdr As OleDbDataReader = cmd.ExecuteReader()

	 Dim MyCommand = New System.Data.OleDb.OleDbDataAdapter(SQL, con)

	 Dim MyData As System.Data.Dataset 

	 MyCommand.Fill(MyData)

	 AEDFilm.FilmTableTableAdapter.Fill(MyData)

	 sdr.Close()
	 con.Close()

catch ex as Exception
	 msgbox (ex.Message)
end try

this is without testing and such, but it will be a good reference, I believe you main problem is the order the code is ran.

This post has been edited by krum110487: 14 April 2009 - 03:07 PM

[Anthaas]

Right, BRAND NEW Start to the same thread.

I am creating a program which interacts with a database. This database has a table called FilmTable. Within this table are the fields: FilmID, Film Title, Leading Actor, Leading Actress (last two are wrongly named, you will see my fix in a minute), Genre, Running Time, Age Classification, Buying Price, Selling Price.
The database is named "DVDDatabase".

I am trying to create a search function by where the user can search for a value within a field of the table. The value is typed into txtSearch, and the possible fields are available in cboField.

Previously, I was trying to show the results in a form used for something else, but came to the conclusion that the coding in that forms load procedure were causing a problem, so I have created a separate form for showing the results.

Here is my code:

 Me.FilmTableTableAdapter.Fill(AEDFilm.DVDDatabaseDataSet.FilmTable)
		Dim SQL As String
		Dim selected As String = CustomerMain.cboField.SelectedItem
		If selected = "Leading Actor" Then selected = "Leading Actor 1"
		If selected = "Leading Actress" Then selected = "Leading Actor 2"
		SQL = "SELECT * FROM FilmTable WHERE " & selected & "  = '" & CustomerMain.txtSearch.Text.Replace("'", "''") & "'"
		Dim com As New OleDb.OleDbCommand(SQL, con)
		Dim builder As New OleDb.OleDbCommandBuilder(myDA)
		myDA = New OleDb.OleDbDataAdapter
		mydataset = New DataSet
		myDA.SelectCommand = com
		myDA.Fill(mydataset, "FilmTable")
		con.Close()

The problem I now have is that no results are being shown in the bound text boxes.

The form being used to display search results is called "SearchResults".

The form used to add Film records is "AEDFilm"


To the reply above, would your code still apply?

This post has been edited by Anthaas: 14 April 2009 - 03:07 PM

[krum110487]

since this is a new problem it can be a new thread (just a heads up), but since that other thread you made was a problem with the SQL call, and then this one was a problem with an SQL call it should have been within the same thread. :-P

what was changed to make the bounded textboxes stop working, (ie. they were working what did you change recently that changed that?)

my code will apply, it basically lets you run any SQL command you wish on your database. So you can insert, query etc.. with minor changes.

[Anthaas]

krum110487, on 14 Apr, 2009 - 02:13 PM, said:

since this is a new problem it can be a new thread (just a heads up), but since that other thread you made was a problem with the SQL call, and then this one was a problem with an SQL call it should have been within the same thread. :-P

what was changed to make the bounded textboxes stop working, (ie. they were working what did you change recently that changed that?)

my code will apply, it basically lets you run any SQL command you wish on your database. So you can insert, query etc.. with minor changes.

The form from which the bounded text boxes WERE working on was AEDFilm, but that is the form which, when it loads, fills with ALL the records of films and allows the user to add/delete/edit records, and save. I think that load procedure is what was causing the problem, i.e. all the records to be shown when search criteria is passed, so all I did was created a new form solely for displaying search results.