Declan Basile
asked on
Passing XML from VBA to SQL Server
I wrote code to pass an XML parameter in ADO from Access 2003 VBA to SQL Server 2012 but get the error message "Parameter object is improperly defined. Inconsistent or incomplete information was provided." on the line of code that appends the parameter to the parameters collection. I'm using the "MSOLEDBSQL" data access provider for the first time because I read that is supports XML. Note: I'm also using the DataTypeCompatibility=80 keyword in the connection string, which I read has to be used for XML support. I also believe I am passing well formed xml. What am I doing wrong? Here is the code and the xml ...
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
cn.ConnectionString = "Provider=MSOLEDBSQL;" _
& "Server=ALTEKDC2;" _
& "Database=ALTEK;" _
& "UID=user1;" _
& "PWD=xyz;" _
& "DataTypeCompatibility=80; "
' & "MARS Connection=True;" For Multiple Result Sets
cn.Open
'cn.Open Forms!DummyForm!tbxOLEConn ectString
With cmd
.ActiveConnection = cn
.CommandText = "procCheckConditions"
.CommandType = adCmdStoredProc
Set param1 = .CreateParameter("ProgramF unctionId" , adInteger, adParamInput, , lngProgramFunctionId)
.Parameters.Append param1
MsgBox strXML
Set param1 = .CreateParameter("XML", adLongVarChar, adParamInput, , strXML)
.Parameters.Append param1 '************************* **This is where it produces the error ********************
Set param1 = .CreateParameter("RetVal", adBoolean, adParamOutput)
.Parameters.Append param1
.Execute Options:=adExecuteNoRecord s
CheckConditions = .Parameters("RetVal")
End With
Set cn = Nothing
Set param1 = Nothing
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
cn.ConnectionString = "Provider=MSOLEDBSQL;" _
& "Server=ALTEKDC2;" _
& "Database=ALTEK;" _
& "UID=user1;" _
& "PWD=xyz;" _
& "DataTypeCompatibility=80;
' & "MARS Connection=True;" For Multiple Result Sets
cn.Open
'cn.Open Forms!DummyForm!tbxOLEConn
With cmd
.ActiveConnection = cn
.CommandText = "procCheckConditions"
.CommandType = adCmdStoredProc
Set param1 = .CreateParameter("ProgramF
.Parameters.Append param1
MsgBox strXML
Set param1 = .CreateParameter("XML", adLongVarChar, adParamInput, , strXML)
.Parameters.Append param1 '*************************
Set param1 = .CreateParameter("RetVal",
.Parameters.Append param1
.Execute Options:=adExecuteNoRecord
CheckConditions = .Parameters("RetVal")
End With
Set cn = Nothing
Set param1 = Nothing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It is normally not part of Windows. But it is imho the fastest client for SQL Server.
ASKER
Thanks very much!
ASKER