error: Access query won't work within case select ASP code

[sarahmc22]

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???????

 

[DougP]

Not sure what you got going on with your code

but it dose not appear that you have put anything into the SQL statement in your select cases.
See
objRS.Open strSQL, objConn

strSQL is not being set to anything

try this:

Select Case strChoice
Case "choiceA"
strSQL = "SELECT * from Employees"
Case "choiceB"
strSQL = "UPDATE Employees SET status='Active' where available='Y'"
End Select

objRS.Open strSQL, objConn
objRS.Close



DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.