CODEWITHSUNDEEP
stored-proceduresvbscriptasp-classic
NarfFlarf
NarfFlarf

Reputation: 63

ASP Classic - Displaying results from MSSQL2000 stored procedure?

I am not very well versed in ASP, but that is what my client's site is written in so I am taking the easy way out.

Basically, I am trying to display results from a stored procedure based on a value I give. There should and ARE multiple records being returned from the SP so I am not sure if I need to loop through them, or what.

So far, I have 

set rst2 = server.createobject("ADODB.Recordset")
rst2.open "exec zSProc_Name of SP " & resID,conL  

while not rst2.eof
            Response.Write(rst2.Fields("fieldname1").value & ", ")
            Response.Write(rst2.Fields("fieldname2").value & "<br />")
            Response.Write(rst2.Fields("fieldname3").value & "<br />")
            Response.Write(rst2.Fields("fieldname4").value & "<br />")
        rst2.movenext
        wend

set rst2 = nothing 'Clear up memory used
rst2.close

can anyone clear up what I may be doing wrong here?

Upvotes: 1

Views: 497

Answers (1)

AnthonyWJones
AnthonyWJones

Reputation: 189457

You are assigning Nothing to rst2 before calling Close so that will result in a "..object not set" error. Swap the lines around to fix that.

Potentially you are getting resID from data sent to the page by the client. If so your use of string concatenation to create a SQL batch is exposing the site to a SQL injection attack.

Instead you should use a ADODB.Command object to execute an SP instead of constructing SQL. For an example consult KB164485.

You are writing field values directly which most of the time will be ok but if any text contains characters such as < or & it won't render properly. Wrap values in Server.HTMLEncode methods.

Upvotes: 4

Related Questions