Re: msdatashape provider error


--
wq352


"Bob Barrows [MVP]" wrote:

wq352 wrote:
I usually used the MSDASQL.1 provider to access SQL Server

Well, then, you've been using a deprecated provider.
http://www.aspfaq.com/show.asp?id=2126

but now I
need to use the MSDatashape as the provider and used MSDASQL.as the
data provider. And here lies the problem, my codes which uses ADO
command refresh to get the parameters from store procedure does not
work in my vb code and return error message:

1: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'stored
procedure name' expects parameter 'parametername', which was not
supplied.

2: Data provider or other service returned an E_FAIL status.


Does anyone know why MSDatashape cannot seems to be able to detect
any of the in/out parameters of a stored procedures?

First, try changing to the SQLOLEDB provider. If that does not help, show us
some code to repro the symptom.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Thank you very much,
When I only use 'Provider=MSDASQL.1' or 'Provider=SQLOLEDB.1' ,It's ok.
If I use the MSDatashape as the provider and used original provider as the
data provider,It returned error.

sample code:
Test Function:

Private m_objConn As ADODB.Connection

Private Sub cmdExec_Click()
Dim cmdProcedure As ADODB.Command
Dim lngEffectedRows As Long

Set cmdProcedure = New ADODB.Command

Set cmdProcedure = ExcuteProcedure("dbo.usp_ProviderTest", 2,
lngEffectedRows)

End Sub


Public Function ConnectDB(objConnection As ADODB.Connection) As Boolean
Dim strConn As String
Dim strErrDesc As String

On Local Error GoTo errHandle:

If objConnection Is Nothing Then
Set objConnection = New ADODB.Connection
End If
If objConnection.State = adStateClosed Then
objConnection.CursorLocation = adUseClient
objConnection.ConnectionTimeout = 30
objConnection.CommandTimeout = 300
End If

strConn = "Provider=MSDataShape;Data Provider=MSDASQL.1;Persist Security
Info=False;"
'strConn = "Provider=MSDASQL.1;Persist Security Info=False;"
' strConn = "Provider=MSDataShape;Data Provider=SQLOLEDB.1;Persist
Security Info=False;"
' strConn = "Provider=MSDataShape;driver={SQL Server};Persist Security
Info=False;"

strConn = strConn & "Data Source=[strDSN];"
strConn = strConn & "User ID=[strUserName];"
strConn = strConn & "password=[strPassword];"
'''' strConn = strConn & "Server=HI1-BOCIM;"
'''' strConn = strConn & "database=BQZ_PICS;"
'''' strConn = strConn & "UID=pics;"
'''' strConn = strConn & "pwd=snp1479;"

objConnection.Open strConn

ConnectDB = True
Exit Function
errHandle:

strErrDesc = "[(" & 1051 + vbObjectError & ")(" & App.Title &
")(ConnectDB)]" & Err.Description
Err.Raise 1000 + vbObjectError, , strErrDesc

End Function

Public Function ExcuteProcedure(ByVal strProcedureName As String, ParamArray
ParamInputList()) As ADODB.Command

Dim strErrDesc As String
Dim cmdProcedure As ADODB.Command
Dim lngProceParamCount As Long
Dim lngParamInputCount As Long
Dim intCount As Integer
Dim varParamInputList As Variant
Dim intParamCount As Integer
Const ParameterSize = 65536

On Error GoTo Error_Handler

'Init Parameter list
If UBound(ParamInputList) >= 0 Then
varParamInputList = ParamInputList
End If

'refresh connction
If m_objConn Is Nothing Then
Set m_objConn = New ADODB.Connection
End If

OpenConn:

If m_objConn.State = adStateClosed Then
Call ConnectDB(m_objConn)
End If

'Init ADO.command
Set cmdProcedure = New ADODB.Command
Set cmdProcedure.ActiveConnection = m_objConn

cmdProcedure.CommandText = Trim$(strProcedureName)
cmdProcedure.CommandType = adCmdStoredProc
cmdProcedure.Parameters.Refresh

With cmdProcedure

'Check Parameter
lngProceParamCount = .Parameters.Count

'Set parameter value
intParamCount = 0

For intCount = 0 To lngProceParamCount - 1
If .Parameters(intCount).Type = adChar Or
..Parameters(intCount).Type = adVarChar Then
.Parameters(intCount).Type = adBSTR
End If

If .Parameters(intCount).Direction = adParamInput Or
..Parameters(intCount).Direction = adParamInputOutput Then

If IsNull(varParamInputList(intParamCount)) Or
IsEmpty(varParamInputList(intParamCount)) Then

Else
.Parameters(intCount).Size = .Parameters(intCount).Size
.Parameters(intCount).Value =
Trim(varParamInputList(intParamCount))
End If
intParamCount = intParamCount + 1
End If

Next intCount
.Execute

End With
Set ExcuteProcedure = cmdProcedure

Exit Function

Error_Handler:

strErrDesc = "[(" & 1051 + vbObjectError & ")(" & App.Title &
")(ExcuteProcedure)]" & Err.Description
Set cmdProcedure = Nothing
Err.Raise 1000 + vbObjectError, , strErrDesc
End Function

Test Storde Procedure:

CREATE PROCEDURE dbo.usp_ProviderTest
(
@pintIn INT,
@pintOut INT=0 OUTPUT
)
AS
BEGIN
set @pintOut=20
RETURN 0
End

.

Relevant Pages

  • Re: Need WMI script
    ... WinNT provider reveals the "primary" group, ... I also assume that strGroupName ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes, ... However, if you use the cscript host, ...
    (microsoft.public.windows.server.scripting)
  • SDK 3.00 .net provider and MS visual studio
    ... Database is Informix Online 9.40 on a Sun Solaris machine, connecting from a windows XP client machine with SDK 3.00.TC3 installed (.net provider). ... using btree; ... Dim IfxDataAdaptorBankTrans As New ...
    (comp.databases.informix)
  • Re: oleDB hangs on ExecuteNonQuery
    ... The OLE DB provider is set to IBM DB2 UDB for iSeries IBMDA400 OLE DB ... Dim success As Boolean = False ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Properties in Access mit ADO auslesen
    ... > Das Objekt oder der Provider kann den angeforderten Vorgang nicht ... > Dim cnn As New ADODB.Connection ... Public Sub SetPropertyADP(ByVal sPropName As String, ...
    (microsoft.public.de.access)
  • ANN: UniDirect .NET Data Provider First Release
    ... Core Lab company has recently announced first release of UniDirect .NET, ... data provider to universal data access to different databases for the ... It supports most of major database servers ... The provider is completely based on ADO.NET ...
    (borland.public.delphi.thirdpartytools.general)