Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: C# SQLDATAADAPTER.FILL problem

    Good day,

    I want to change my code so that instead of using the SQL statement as per below which all works I want to use a Stored Procedure:
    WORKING CODE
    string strConn = "CONNECTION STRING";
    SqlConnection cn = new SqlConnection(strConn);
    cn.Open();

    DataSet ds = new DataSet();
    string strSQL = "SQL STATEMENT";
    SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);

    ds.Tables.Add("table");
    ds.EnforceConstraints = false;
    ds.Tables["table"].BeginLoadData();
    da.Fill(ds.Tables["table"]);
    ds.Tables["table"].EndLoadData();


    Code I am trying to get working using a Stored Procedure instead but it falls over on the da.fill:
    string strConn = "CONNECTION STRING";
    SqlConnection cn = new SqlConnection(strConn);
    cn.Open();

    DataSet ds = new DataSet();
    string strSQL = "Exec STOREDPROCEDURE '" + VALUE ENTERED ON THE SCREEN TO PASS TO STORED PROCEDURE + "'";
    SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);

    ds.Tables.Add("table");
    ds.EnforceConstraints = false;
    ds.Tables["table"].BeginLoadData();
    da.Fill(ds.Tables["table"]);
    ds.Tables["table"].EndLoadData();


    Can anyone offer some advice please?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What error message(s) are you receiving?

    Your code is exposed to SQL injection attacks (obligatory: https://xkcd.com/327/)

    You need to use parameters:

    Air-code:
    Code:
    SqlDataAdapter da = new SqlDataAdapter("StoredProcedureName", cn);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;
    da.SelectCommand.Parameters.Add("@Parameter1", SqlDbType.Int).Value = 937;
    George
    Home | Blog

Posting Permissions

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