how to get values from stroed procedure

[Yoshitha]

hi all
i created a stored procedure like this which retruns values am not sure
whether it is correct
or not.
if it is wrong can you tell me where i did mistake.

CREATE PROCEDURE QA_Select_Candidate

@role sql_variant

AS
declare @fname varchar(50) ,
@lname varchar(50) ,
@email varchar(50) ,
@pno varchar(50)


select @fname=firstname,@lname=lastname,@email=Email_ID,@pno=Phone_No from
QA_User_Type where role=@role
return
GO


and am calling this stored procedure from asp.net like this.

cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "QA_Select_Candidate"
cmd.Connection = mycon

Dim fname As New OleDbParameter
fname.OleDbType = OleDbType.VarChar
fname.Direction = ParameterDirection.Output

Dim lname As New OleDbParameter
lname.OleDbType = OleDbType.VarChar
lname.Direction = ParameterDirection.Output

Dim email As New OleDbParameter
email.OleDbType = OleDbType.VarChar
email.Direction = ParameterDirection.Output

Dim phono As New OleDbParameter
phonleDbType = OleDbType.Numeric
'phono.Direction = ParameterDirection.Output

Dim role As New OleDbParameter
role.OleDbType = OleDbType.Variant
role.Direction = ParameterDirection.Input
role.Value = "candidate"

cmd.Parameters.Add(role)
cmd.Parameters.Add(fname)
cmd.Parameters.Add(lname)
cmd.Parameters.Add(email)
cmd.Parameters.Add(phono)

da.SelectCommand = cmd
da.Fill(ds, "result")
dgCandidate.DataSource = ds.Tables("result")
dgcandidate.databind()

when executing the appication am getting error


"Parameter 1: 'Parameter2' of type: String, the property Size has an
invalid size: 0 "

why am getting this error.

how to retrive values from a stored procedure.

thanx in advance
yoshitha

 

[Lars Netzel]

Doesn't seem like you are really using all the parameters and then it feels
unnessessary try this and skip the parameters that you dont have a value
for.

CREATE PROCEDURE QA_Select_Candidate
@role sql_variant
AS
SELECT firstname,lastname,Email_ID,Phone_No
FROM QA_User_Type
WHERE role=@role
GO

/Lars

 

[Hans Kesting]

hi all
i created a stored procedure like this which retruns values am not sure
whether it is correct
or not.
if it is wrong can you tell me where i did mistake.

CREATE PROCEDURE QA_Select_Candidate

@role sql_variant

AS
declare @fname varchar(50) ,
@lname varchar(50) ,
@email varchar(50) ,
@pno varchar(50)


select @fname=firstname,@lname=lastname,@email=Email_ID,@pno=Phone_No from
QA_User_Type where role=@role
return
GO

[snip]

thanx in advance
yoshitha

the "@fname" etc are variables to the procedure, NOT parameters.
If you want parameters, you should declare them as such:

CREATE PROCEDURE QA_Select_Candidate
(
@role sql_variant,
@fname varchar(50) OUT,
@lname varchar(50) OUT,
@email varchar(50) OUT,
@pno varchar(50) OUT
)
AS
select @fname=firstname,@lname=lastname,@email=Email_ID,@pno=Phone_No
from QA_User_Type where role=@role



But (as Lars said), it might be better not to use parameters at all for
output (in this case).