none
Calling a Stored Procedure with one input parameter and one output parameter then filling dataset with the values of output parameter RRS feed

  • Question

  • Hi,

    I writted a SP that as follows

    create procedure SEL_TRANS_ID

    @VendorID int,@TransID int output

    AS

    BEGIN

                 select @TransID=TransactionId  from TransDetails where VendorId=@VendorID;

    END

    In frontend My requirement is to retraive TransactionId values based on given VendorID by using above SP then  i need to display that TransactionId value in dynamic Gridview at runtime.In this way one VendorId may have one or more then one TransactionId values.

    My coding as follows:

    GView.Columns.Clear();

     

     

    SqlConnection con = new SqlConnection("server=.;user id=sa;password=abc;database=example");

    con.Open();

    SqlCommand cmd = new SqlCommand("SEL_TRANS_ID",con);

    cmd.CommandType =

    CommandType.StoredProcedure;
      

     

    SqlParameter p1;

    p1 =

    new SqlParameter("@VendorID", SqlDbType.Int);

    p1.Value =

    Convert.ToInt32(TextBox1.Text);

    cmd.Parameters.Add(p1);

    p1 =

    new SqlParameter("@TransID", SqlDbType.Int);

    p1.Direction =ParameterDirection.Output;

    cmd.Parameters.Add(p1);

     

    SqlDataAdapter da = new SqlDataAdapter(cmd);

     

    DataSet ds = new DataSet();

    da.Fill(ds,"transid");

    BoundField

    BC1 = new BoundField ();

    BC1.HeaderText =

    "ID" ;

    BC1.DataField =

    Convert .ToString (cmd.Parameters [1].Value );  

    GView.Columns.Add(BC1);

    GView.DataSource = ds; 

    GView.DataBind();

    con.Close();

    I am geting a error message that "The IListSource does not contain any data sources".

     

    please advise me regarding this

    Thanks in Advance

     
    • Edited by udaysimha444 Friday, August 12, 2011 7:40 AM just for proper alignment
    Friday, August 12, 2011 7:27 AM

Answers

  • You are issuing a single sql call to a stored procedure and you expect it will return a list of transaction ids?

    For beginning, did you try invoking your stored procedure from SQL Server Management studio and check out the results?

    Why don't you simple return

    select TransactionId from TransDetails where VendorId=@VendorID;
    



    Miha Markic [MVP C#] http://blog.rthand.com
    Saturday, August 13, 2011 2:49 PM

All replies

  • You are issuing a single sql call to a stored procedure and you expect it will return a list of transaction ids?

    For beginning, did you try invoking your stored procedure from SQL Server Management studio and check out the results?

    Why don't you simple return

    select TransactionId from TransDetails where VendorId=@VendorID;
    



    Miha Markic [MVP C#] http://blog.rthand.com
    Saturday, August 13, 2011 2:49 PM
  • Hi,

    First of all thanks for u r reply, As you suggestd i executed the SP at sqlserver it`s  geting the last TransactionId value, means  suppose 1,3,5,7 are TransactionId`s, 7 alown is showing.

    Please advise me & give me SP coding and explain me how to add this SP  result to Dataset, from Dataset to Dynamic GridView.

     

    Thanks

    K Uday 

    Sunday, August 14, 2011 10:00 AM
  • When you bind to your control, you need to specify to which DataTable inside of DataSet you need to bind. In your case, if it is only one DataTable, your binding code should look like

     

    GView.DataSource = ds.Table[0];


    Val Mazur (MVP)

    http://www.xporttools.net

    Monday, August 15, 2011 10:49 AM
    Moderator