ADO Help

[RonRepp]

Hi all:

I have a procedure that worked fine while I was grouping the fields; however, I had to rewrite the SQL statement in an ungrouped status. My code is below, but I keep getting an error stating "The requested properties cannot be supported." I'm using SQL 2000 and ADO 2.5.

Can I just create a table from the SQL statement, and if so, how?

Public Sub RunTransmittals()
Dim TransCon As New ADODB.Connection
Dim TransRS As New ADODB.Recordset

Set TransCon = New ADODB.Connection
TransCon.ConnectionString = "Provider=sqloledb.1;" & _
"Data Source=D63WV941;Initial Catalog=TSMaster;User Id=sa;Password=sa; "


SQL = "SELECT TOP 100 PERCENT COMPANY, DIVISION, DATE_, TRANSMITTAL_NR AS TransNum, " & _
"AMOUNTS_1 AS Debit, AMOUNTS_2 AS Credit, " & _
"From dbo.SV_MACORD_FT_SHIP " & _
"ORDER BY COMPANY, DIVISION, DATE_, TRANSMITTAL_NR"


On Error GoTo TransERR

TransCon.ConnectionTimeout = 0

TransCon.Open TransCon.ConnectionString

Set TransRS = New ADODB.Recordset

TransRS.Open SQL, TransCon, adOpenForwardOnly, adLockBatchOptimistic

Do While Not TransRS.EOF

Comp = TransRS(0).Value
Div = TransRS(1).Value
M = Mid(TransRS(2).Value, 5, 2)
D = Right(TransRS(2).Value, 2)
Y = Left(TransRS(2).Value, 4)
strDate = M & "/" & D & "/" & Y
TransDate = CDate(strDate)
If IsNull(TransRS(3).Value) Then
Trans = "NULL"
Else
Trans = TransRS(3).Value
End If

Debit = TransRS(4).Value
Credit = TransRS(5).Value
If Debit = 0 Then
If Credit = 0 Then
TransCount = 0
End If
ElseIf Debit < 0 Then
TransCount = 0
ElseIf Credit < 0 Then
TransCount = 0
Else
TransCount = 1
End If

AddTransmittals Comp, Div, TransDate, Trans, Debit, Credit, TransCount


TransRS.MoveNext

Loop

TransRS.Close
TransCon.Close
Set TransRS = Nothing
Set TransCon = Nothing
Exit Sub

TransERR:

MsgBox "You have encountered an error!" & vbCrLf & _
"Error #: " & Err.Number & vbCrLf & _
"Description: " & Err.Description, vbCritical + vbOKOnly, "Help"
End Sub


Thanks,

Ron

 

[Sheco]

I like to work my SQL problems out using the Query Analyzer tool and only when I have the syntax correct move it back to code.

Doing it this way reduces the number of things that can go wrong and helps you zero in on the problem.

 

[RonRepp]

Sheco:

That's part of the problem; it works fine in the query analyzer. Do you have any other suggestions?

Thanks,

Ron

 

[Sheco]

Well where is exactly does the error happen?

Does this line cause it?
TransRS.Open SQL, TransCon, adOpenForwardOnly, adLockBatchOptimistic


Or is it later when you are trying to use TransRS inside the loop?

Or earlier before you even open it?

 

[RonRepp]

Sheco:

This is the line:

TransRS.Open SQL, TransCon, adOpenForwardOnly, adLockBatchOptimistic


I've tried changing the properties to Keyset...all four of them, mixed and matched. I'm flabbergasted because the same code worked when it was grouped. It's only been since I ungrouped the fields. Makes me scratch my head.

That's why I was wondering what the correct syntax would be to create a table and see if that worked.

Thanks,

Ron

 

[Sheco]

What is the point of this: TOP 100 PERCENT ???

 

[RonRepp]

Got me, but I tried removing it and I get a timeout error. I got TOP 100 PERCENT from using the VIEW in Enterprise Manager.

I'm going to try rewriting the statement and see what happens.

Thanks,

Ron

 

[Creepers]

Try
TransRS.Open SQL, TransCon, adOpenForwardOnly, adLockBatchOptimistic,-1

 

[Sheco]

Is this an ADO error or an error from the database or provider? You should be able to break it out like this:

Set TransRS = New ADODB.Recordset
[red]
TrasnRS.Source = SQL
Set TransRS.ActiveConnection = TransCon 
TransRs.CursorType = adOpenForwardOnly
TransRS.LockType = adLockBatchOptimistic
[/red]
TransRS.Open SQL

Also you can check to make sure TransCon.Errors.Count is zero.

Are you running against a table or a view? What permissions level do you have? Should the locktype be ReadOnly?

You mentioned groupings... do you mean that your old SQL had a GROUP BY clause?

 

[Sheco]

Seems like top 100 percent would be the same as getting all the records.

 

[gmmastros]

I may see the problem.... You have a comma after the last field you are returning. Try removing the comma.

SQL = "SELECT TOP 100 PERCENT COMPANY, DIVISION, DATE_, TRANSMITTAL_NR AS TransNum, " & _
    "AMOUNTS_1 AS Debit, AMOUNTS_2 AS Credit " & _
    "From dbo.SV_MACORD_FT_SHIP " & _
    "ORDER BY COMPANY, DIVISION, DATE_, TRANSMITTAL_NR"

-George

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

 

[RonRepp]

Sheco,

Yes, TOP 100 PERCENT is the same as asking for all the records, which is what I want. I received another error when I tried your TransRS.Source. It told me I needed an object, which was already set in the preceding line.

Creepers,

I've tried TransRS.Open SQL, TransCon, adOpenForwardOnly, adLockBatchOptimistic,-1 and I get an error also. As I said earlier, I'm going to try and rewrite the statement.

Thanks,

Ron

 

[Sheco]

Oh for crying out loud!

I looked right at that and didn't see it.

 

[RonRepp]

George:

Good catch, but I did finally find that error. Thanks anyway.

Ron