how to get values from stroed procedure

  • Thread starter Thread starter Yoshitha
  • Start date Start date
Y

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
phono.OleDbType = 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
 
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
 
Yoshitha said:
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
Click to expand...

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).
 
Back
Top