We help IT Professionals succeed at work.

Basic SQL query statement not working when WHERE clause added

271 Views
Last Modified: 2014-08-19
Very basic SQL clause will not return a value either in vb or SQL even though the table definately has the data included.  Machine is selected in a combobox and the selectedindexchange event is used.

I can run it without the where clause and it works fine and I10 is definately a machine listed.  Machine is a varchar(15), don't know if that should make any difference in the statement.

In SQL
SELECT OEE As OEE, Hour As Hour FROM qry_OEE_Hourly_Machine WHERE Machine ='I10'

In VB
SQL.RunQuery("SELECT OEE As OEE, Hour As Hour FROM qry_OEE_Hourly_Machine WHERE Machine ='" & cboMachine.Text & "' ")
Comment
Watch Question

dsackerContract ERP Admin/Consultant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Administrador de Sistemas
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
Note that the code you posted is vulnerable to sql injection.

DO NOT USE THIS, this is for example only:
assume:
cboMachine.Text = "'; delete qry_OEE_HourlyMachine"

This
SQL.RunQuery("SELECT OEE As OEE, Hour As Hour FROM qry_OEE_Hourly_Machine WHERE Machine ='" & cboMachine.Text & "' ")

would generate:
SELECT OEE As OEE, Hour As Hour FROM qry_OEE_Hourly_Machine WHERE Machine =''; delete qry_OEE_HourlyMachine

which is not something you want to do.

To get around that:
set the command text to "SELECT OEE As OEE, Hour As Hour FROM qry_OEE_Hourly_Machine WHERE Machine = @Machine"

Then add a parameter:
cmd.Parameters.Add("@Machine", cboMachine.Text)


Also is this windows forms or ASP.Net?

Author

Commented:
windows forms

Author

Commented:
As i use

If cboMachine.SelectedItem IsNot Nothing Then
                cboMachine.Text = cboMachine.SelectedItem
End If

Then i assume it shoudl not matter if i have blank spaces or not?

Author

Commented:
Hello Kyle,

When i add the parametr line i get an error message must declar scalar variable "@machine"

Trimmed code does not work - no error just returns nothing as the others.
Daniel Van Der WerkenIndependent Consultant
CERTIFIED EXPERT

Commented:
What happens if you do this?

SELECT OEE As OEE, Hour As Hour FROM qry_OEE_Hourly_Machine WHERE Machine LIKE 'I10%'

Open in new window


OR

SELECT OEE As OEE, Hour As Hour FROM qry_OEE_Hourly_Machine WHERE Machine ='I%'

Open in new window

Author

Commented:
Both work, what does that mean?

Author

Commented:
Any how do i put that that in a vb string as i have have only proven it works in SQL

Author

Commented:
I tried this

SQL.RunQuery("SELECT OEE As OEE, Hour As Hour FROM qry_OEE_Hourly_Machine WHERE Machine LIKE '%" + cboMachine.Text + "%'")

But again it returns no records even though cboMachine.text = I10

On the othe hand running a traight SQL statement

SELECT OEE As OEE, Hour As Hour FROM qry_OEE_Hourly_Machine WHERE Machine LIKE 'I10%' as Dan7el suggest returns the correct answer?

Author

Commented:
Now finally it works with trim - thanks Dan7el, i will award the points now.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.