CODEWITHSUNDEEP
c#sqlstored-proceduresparameterssqldataadapter
litpost
litpost

Reputation: 105

SqlDataAdapter filling with DataTable does not work

I have this code running in form_load event:

        using (SqlConnection sqlConn = new SqlConnection(strConn))
        {
            sqlConn.Open();
            SqlDataAdapter sqlDa = new SqlDataAdapter("pp_sp_MachineAndOp", sqlConn);
            DataTable sqlDt = Helper.ExecuteDataTable("pp_sp_MachineAndOp", new SqlParameter("@MachineAndOpID", 7));
            sqlDa.Fill(sqlDt);
            dgvMachineAndOp.AutoGenerateColumns = false;
            dgvMachineAndOp.DataSource = sqlDt;

            sqlDa.Dispose();
            sqlConn.Close();

        }

I get error 'Procedure or function 'pp_sp_MachineAndOp' expects parameter '@MachineAndOpID', which was not supplied.' at line:

                sqlDa.Fill(sqlDt);

Important to say that if I open DataTable Visualizer of sqlDt at runtime I see expected results!

Here is a code behind Helper.ExecuteDataTable:

        public static DataTable ExecuteDataTable(string storedProcedureName, params SqlParameter[] arrParam)
    {
        DataTable dt = new DataTable();

        // Open the connection 
        using (SqlConnection sqlConn = new SqlConnection(strConn))
        {
            try
            {
                sqlConn.Open();
                // Define the command 
                using (SqlCommand sqlCmd = new SqlCommand())
                {
                    sqlCmd.Connection = sqlConn;
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText = storedProcedureName;

                    // Handle the parameters 
                    if (arrParam != null)
                    {
                        foreach (SqlParameter param in arrParam)
                        {
                            sqlCmd.Parameters.Add(param);
                        }
                    }

                    // Define the data adapter and fill the dataset 
                    using (SqlDataAdapter da = new SqlDataAdapter(sqlCmd))
                    {
                        da.Fill(dt);
                    }
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }
        return dt;
    }

What I am missing?

Upvotes: 0

Views: 448

Answers (3)

imomins
imomins

Reputation: 24

you can use below function(modification required as per your need):

 public IDataReader ExecuteReader(string spName, object[] parameterValues)
    {
        command = GetCommand();
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = spName;
        if (parameterValues != null)
        {
            for (int i = 0; i < parameterValues.Length; i++)
            {
                command.Parameters.Add(parameterValues[i]);
            }
        }
        reader = command.ExecuteReader();
        if (parameterValues != null)
            command.Parameters.Clear();
        return reader;
    }

Upvotes: 0

Amit Verma
Amit Verma

Reputation: 2490

Remove everything except

 DataTable sqlDt = Helper.ExecuteDataTable("pp_sp_MachineAndOp", new SqlParameter("@MachineAndOpID", 7));
dgvMachineAndOp.AutoGenerateColumns = false;
dgvMachineAndOp.DataSource = sqlDt;

your Helper.ExecuteDataTable is doing everything. you don't need to replicate same this in your code.

Upvotes: 0

Amit Verma
Amit Verma

Reputation: 2490

I think your helper class is creating connection with database as your data table has data.

So, try to remove stored proc name and connection object from adaptor and then check.

SqlDataAdapter sqlDa = new SqlDataAdapter();//use this only.

Upvotes: 0

Related Questions