Get error when using Transaction.

[NinjaTurtle]

dear,

i face a problem when i use TRANSACTION method: objConn.BeginTrans & objConn.CommitTrans.
i will get prompt error when my SQL statement used "Group by"??? why? but "Order by" is work.

any simple or advance sample? and how to test whether the Trans is workable????

[aCcodeMonkey]

Ninja,
Are you just retrieving data i.e.:

SELECT myField1, myField2, Sum(myField3+1) AS myNewValue, myField4, myField4
FROM myTable
GROUP BY myField1 , myField2 , myField3 , myField4
HAVING myField4 = 'some value'

Check your syntax



<% option explicit %>
<%
Dim oConn
Dim oRs
Dim sSQL
set oConn = server.CreateObject("ADODB.CONNECTION")
set oRs = server.CreateObject("ADODB.RecordSet")

oConn.ConnectionString = application("DbWriter")
oConn.Open

sSQL = "SELECT myField1, myField2, Sum([myValue]+1) AS myNewValue, myField3, myField3 " & _
"FROM myTable " & _
"GROUP BY myField1, myField2, myField3 " & _
"where myField3 ='some value'"

oConn.BeginTrans
oRs.Open sSQL,oConn,2,3
if oConn.Errors.Count <> 0 then
oConn.RollbackTrans
Response.Write("Error Num: " & Err.Number & "<br />" & vbNewLine)
Response.Write("Error Num: " & Err.Description & "<br />" & vbNewLine)
Else
Response.Write("Done<br />")
do while not oRs.eof
Response.Write(oRs.Fields("myField1").Value & ", " & oRs.Fields("myField2").Value & ", " & oRs.Fields("myNewValue").Value & ", " & oRs.Fields("myField3").Value & "<br>" & vbNewLine)
oRs.MoveNext
loop
' This must be after the loop, otherwise the Recorset is destroyed before being parsed
oConn.CommitTrans
End if
oConn.Close
set oConn = nothing
%>


Hope this helps

[aCcodeMonkey]

Sorry Posed the Failing Code

The query in the code should be...


sSQL = "SELECT myField1, myField2, Sum([myValue]+1) AS myNewValue, myField3, myField3 " & _
"FROM myTable " & _
"GROUP BY myField1, myField2, myField3 " & _
"HAVING myField3 ='some value'"


I think I need more coffee...