Hello,
I have an ASP that takes a connection string and SQL statement in the
querystring and is supposed to return the XML representation of the
recordset to the Response stream (don't worry, this is a local page, not
one on the Internet). I had it working fine with row-returning SQL, such
as SELECT, but was trying to get it to work with non-row-returnign ones
as well (such as UPDATE and DELETE). Not only can't I get this bit to
work, I seem to have broken the row-returning bit as well!!
The full ASP is shown below. Can anyone see why this isn't working? It
is easy to test, assuming it's called xml.asp, and you have the
Northwind database installed on a local server, you can use the
following URL...
https://127.0.0.1/dap.asp?connstr=dr...(local);uid=sa
;pwd=;Network+Library=dbmssocn;Database=Northwind& SQL=select+top+2+*+from
+Categories
I had this working fine, so it showed the XML in IE. I don't know what I
did, but now it gives an error that the XML is badly formed. I presume
that this is because it is throwing an error somewhere, and the error
message is being sent to the browser. Unfortunately, I can't see what or
where the error is.
The SendErrRs is meant to create and return a recordset in case of an
error occurring. That way, the client that picks this up can see what
the error was. It was working fine with SELECT queries, but even that
has stopped working. My intention was that for UPDATEs, it would return
a simple recordset (maybe using the SendErrRs Sub) with a field set to
indicate success.
Here is the full code in the ASP. If anyone can see what is going wrong,
I would be very grateful. TIA
<%@ Language=VBScript %>
<%option explicit%>
<%
Dim SQL, ConnStr, Conn, Rs, adPersistXML, i
adPersistXML = 1
Response.ContentType = "text/xml"
ConnStr = Request.QueryString("ConnStr")
SQL = Request.QueryString("sql")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = ConnStr
On Error Resume Next
Conn.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
Set Rs = Server.CreateObject("ADODB.Recordset")
With Rs
.ActiveConnection = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Source = SQL
.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
' We know the command executed OK, but if it was a command that didn't return rows, we
' won't have a recordset. If so, trying to save the recordset will generate an error.
' Check to see if we have a recordset before saving.
i = .RecordCount
Select Case Err.Number
Case 0
' no error, therefore rows returned
.Save Response, adPersistXML
Case 3704
' no rows, ie the SQL was an update, delete, etc
SendErrorRs 0, "No rows returned"
Case Else
' genuine error
SendErrRs Err.Number, Err.Description
End Select
End If
End With
End If
Sub SendErrRs(Num, Desc)
Dim Rs
Set Rs = Server.CreateObject("ADODB.Recordset")
'response.write("<p>" & num & " - " & desc & "</p>"): exit sub
With Rs
.CursorLocation = adUseClient
.Fields.Append "ErrorNumber", adInteger
.Fields.Append "ErrorDescription", adVarChar, 400
.Open
.AddNew
.Fields("ErrorNumber") = Num
.Fields("ErrorDescription") = Desc
.Update
.Save Response, adPersistXML
End With
End Sub
%>
--
Alan Silver
(anything added below this line is nothing to do with me)
