<WebMethod> function that calls a param SP with optional args

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi folks

I have a SP that is as follows:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE PROCEDURE [dbo].[procGetClients]
@clientid int = 0,
@lastname varchar (50) = '1234'

AS

IF @clientid <>0

SELECT * FROM Client
WHERE ClientID = @clientid

ELSE

IF @lastname <> '1234'

SELECT * FROM Client
WHERE LastName LIKE @lastname + '%'

ELSE

SELECT * FROM Client
GO
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I have a .net WebMethod function that is supposed to return a dataset as
follows:

'''''''''''''''''''''''''''''''''''''''''''''
<WebMethod()> Public Function GetClients(ByVal intClientID As Int32,
ByVal strLastName As String) As dsClients

Dim cn As SqlConnection
Dim adclient As SqlDataAdapter
Dim clients As DataSet
Dim strConnInfo As String

cn = New SqlConnection

strConnInfo = "Database=FusionClientAndVolunteers;Data
Source=local;Integrated Security=SSPI;Packet Size=8192;Persist Security
Info='false';Workstation ID='USER-LMZWW8DZJO';"

Try

'open a connection to the DB
cn.ConnectionString = strConnInfo
cn.Open()

'create command object & parameters
Try

Dim cmd As SqlCommand
Dim prmTemp As SqlParameter

cmd = New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "procGetClients"

'add the parameter for the optional arg (@clientid) in
the SP
prmTemp = .Parameters.Add(New SqlParameter("@clientid",
SqlDbType.Int))
prmTemp.Direction = ParameterDirection.Input
prmTemp.Value = intClientID

'add the parameter for the optional arg (@lastname) in
the SP
prmTemp = .Parameters.Add(New SqlParameter("@lastname",
SqlDbType.VarChar, 50))
prmTemp.Direction = ParameterDirection.Input
prmTemp.Value = strLastName

'initialize objects
adclient = New SqlDataAdapter
clients = New DataSet

adclient.SelectCommand = cmd

'fill dataset
adclient.Fill(clients)

End With
Catch ex As Exception

End Try

Catch e As SqlException

Dim errorMessages As String
Dim i As Integer

For i = 0 To e.Errors.Count - 1
errorMessages += "Index #" & i.ToString() &
ControlChars.NewLine _
& "Message: " & e.Errors(i).Message &
ControlChars.NewLine _
& "LineNumber: " & e.Errors(i).LineNumber &
ControlChars.NewLine _
& "Source: " & e.Errors(i).Source &
ControlChars.NewLine _
& "Procedure: " & e.Errors(i).Procedure &
ControlChars.NewLine
Next i

Dim log As System.Diagnostics.EventLog = New
System.Diagnostics.EventLog
log.Source = "Fusion Client & Volunteer Service"
log.WriteEntry(errorMessages)
Console.WriteLine("An exception occurred. Please contact your
system administrator.")

Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
When I test the proc it correctly recognizes the function and when I supply
a value for the arg strLastName but leave the text box for the arg
intclientID empty, the following error msg is displayed in the browser:

Cannot convert to System.Int32.
Parameter name: type --> Input string was not in a correct format.

I tried modifying the func to make the arguments optional, but I received a
build error saying:

c:\inetpub\wwwroot\FusionClientAndVolunteerWebService\FusionClientAndVolunteerWebService\FusionClientAndVolunteerService.asmx.vb(90):
Attribute 'WebMethod' cannot be applied to a method with optional parameters.

Does anybody have any ideas?

Kind regards

Ross Petersen
 
The error is exactly what it says - you can't do optional params on the Soap
method. Pass in the parameters as XML or some other similar structure.

BTW - what is the point of creating a webservice that exposes a dataset?
(There could be reasons, but I am just offerring a free kick the tires of
your architecture service :-) )

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------

Ross said:
Hi folks

I have a SP that is as follows:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE PROCEDURE [dbo].[procGetClients]
@clientid int = 0,
@lastname varchar (50) = '1234'

AS

IF @clientid <>0

SELECT * FROM Client
WHERE ClientID = @clientid

ELSE

IF @lastname <> '1234'

SELECT * FROM Client
WHERE LastName LIKE @lastname + '%'

ELSE

SELECT * FROM Client
GO
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I have a .net WebMethod function that is supposed to return a dataset as
follows:

'''''''''''''''''''''''''''''''''''''''''''''
<WebMethod()> Public Function GetClients(ByVal intClientID As Int32,
ByVal strLastName As String) As dsClients

