SQL Help

Is this SQL right?

  • (2 Pages)
  • +
  • 1
  • 2

29 Replies - 1471 Views - Last Post: 15 April 2009 - 07:28 PM Rate Topic: -----

#1 Anthaas  Icon User is offline

  • D.I.C Head

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

SQL Help

Posted 14 April 2009 - 01:18 PM

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 & ""
Is This A Good Question/Topic? 0
  • +

Replies To: SQL Help

#2 kasbaba  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 133
  • Joined: 03-November 08

Re: SQL Help

Posted 14 April 2009 - 01:29 PM

View PostAnthaas, 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.
Was This Post Helpful? 0
  • +
  • -

#3 Anthaas  Icon User is offline

  • D.I.C Head

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

Re: SQL Help

Posted 14 April 2009 - 01:34 PM

View Postkasbaba, on 14 Apr, 2009 - 12:29 PM, said:

View PostAnthaas, 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''.
Was This Post Helpful? 0
  • +
  • -

#4 Ian2009  Icon User is offline

  • New D.I.C Head

Reputation: 7
  • View blog
  • Posts: 31
  • Joined: 07-April 09

Re: SQL Help

Posted 14 April 2009 - 01:34 PM

Also make sure always take care of the real apostrophe characters in the text
SQL = "SELECT * FROM FilmTable WHERE " & selected & "  = '" & txtSearch.Text.Replace("'","''") & "'"


Was This Post Helpful? 0
  • +
  • -

#5 krum110487  Icon User is offline

  • D.I.C Regular

Reputation: 39
  • View blog
  • Posts: 291
  • Joined: 07-February 09

Re: SQL Help

Posted 14 April 2009 - 01:35 PM

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...
Was This Post Helpful? 0
  • +
  • -

#6 Anthaas  Icon User is offline

  • D.I.C Head

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

Re: SQL Help

Posted 14 April 2009 - 01:46 PM

View Postkrum110487, 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...
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: SQL Help

Posted 14 April 2009 - 02:15 PM

View PostAnthaas, on 14 Apr, 2009 - 12:46 PM, said:

View Postkrum110487, 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?
Was This Post Helpful? 0
  • +
  • -

#8 krum110487  Icon User is offline

  • D.I.C Regular

Reputation: 39
  • View blog
  • Posts: 291
  • Joined: 07-February 09

Re: SQL Help

Posted 14 April 2009 - 02:40 PM

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

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: SQL Help

Posted 14 April 2009 - 02:45 PM

View Postkrum110487, 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.
Was This Post Helpful? 0
  • +
  • -

#10 krum110487  Icon User is offline

  • D.I.C Regular

Reputation: 39
  • View blog
  • Posts: 291
  • Joined: 07-February 09

Re: SQL Help

Posted 14 April 2009 - 02:49 PM

I see your problem...

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



you need to fill the table at the end with "myDA"
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: SQL Help

Posted 14 April 2009 - 02:51 PM

View Postkrum110487, 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.
Was This Post Helpful? 0
  • +
  • -

#12 krum110487  Icon User is offline

  • D.I.C Regular

Reputation: 39
  • View blog
  • Posts: 291
  • Joined: 07-February 09

Re: SQL Help

Posted 14 April 2009 - 03:03 PM

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

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: SQL Help

Posted 14 April 2009 - 03:06 PM

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

Was This Post Helpful? 0
  • +
  • -

#14 krum110487  Icon User is offline

  • D.I.C Regular

Reputation: 39
  • View blog
  • Posts: 291
  • Joined: 07-February 09

Re: SQL Help

Posted 14 April 2009 - 03:13 PM

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.
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: SQL Help

Posted 14 April 2009 - 03:17 PM

View Postkrum110487, 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.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2