1

To make it parameterized I have used command object, so as to protect from Injection, after making it parameterized it's not able to connect to DB. I am not sure where it went wrong.

Error :Operation is not allowed when the object is closed.

Can somebody help me please in fixing the issue ?

Set dbConn = Server.CreateObject("ADODB.Connection")
set RS = server.CreateObject("ADODB.recordset")
dbConn.Open Application("SOP_DB_ConnectionString"), _
Application("SOP_DB_RuntimeUserName"), _
Application("SOP_DB_RuntimePassword")

Set cmdSQL= server.CreateObject("ADODB.command")

sqlStmt = "select * from Parameters where Parameter like ?"

cmdSQL.Activeconnection = dbConn
cmdSQL.CommandText = sqlStmt
cmdSQL.CommandType = adCmdText
cmdSQL.Prepared = True ' only needed if u plan to reuse this command often
cmdSQL.Parameters.Refresh 

strSearch = "%conn%"
Set prmSQL = cmdSQL.CreateParameter("strVal" , adVarChar, adParamInput, 255,strSearch)
cmdSQL.Parameters.Append prmSQL

RS.CursorType = 3' adOpenStatic 

RS.Open cmdSQL,dbConn

if RS.BOF and RS.EOF then ' if no connection found -->GETTING ERROR HERE
    session("ErrorTitle")="the initializing process"    
    session("ErrorText")="Connection failed- Couldn't read SOP parameters from database"
    Response.Redirect "Error.asp"
else ' found record 
    RS.MoveFirst
    Do while not RS.EOF
        session(trim(RS.Fields("Parameter").value)) =       Trim(RS.Fields("Val").value)
        RS.MoveNext
    Loop    
end if 
Rs.close
Paul
  • 4,160
  • 3
  • 30
  • 56
Praveen_07
  • 170
  • 2
  • 12
  • And what is the DB? SQL Server, MySQL, Oracle or heaven forbid MS Access?? The `LIKE` pattern matching wildcard character can be different for example MS Access uses `*` instead of `%`. – user692942 Apr 28 '15 at 07:44
  • Thanks Lankymart for your reply. DB is SQL Server – Praveen_07 Apr 28 '15 at 08:32
  • Have you tried running the query `select * from Parameters where Parameter like '%conn%'` in SSMS does it return anything? If not there is the issue, if no records are found that match the query `ADODB.Recordset` will return in a closed state, you can check for this using `If RS.State = adStateClosed Then`. – user692942 Apr 28 '15 at 12:03
  • 2
    Two things try : 1. Escape the object names using `[]` , `Parameter` is in the list of "Future Keywords" in this list : https://technet.microsoft.com/en-us/library/aa238507%28v=sql.80%29.aspx. So your command text becomes: `select * from [Parameters] where [Parameter] like ?` . 2. Change `strVal` to `@strVal` . That is the usual SQL Server syntax. You could also check this post: http://stackoverflow.com/questions/770419/how-to-make-a-parametrized-sql-query-on-classic-asp – Flakes Apr 28 '15 at 14:31
  • @Praveen_07 what worked exactly? Please ask the user who gave correct reply to post new answer by using `@` to like I did here. :) – Shadow The GPT Wizard Apr 29 '15 at 06:51
  • @SearchAndResQ suggested links helped me to resolve the issue. Thanks SearchAndResQ :) – Praveen_07 Apr 29 '15 at 07:01
  • @Praveen_07 which link? the reserved keyword one or the one to the stackexchange post ? You are free to add an answer yourself describing what changes you made; because we don't know what changes you made. – Flakes Apr 29 '15 at 12:03

0 Answers0