Dim cn As SqlConnection
Dim adclient As SqlDataAdapter
Dim clients As DataSet
Dim strConnInfo As String

cn = New SqlConnection

strConnInfo = "Database=FusionClientAndVolunteers;Data
Source=local;Integrated Security=SSPI;Packet Size=8192;Persist Security
Info='false';Workstation ID='USER-LMZWW8DZJO';"

Try

'open a connection to the DB
cn.ConnectionString = strConnInfo
cn.Open()

'create command object & parameters
Try

Dim cmd As SqlCommand
Dim prmTemp As SqlParameter

cmd = New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "procGetClients"

'add the parameter for the optional arg (@clientid) in
the SP
prmTemp = .Parameters.Add(New SqlParameter("@clientid",
SqlDbType.Int))
prmTemp.Direction = ParameterDirection.Input
prmTemp.Value = intClientID

'add the parameter for the optional arg (@lastname) in
the SP
prmTemp = .Parameters.Add(New SqlParameter("@lastname",
SqlDbType.VarChar, 50))
prmTemp.Direction = ParameterDirection.Input
prmTemp.Value = strLastName

'initialize objects
adclient = New SqlDataAdapter
clients = New DataSet

adclient.SelectCommand = cmd

'fill dataset
adclient.Fill(clients)

End With
Catch ex As Exception

End Try

Catch e As SqlException

Dim errorMessages As String
Dim i As Integer

For i = 0 To e.Errors.Count - 1
errorMessages += "Index #" & i.ToString() &
ControlChars.NewLine _
& "Message: " & e.Errors(i).Message &
ControlChars.NewLine _
& "LineNumber: " & e.Errors(i).LineNumber &
ControlChars.NewLine _
& "Source: " & e.Errors(i).Source &
ControlChars.NewLine _
& "Procedure: " & e.Errors(i).Procedure &
ControlChars.NewLine
Next i

Dim log As System.Diagnostics.EventLog = New
System.Diagnostics.EventLog
log.Source = "Fusion Client & Volunteer Service"
log.WriteEntry(errorMessages)
Console.WriteLine("An exception occurred. Please contact your
system administrator.")

Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
When I test the proc it correctly recognizes the function and when I supply
a value for the arg strLastName but leave the text box for the arg
intclientID empty, the following error msg is displayed in the browser:

Cannot convert to System.Int32.
Parameter name: type --> Input string was not in a correct format.

I tried modifying the func to make the arguments optional, but I received a
build error saying:

c:\inetpub\wwwroot\FusionClientAndVolunteerWebService\FusionClientAndVolunte
erWebService\FusionClientAndVolunteerService.asmx.vb(90):
Attribute 'WebMethod' cannot be applied to a method with optional parameters.

Does anybody have any ideas?

Kind regards

Ross Petersen
Click to expand...
 
Thanks Sahil

I was wondering about your comment - " BTW - what is the point of creating a
webservice that exposes a dataset?
(There could be reasons, but I am just offerring a free kick the tires of
your architecture service :-) )"
Click to expand...

Is this because it can cause interoperability problems with a Java app or
some other type of app?

Kind regards

Ross Petersen


Sahil Malik said:
The error is exactly what it says - you can't do optional params on the Soap
method. Pass in the parameters as XML or some other similar structure.

BTW - what is the point of creating a webservice that exposes a dataset?
(There could be reasons, but I am just offerring a free kick the tires of
your architecture service :-) )

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------

Ross said:
Hi folks

