[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Basic SQL query statement not working when WHERE clause added

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 & "' ")
0
SweetingA
Asked:
SweetingA
2 Solutions
 
dsackerContract ERP Admin/ConsultantCommented:
Have you checked that I10 is not a lower-case "L", rather than an upper case "i" ?? Also check that your value has no spaces before or after. Rule out the obvious things, because your query looks fine.
0
 
fundacionrtsAdministrador de SistemasCommented:
Is it posible that in the field (Machine) from the database that stores the value (I10) has a blank space at the end: (I10 ).

To test this, try to trim Machine field and cboMachine.Text in the sql query.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SweetingAAuthor Commented:
windows forms
0
 
SweetingAAuthor 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?
0
 
SweetingAAuthor 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.
0
 
Daniel Van Der WerkenIndependent ConsultantCommented:
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

0
 
SweetingAAuthor Commented:
Both work, what does that mean?
0
 
SweetingAAuthor Commented:
Any how do i put that that in a vb string as i have have only proven it works in SQL
0
 
SweetingAAuthor 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?
0
 
SweetingAAuthor Commented:
Now finally it works with trim - thanks Dan7el, i will award the points now.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now