Orer by problem 2

[munchen]

I have the following line of code that works perfectly. However I want to have an order by C in there.

rs.Open "SELECT A, B, C FROM tblTest WHERE C = '" & Me.DataCombo1.Text & "'", MySQLConnection, adOpenStatic

Where would I place the Order By C code and does it have to be surrounded by & and '"?

 

[zemp]

Try this,

rs.Open "SELECT A, B, C FROM tblTest WHERE C = '" & Me.DataCombo1.Text & "' ORDER BY C", MySQLConnection, adOpenStatic

zemp

 

[munchen]

zemp

It didn't work.

I get the following Compile error:

Expected end of statement

It highlights the word ORDER

 

[BobRodes]

zemp's code should work fine. Would you please post the line that doesn't work? As in cut and paste the exact code, if you would.

Thanks,

Bob

 

[petermeachem]

The ORDER by C is a bit superfluous as all records returned will have the same C value.

 

[BobRodes]

Er...you do have a point.

 

[gmmastros]

I'm guessing that the A,B, & C stuff is just an example, and the filter on C along with the order on C were unintentional.

When building a SQL query, the general format is...

Select <Fields>
From <Table(s)>
Where <Conditions>
Order By <Order By Fields>

The reason you got your Order By error is because it should have been included on the same line as the rest of the sql query. Because of the way it was posted, it appears as a second line.

You could do something like...

Dim sSQL as String

sSQL = "SELECT A, B, C "
sSQL = sSQL & "FROM tblTest "
sSQL = sSQL & "WHERE C = '" & Me.DataCombo1.Text & "' "
sSQL = sSQL & "ORDER BY C "

rs.Open sSQL, MySQLConnection, adOpenStatic

Notice that each line (where we are building the sql string) has a space as the last character of the string. This is important, so make sure you leave it in.

As soon as you get this working, do a google search on "SQL Injection Attack" to learn why this method leaves your system vulnerable.

Good Luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chiph]

gmmastros gets a star for having nicely formatted SQL and mentioning injection attacks.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[gmmastros]

Gee thanks. I've gotten stars for less, but each one is appreciated. I only mentioned "SQL Injection Attack" because I knew you would if I didn't.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chiph]

Easy to read SQL is nice because it makes maintenance significantly easier. When you're trying to find a missing single-quote in a built-up string, breaking it out like that into separate lines really helps.

Doesn't take up any additional memory, *does* take a little longer to write the first time, but that time is regained when you're troubleshooting a problem.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[guitarzan]

I've taken to putting my SQL in a separate text file, such as this:

<SQLREPORT1>
SELECT 
 Table1.field1, 
 Table1.field2, 
 Table1.field3, 
 Table1.field4, 
 etc

 FROM Table1 RIGHT JOIN (Table2 RIGHT JOIN (Table3 etc etc etc!))) 
</SQLREPORT1>

Then I wrote a function that takes the XML tag and returns the text in between.

Besides being more readable, it has the added bonus (if you use Access) of being able to copy and paste it directly into Access's SQL view, then switch to design mode to make changes, which so far is the only way I am able to create queries with complex Joins (I tried to write Joins on my own and very nearly went insane)

 

[chiph]

Some guys at a previous job wrote something like that -- they had some tricky work to handle SQL IN clauses, but mostly it was very straight-forward.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first