I have a SP that is as follows:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE PROCEDURE [dbo].[procGetClients]
@clientid int = 0,
@lastname varchar (50) = '1234'

AS

IF @clientid <>0

SELECT * FROM Client
WHERE ClientID = @clientid

ELSE

IF @lastname <> '1234'

SELECT * FROM Client
WHERE LastName LIKE @lastname + '%'

ELSE

SELECT * FROM Client
GO
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I have a .net WebMethod function that is supposed to return a dataset as
follows:

'''''''''''''''''''''''''''''''''''''''''''''
<WebMethod()> Public Function GetClients(ByVal intClientID As Int32,
ByVal strLastName As String) As dsClients

Dim cn As SqlConnection
Dim adclient As SqlDataAdapter
Dim clients As DataSet
Dim strConnInfo As String

cn = New SqlConnection

strConnInfo = "Database=FusionClientAndVolunteers;Data
Source=local;Integrated Security=SSPI;Packet Size=8192;Persist Security
Info='false';Workstation ID='USER-LMZWW8DZJO';"

Try

'open a connection to the DB
cn.ConnectionString = strConnInfo
cn.Open()

'create command object & parameters
Try

Dim cmd As SqlCommand
Dim prmTemp As SqlParameter

cmd = New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "procGetClients"

'add the parameter for the optional arg (@clientid) in
the SP
prmTemp = .Parameters.Add(New SqlParameter("@clientid",
SqlDbType.Int))
prmTemp.Direction = ParameterDirection.Input
prmTemp.Value = intClientID

'add the parameter for the optional arg (@lastname) in
the SP
prmTemp = .Parameters.Add(New SqlParameter("@lastname",
SqlDbType.VarChar, 50))
prmTemp.Direction = ParameterDirection.Input
prmTemp.Value = strLastName

'initialize objects
adclient = New SqlDataAdapter
clients = New DataSet

adclient.SelectCommand = cmd

'fill dataset
adclient.Fill(clients)

End With
Catch ex As Exception

End Try

Catch e As SqlException

Dim errorMessages As String
Dim i As Integer

For i = 0 To e.Errors.Count - 1
errorMessages += "Index #" & i.ToString() &
ControlChars.NewLine _
& "Message: " & e.Errors(i).Message &
ControlChars.NewLine _
& "LineNumber: " & e.Errors(i).LineNumber &
ControlChars.NewLine _
& "Source: " & e.Errors(i).Source &
ControlChars.NewLine _
& "Procedure: " & e.Errors(i).Procedure &
ControlChars.NewLine
Next i

Dim log As System.Diagnostics.EventLog = New
System.Diagnostics.EventLog
log.Source = "Fusion Client & Volunteer Service"
log.WriteEntry(errorMessages)
Console.WriteLine("An exception occurred. Please contact your
system administrator.")

Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
When I test the proc it correctly recognizes the function and when I supply
a value for the arg strLastName but leave the text box for the arg
intclientID empty, the following error msg is displayed in the browser:

Cannot convert to System.Int32.
Parameter name: type --> Input string was not in a correct format.

I tried modifying the func to make the arguments optional, but I received a
build error saying:

c:\inetpub\wwwroot\FusionClientAndVolunteerWebService\FusionClientAndVolunte
erWebService\FusionClientAndVolunteerService.asmx.vb(90):
Attribute 'WebMethod' cannot be applied to a method with optional parameters.

Does anybody have any ideas?

Kind regards

Ross Petersen
Click to expand...
Click to expand...
 
Is this because it can cause interoperability problems with a Java app or
some other type of app?
Click to expand...

Exactly. :-). And if it is an end to end .NET shop that you control, then it
may be an acceptable solution.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------


Ross said:
Thanks Sahil

I was wondering about your comment - " BTW - what is the point of creating
a
webservice that exposes a dataset?
(There could be reasons, but I am just offerring a free kick the tires of
your architecture service :-) )"
Click to expand...

Is this because it can cause interoperability problems with a Java app or
some other type of app?

Kind regards

Ross Petersen


Sahil Malik said:
The error is exactly what it says - you can't do optional params on the
Soap
method. Pass in the parameters as XML or some other similar structure.

BTW - what is the point of creating a webservice that exposes a dataset?
(There could be reasons, but I am just offerring a free kick the tires of
your architecture service :-) )

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------

Ross said:
Hi folks

I have a SP that is as follows:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE PROCEDURE [dbo].[procGetClients]
@clientid int = 0,
@lastname varchar (50) = '1234'

AS

IF @clientid <>0

SELECT * FROM Client
WHERE ClientID = @clientid

ELSE

IF @lastname <> '1234'

SELECT * FROM Client
WHERE LastName LIKE @lastname + '%'

ELSE

SELECT * FROM Client
GO
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I have a .net WebMethod function that is supposed to return a dataset
as
follows:

'''''''''''''''''''''''''''''''''''''''''''''
<WebMethod()> Public Function GetClients(ByVal intClientID As
Int32,
ByVal strLastName As String) As dsClients

