how to get values from stroed procedure

Results 1 to 2 of 2

Thread: how to get values from stroed procedure

  1. #1
    Join Date
    Dec 1969

    Default how to get values from stroed procedure

    hi all <BR>i created a stored procedure like this which retruns values am not sure whether it is correct <BR>or not.<BR>if it is wrong can you tell me where i did mistake.<BR><BR>CREATE PROCEDURE QA_Select_Candidate <BR><BR>@role sql_variant<BR><BR> AS<BR>declare @fname varchar(50) ,<BR>@lname varchar(50) ,<BR> @email varchar(50) ,<BR> @pno varchar(50) <BR><BR><BR>select @fname=firstname,@lname=lastname,@email=Email_ID,@ pno=Phone_No from QA_User_Type where <BR><BR>role=@role<BR><BR><BR>return<BR>GO<BR><BR> <BR>and am calling this stored procedure from like this.<BR><BR>cmd.CommandType = CommandType.StoredProcedure<BR> cmd.CommandText = "QA_Select_Candidate"<BR> cmd.Connection = mycon<BR><BR> Dim fname As New OleDbParameter<BR> fname.OleDbType = OleDbType.VarChar<BR> fname.Direction = ParameterDirection.Output<BR><BR> Dim lname As New OleDbParameter<BR> lname.OleDbType = OleDbType.VarChar<BR> lname.Direction = ParameterDirection.Output<BR><BR> Dim email As New OleDbParameter<BR> email.OleDbType = OleDbType.VarChar<BR> email.Direction = ParameterDirection.Output<BR><BR> Dim phono As New OleDbParameter<BR> phono.OleDbType = OleDbType.Numeric<BR> &#039;phono.Direction = ParameterDirection.Output<BR><BR> Dim role As New OleDbParameter<BR> role.OleDbType = OleDbType.Variant<BR> role.Direction = ParameterDirection.Input<BR> role.Value = "candidate"<BR><BR> cmd.Parameters.Add(role)<BR> cmd.Parameters.Add(fname)<BR> cmd.Parameters.Add(lname)<BR> cmd.Parameters.Add(email)<BR> cmd.Parameters.Add(phono)<BR><BR> da.SelectCommand = cmd<BR> da.Fill(ds, "result")<BR> dgCandidate.DataSource = ds.Tables("result")<BR> dgcandidate.databind()<BR><BR>when executing the appication am getting error<BR><BR><BR> "Parameter 1: &#039;Parameter2&#039; of type: String, the property Size has an invalid size: 0 "<BR><BR>why am getting this error.<BR><BR>how to retrive values from a stored procedure.<BR><BR>thanx in advance<BR>yoshitha

  2. #2
    Join Date
    Dec 1969

    Default You can't get those.

    [code language="T-SQL"]<BR>CREATE PROCEDURE QA_Select_Candidate <BR><BR>@role sql_variant <BR><BR>AS <BR>[hl="yellow"]declare @fname varchar(50) , <BR>@lname varchar(50) , <BR>@email varchar(50) , <BR>@pno varchar(50) [/hl]<BR><BR><BR>select @fname=firstname,@lname=lastname,@email=Email_ID,@ pno=Phone_No from QA_User_Type where <BR><BR>role=@role <BR><BR><BR>return <BR>GO <BR>[/code]<BR><BR>The variables that I highlighted are LOCAL to the stored procedure and are NOT accessible outside of the procedure. What you need are "output parameters".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts