complicated SELECT statement

[palehorse]

Hello everyone,

I have something that has been bothering me for a while now. It seems to be a complicated thing for me since I can't seem to find a resolution.

I have an access db that I am working with. It holds data on equipment for different locations.

setup is like so:

Columns:

inv_location, inv_equipment, inv_mileage, inv_status

Rows:

950102, AB 1785, 4698, clean
950102, DC 1989, 5897, dirty
950102, GH 1221, 1225, clean
950103, EL 1896, 9892, repair
950103, JH 1002, 10256, ready
950103, TM 1599, 1478, dirty

on my form, I have two text boxes to search with. One, the user enters in the location number which corrosponds with inv_location which has many duplicate values. The next text box they enter in the equipment number which corrosponds with inv_equipment which holds no duplicate values. Upon clicking on the search button, I am trying to have it look for that particular location number, then find that equipment number. Once it has done this - it then looks at the columns for the inv_mileage and inv_status fields.

An example how this will work:

txtbox1 = 950102, txtbox2 = GH 1221

on the form, there are two lables, lblMileage and lblStatus.

once the button is clicked - it finds the particular record and the lables would read like so:

lblMileage = 1225
lblStatus = clean

I even tried to hard code it in like this and it doesn't work:

select * from mydatabase where inv_location='950102' and inv_equipment='gh 1221'

Can sombody help me out with this?

 

[levyuk]

And what is the problem?

 

[kulrom]

post the code (code before and after SQL select statement) ... cheers

 

[palehorse]

The problem is that no data is getting retrieved by the SELECT statement. I will post my code as soon as I am back on that machine so you can see where I am at.

 

[jmcilhinney]

If your posted Select statement is actually what you are using, it may be that you have quoted the inv_location value. If this field is a text field and not a number then obviously it must be something else.

 

[palehorse]

This is what I am trying to do:

[size=3][color=#0000ff]Dim[/color][/size][size=3] MySQL [/size][size=3][color=#0000ff]As[/color][/size][size=3][color=#0000ff]String
 
[/color][/size][size=3][color=#0000ff]Dim[/color][/size][size=3] MyCommand [/size][size=3][color=#0000ff]As[/color][/size][size=3] OleDbCommand
 
[/size][size=3][color=#0000ff]Dim[/color][/size][size=3] MyReader [/size][size=3][color=#0000ff]As[/color][/size][size=3] OleDbDataReader
 
[/size][size=3][color=#0000ff]Dim[/color][/size][size=3] MyConnection [/size][size=3][color=#0000ff]As[/color][/size][size=3][color=#0000ff]New[/color][/size][size=3] OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;data source= C:\gtc\eqpinv.mdb")
 
[/size][size=3]MySQL = "SELECT * FROM equipmentinventory WHERE inv_location='950102' AND inv_equipment='AO 3333C'"
 
[/size][size=3]MyCommand = [/size][size=3][color=#0000ff]New[/color][/size][size=3] OleDbCommand(MySQL, MyConnection)
 
MyConnection.Open()
 
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)
 
[/size][size=3][color=#0000ff]If[/color][/size][size=3] MyReader.Read [/size][size=3][color=#0000ff]Then
 
[/color][/size][size=3]txtRotationFlag.Text = MyReader("inv_isrotation").ToString
 
txtMileageType.Text = MyReader("inv_kilo").ToString
 
[/size][size=3][color=#0000ff]End[/color][/size][size=3][color=#0000ff]If
 
[/color][/size][size=3]MyConnection.Close()

The SQL statment used above doesn't return any values for my txtboxes. The screen just Posts back and that is it.

This SQL Statement does the same thing:

MySQL = "SELECT * FROM equipmentinventory WHERE ein_entity=" & "'" & txtEntity.Text & "'" & " AND ein_equipment=" & "'" & txtEquipment.Text & "'"

In the database, the inv_locatoin and inv_equipment field is "text". Let me know if you need any more information. I am still stuck on this issue.

Just on another note, I tried to create a query in MS Access with this DB and enter in the same criteria, meaning location specified and equipment specified, then once that was found, display other fields pertaining to that particular record - the query returns no data as well - so it is as if this is an illegal SELECT statement or something.

Also - I should mention that after stepping through the webform - I used the Command Window and ran ? MyReader.Read and it returns FALSE, which is obviously why my textboxes never receive the data - but what is wrong with the SELECT statement?



Respectfully,

Scott
[/size]

 

[palehorse]

Forgive my ignorance...

The inv_equipment field in the database contains two blank characters before the begining of the quipment number so in theory, I should have been searching for " XX 1111" instead of "XX 1111". This database is so large that it would take forever to eliminate those extra spaces, so I came up with this and it works - unless somebody has a different way (I know using the LIKE is sort of slow).

[/size]
[size=3]MySQL = [/size][size=3][color=#0000ff]String[/color][/size][size=3].Format("SELECT * FROM equipmentinventory WHERE inv_location='{0}' AND inv_equipment like '%{1}'", txtEntity.Text, txtEquipment.Text)[/size]
[size=3]

 

[kulrom]

You should use Trim method ... as the Trim method removes white spaces from the beginning and end of a string.

I hope you'll know manage it ... if you don't please let me know

Cheers

 

[palehorse]

You should use Trim method ... as the Trim method removes white spaces from the beginning and end of a string.

I hope you'll know manage it ... if you don't please let me know

Cheers

That works LTRIM(inv_equipment)

 

[kulrom]

Glad to help you! Excuse me, but can you tell me why you have that string right there? With white spaces ahead? How are you putting it? Through the app?

Kind regards

 

[palehorse]

It is a MDB given to us from class. The teach wants us to create this app that deals with the db and present it for our final. I can only assume it was a test to see if we could figure out the solution

That is the only thing I can think of - is that he purposley set it to do that so my app wouldn't find the data.