Problem with SQLParameter data type (because 'System.Data.SqlClient.SqlParameter' is not derived fro

[Stacey Levine]

I have a webservice that has the below procedure. Basically a procedure to
called a stored procedure and return the results. When I try to call the
webservice from my program I get the error. Both my calling code and the
webservice code are below. Thanks for your help.


D:\Projects .NET\StoreBO\frmVoids.vb(182): Value of type '1-dimensional
array of System.Data.SqlClient.SqlParameter' cannot be converted to
'1-dimensional array of StoreBO.StoreBackOffice.SqlParameter' because
'System.Data.SqlClient.SqlParameter' is not derived from
'StoreBO.StoreBackOffice.SqlParameter'.


--------------------------- CODE CALLING WEBSERVICE HERE

Private Sub GetData(Optional ByVal MyFilter As Integer = 0)

Me.Cursor = Cursors.WaitCursor


Dim MyParams() As SqlParameter


ReDim Preserve MyParams(5)

MyParams(0) = New SqlParameter("@StartDate",

Me.dteStart.Value.ToShortDateString)

MyParams(1) = New SqlParameter("@EndDate",

Me.dteStart.Value.ToShortDateString)

MyParams(2) = New SqlParameter("@Store", MyFilter)

MyParams(3) = New SqlParameter("@UnVerifiedOnly", 0)

MyParams(4) = New SqlParameter("@PostVoidsOnly", 0)

MyParams(5) = New SqlParameter("@PostVoidTimeDifference", 0)



Dim RsTemp As DataSet

RsTemp = New DataSet

Me.grdVoids.DataSource = Nothing


Dim rsData As New DataSet

Dim MySvc As New StoreBackOffice.StoreBackOffice

Dim ErrorMsg As String = "Error getting Post Void Information"

Try

If MySvc.GetDataFromStoredProcedure(MerchConn,

"vm_VoidsAudit", MyParams, MerchConn, ErrorMsg, RsTemp, "Voids", 120) = True
Then

rsData.Clear()

rsData.Merge(RsTemp)

Me.grdVoids.DataSource = rsData.Tables("Voids")

Else

MessageBox.Show(ErrorMsg, "Error getting Void Data", MessageBoxButtons.OK,
MessageBoxIcon.Error)

End If

Catch ex As System.Exception

Dim sMsg As String = "There has been an error Post Voids Information : " &
ex.ToString

MessageBox.Show(sMsg, "Error loading data", MessageBoxButtons.OK,
MessageBoxIcon.Error)

Finally


End Try


RsTemp.Dispose()

Me.Cursor = Cursors.Default


End Sub




--------------------------WEB SERVICE METHOD BELOW


<WebMethod()> _

Public Function GetDataFromStoredProcedure(ByVal dbConn As String, ByVal
MySproc As String, ByVal MyParams() As SqlParameter, ByVal MyConn As String,
ByRef ErrorDesc As String, ByRef ReturnDS As DataSet, ByVal DataName As
String, ByVal MyTimeout As Integer) As Boolean

Dim oConn As New SqlConnection(dbConn)

Dim bRetVal As Boolean

Try

Dim MyCmd As New SqlCommand(MySproc, oConn)

MyCmd.CommandTimeout = MyTimeout

MyCmd.CommandType = CommandType.StoredProcedure

Dim p As SqlParameter

If IsNothing(MyParams) = False Then

For Each p In MyParams

p = MyCmd.Parameters.Add(p)

p.Direction = ParameterDirection.Input

Next

End If

Dim oAdapt As New SqlDataAdapter(MyCmd)

MyCmd.Prepare()

oConn.Open()

oAdapt.Fill(ReturnDS, DataName)

'oConn.Dispose()

MyCmd.Dispose()

oConn.Dispose()

bRetVal = True

Catch ex As System.Exception

Dim sMsg As String = ErrorDesc & "(" & MySproc & ") : " & ex.Message

ErrorDesc = sMsg

bRetVal = False

Finally


End Try

Return bRetVal

End Function

 

[Cor Ligthert]

Stacey,

To overcome these problems the best thing you can do is set in the top of
your programs

Option Strict on
Than this kind of problems will be showed (mostly) in your code.

Cor

 

[Stacey Levine]

I do have option Strict On. The problem would more than likely go away if I
did not have this on. The problem is for some reason the SQLParameter type
is getting changed when I reference a webservice that takes that as a
parameter. Within the webservice it is fine.. it is just a problem in the
referencing program.

 

[Peter Huang [MSFT]]

Hi

From the error, it seems to be a casting error.
This is because the WebService wanted a himself defined SqlParameter class
as below.
This is included in the Reference.vb file in the web service generated
client.

Because WebService can be consumed by many client, e.g. java, so the .NET
defined SqlParameter class is not specifed in java, the Webservice then
define a described SqlParameter.


<System.Xml.Serialization.XmlTypeAttribute([Namespace]:="http://tempuri.org/
TestWS/Service1")> _
Public Class SqlParameter
Inherits MarshalByRefObject

'<remarks/>
Public DbType As DbType

'<remarks/>
<System.ComponentModel.DefaultValueAttribute(SqlDbType.NVarChar)> _
Public SqlDbType As SqlDbType = SqlDbType.NVarChar

'<remarks/>

<System.ComponentModel.DefaultValueAttribute(ParameterDirection.Input)> _
Public Direction As ParameterDirection = ParameterDirection.Input

'<remarks/>
<System.ComponentModel.DefaultValueAttribute(false)> _
Public IsNullable As Boolean = false

'<remarks/>
<System.ComponentModel.DefaultValueAttribute(0)> _
Public Offset As Integer = 0

'<remarks/>
<System.ComponentModel.DefaultValueAttribute("")> _
Public ParameterName As String = ""

'<remarks/>
<System.ComponentModel.DefaultValueAttribute(GetType(System.Byte),
"0")> _
Public Precision As Byte = CType(0,Byte)

'<remarks/>
<System.ComponentModel.DefaultValueAttribute(GetType(System.Byte),
"0")> _
Public Scale As Byte = CType(0,Byte)

'<remarks/>
<System.ComponentModel.DefaultValueAttribute(0)> _
Public Size As Integer = 0

'<remarks/>
<System.ComponentModel.DefaultValueAttribute("")> _
Public SourceColumn As String = ""

'<remarks/>

<System.ComponentModel.DefaultValueAttribute(DataRowVersion.Current)> _
Public SourceVersion As DataRowVersion = DataRowVersion.Current

'<remarks/>
Public Value As Object
End Class

I think you may try to use the full qualified name as below to use the
Webservice SqlParameter in the winform client.
localhost.SqlParameter 'Webservice name is localhost.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.