Error when opening recordset 1

[Swi]

I get this error when trying to run the following statement.

Error:

Run-time error '-2147467259 (80004005)':
Selected collating sequence not supported by the operating system

Statement:
rs.Open "SELECT " & FieldsToCount & " FROM " & FileName & " ORDER BY " & FieldsToCount, conn, adOpenDynamic, adLockOptimistic

Connection:
With conn
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";Extended Properties=dBASE IV;User ID=Admin;Password="
End With

If I run the code initially in the IDE it gives the error but if click debug and run it again it works fine. Anyone have any insight?

Swi

 

[SmokinWrek]

I'm just taking wild guesses here, but is it possible that you're trying to 'ORDER BY' too many fields?

OR

Is it possible that you need a closing semi-colon on your SELECT statement?

Like I said....just wild guesses.

Kevin

 

[Swi]

Tried both but did not work. The thing that gets me is that it will work after I run the app a second time.

Swi

 

[CasperTFG]

Swi the fact that it runs the second time and not the first leads me to believe that there is a problem connecting, or that the connection was open and left that way...

Right click on the connection object and choose add watch. Then put a break point on the Select statement.

When you get to it you should be able to see what that connection object is set to in the watch window

Casper

"There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[CasperTFG]

Oh wait there is a problem with your SQL as well....

"SELECT " & FieldsToCount & " FROM " & FileName & " ORDER BY " & FieldsToCount

What is FieldsToCount looks to me like youa re passing in a number?

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[Swi]

FieldToCount is a string variable that is populated by the contents of a list box:

Sub GetSelectedFields()
FieldsToCount = ""
For i = 0 To List2.ListCount - 1
FieldsToCount = FieldsToCount & "[" & List2.List(i) & "], "
Next
FieldsToCount = Left$(FieldsToCount, Len(FieldsToCount) - 2)
End Sub

Swi

 

[CasperTFG]

Check DB2 docs and see if you are allowed multiple order by's. It may be that you have to trim off the last order by. But I'm still thinking connection problem if it works some times.

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[Swi]

The multiple order by's work as well after the first attempt.

Swi

 

[Swi]

The ORDER BY clause is what is making it blow up. I need the ORDER BY clause to work or it defeats the who purpose of the program. Is this not suppoted somehow?

Swi

 

[CasperTFG]

Okay this is more of an SQL questions, but why do you need that? What are you trying to do with the data?

I only ask because to me it looks like you want to count some data? If so i can give you a way to have SQL do that for you.

Also what is the data you wish sorted, some of this can probably be doen with Group By's

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[Swi]

That is exactly what I am trying to do. If you could give me an example that would be great.

Swi

 

[CasperTFG]

Hmmm.... trying to give you an example without knowing the data is kinda hard, but I will try.
If you had the following table and data as an example...

OrderNum    Product    Color      Units       
----------- ---------- ---------- ----------- 
100011      A11RG2     White      2
100012      b43R4D     White      1
100013      A11RG2     Black      1
100014      A11RG2     Black      10
100015      b43R4D     White      1
100016      A11RG2     Black      5
100017      b43R4D     White      4

If you wanted to find out How many orders you had in Black only....
SELECT COUNT(*) As OrderColorCount FROM Table1 WHERE Color = 'Black';
Returns:

OrderColorCount 
--------------- 
3

But if you wanted a sum or a count of the pieces sold in Black...
SELECT SUM([Units]) As ColorCount FROM Table1 WHERE Color = 'Black';
Returns:

ColorCount  
----------- 
16

Now you want to get more complicated. The count of all unit and color groups sold.
SELECT Product, Color, SUM([Units]) As UnitCount FROM Table1 Group By Product, Color;
Returns:

Product    Color      UnitCount   
---------- ---------- ----------- 
A11RG2     Black      16
A11RG2     White      2
b43R4D     White      6

If you need more than that the best to do would be to email me your table and what you want out of it. Though I am on vacation next week and the closest I plan to getting in contact with a computer is to play Counter-Strike.


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[Swi]

Thank you for all of your help. Also thank you for the examples. I am still getting an error when using the GROUP BY or ORDER BY statements.

Swi

 

[Swi]

Here is what I am trying to accomplish:

OrderNum Product Color Units
----------- ---------- ---------- -----------
100011 A11RG2 White 2
100012 b43R4D White 1
100013 A11RG2 Black 1
100014 A11RG2 Black 10
100015 b43R4D White 1
100016 A11RG2 Black 5
100017 b43R4D White 4

I want the user to be able to select any amount of fields and count the instances within them. For example lets say the user wants a count by Color:

COUNT COLOR
---------------------
3 Black
4 White

Or by Color within Order Number:

COUNT PRODUCT COLOR
--------------------------------------------------
3 A11RG2 Black
1 A11RG2 White
3 B43R4D White

Swi

 

[madlarry]

Check out the following link:

http://support.microsoft.com/default.aspx?scid=kb;en-us;133381

I know you're not using Access but the same principles may apply.

 

[SmokinWrek]

Swi,

I was just thinking about your problem and took a look in my copy of the MS Jet Database Engine Programmer's Guide, and think I have a possible answer to your problem. According to the book, Jet databases have a limit of 10 fields that can be used in an ORDER BY clause. Is it possible that you're querying more than 10 fields?

Kevin

 

[Swi]

Kevin,

Even when I try ordering the recordset by 1 fields it gives me the error. Have you ever used FoxPro? When you open it the program sometimes asks you what code page you want to use. When I looked the above error up on MSDN it said something about code pages. I think that is where my problem resides because I tried the same code on my home PC which has FoxPro installed and I could use the ORDER BY statements. I just find it very strange that it won't run when I initially run the program but on successive runs it works fine in the IDE. By the way, do you have an email address that I can get ahold of you at?

Swi

 

[SmokinWrek]

Swi,

You can get ahold of me with the same username as here at yahoo.com.

Kevin

 

[Swi]

May not be the most eloquent way to fix my problem but it seems to work perfectely:

On Error Resume Next
rs.Open "SELECT " & FieldsToCount & " FROM " & FileName & " ORDER BY " & FieldsToCount, conn, adOpenKeyset, adLockOptimistic, adCmdText
If Err.Number = "-2147467259" Then
Err.Clear
rs.Close
rs.Open "SELECT " & FieldsToCount & " FROM " & Path & FileName & " ORDER BY " & FieldsToCount, conn, adOpenKeyset, adLockOptimistic, adCmdText
End If

I tried tried the following code without the error handling at home on a DBF file and it worked perfectly every time. The only difference is that I have FoxPro at home and I know all of the tables I used had a code page of Windows ANSI.

Swi

 

[Swi]

I have researched this issue a bit more and it is not a problem with the code, it is a problem with VB's IDE. Here is a link if anyone is interested.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;246167

Swi