YIKES - cant run stored Access query within case select statemnt!

[sarahmc22]

Please HELP! I have an Access query that won't work within case select in ASP code! I have saved this query in the Access database under qryUpdate. The error message is "Invalid SQL Statement; Expected 'DELETE','INSERT'..." I Dim and Set Connection and RecordSets outside the case select, then in the case select body, I try to execute the query through objRS.Open strQuery, objConn.

The funny thing is, in my first case, which uses a SELECT type query, the query works.

Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.ConnectionString = "DSN=myDSN"
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset&quot
Dim strQuery

select case strChoice
case "choiceA"
'qrySelect in the db is: SELECT * from Employees
strQuery = "qrySelect"
objRS.Open strSQL, objConn
' stuff to deal with RecordSet
objRS.Close

case "choiceB"
'qryUpdate in the DB is UPDATE Employees SET status='Active' where available='Y'

strQuery = "qryUpdate"
objRS.Open strSQL, objConn
objRS.Close
end select
' clean up objRS and objConn

I can do this query outside the select case and it works fine. Any ideas???????

 

[jaredn]

use an if/else/if statement instead... this might not be the best solution, but try that until someone here figures it out.. jared@aauser.com

 

[khorjak]

Try this:

objRS.Open strQuery, objConn, 1, 3, &H0004

1 - adOpenKeyset
3 - adLockOptimistic
&H0004 - adCmdStoredProc

Choo Khor
choo.khor@intelebill.com