Dim cn As SqlConnection
Dim adclient As SqlDataAdapter
Dim clients As DataSet
Dim strConnInfo As String

cn = New SqlConnection

strConnInfo = "Database=FusionClientAndVolunteers;Data
Source=local;Integrated Security=SSPI;Packet Size=8192;Persist Security
Info='false';Workstation ID='USER-LMZWW8DZJO';"

Try

'open a connection to the DB
cn.ConnectionString = strConnInfo
cn.Open()

'create command object & parameters
Try

Dim cmd As SqlCommand
Dim prmTemp As SqlParameter

cmd = New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "procGetClients"

'add the parameter for the optional arg (@clientid)
in
the SP
prmTemp = .Parameters.Add(New SqlParameter("@clientid",
SqlDbType.Int))
prmTemp.Direction = ParameterDirection.Input
prmTemp.Value = intClientID

'add the parameter for the optional arg (@lastname)
in
the SP
prmTemp = .Parameters.Add(New SqlParameter("@lastname",
SqlDbType.VarChar, 50))
prmTemp.Direction = ParameterDirection.Input
prmTemp.Value = strLastName

'initialize objects
adclient = New SqlDataAdapter
clients = New DataSet

adclient.SelectCommand = cmd

'fill dataset
adclient.Fill(clients)

End With
Catch ex As Exception

End Try

Catch e As SqlException

Dim errorMessages As String
Dim i As Integer

For i = 0 To e.Errors.Count - 1
errorMessages += "Index #" & i.ToString() &
ControlChars.NewLine _
& "Message: " & e.Errors(i).Message &
ControlChars.NewLine _
& "LineNumber: " &
e.Errors(i).LineNumber &
ControlChars.NewLine _
& "Source: " & e.Errors(i).Source &
ControlChars.NewLine _
& "Procedure: " & e.Errors(i).Procedure
&
ControlChars.NewLine
Next i

Dim log As System.Diagnostics.EventLog = New
System.Diagnostics.EventLog
log.Source = "Fusion Client & Volunteer Service"
log.WriteEntry(errorMessages)
Console.WriteLine("An exception occurred. Please contact
your
system administrator.")

Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
When I test the proc it correctly recognizes the function and when I supply
a value for the arg strLastName but leave the text box for the arg
intclientID empty, the following error msg is displayed in the browser:

Cannot convert to System.Int32.
Parameter name: type --> Input string was not in a correct format.

I tried modifying the func to make the arguments optional, but I
received a
build error saying:

c:\inetpub\wwwroot\FusionClientAndVolunteerWebService\FusionClientAndVolunte
erWebService\FusionClientAndVolunteerService.asmx.vb(90):
Attribute 'WebMethod' cannot be applied to a method with optional parameters.

Does anybody have any ideas?

Kind regards

Ross Petersen
Click to expand...
Click to expand...
Click to expand...
 
nod.gif
nice one !
 
Back
Top