how to pass Sql parameter

Question

This error will come in my code Procedure or function 'gridalldata' expects parameter '@order_no', which wast not supplied. I am sending parameter to procedure like below

try
{
    con.Open();

    SqlCommand cmd = new SqlCommand("gridalldata", con);
    cmd.Parameters.Add("@order_no", SqlDbType.NVarChar).Value = txt_orderno.Text;

    SqlDataReader dr = cmd.ExecuteReader();

    for (int i = 0; i < dataGridView1.Rows.Count; i++)
    {
        if (dr.HasRows)
        {
            dr.Read();

            dataGridView1.Rows[i].Cells[0].Value = dr[0].ToString();

            dataGridView1.Rows[i].Cells[2].Value = dr[2].ToString();
            dataGridView1.Rows[i].Cells[3].Value = dr[3].ToString();
            dataGridView1.Rows[i].Cells[4].Value = dr[4].ToString();
            dataGridView1.Rows[i].Cells[5].Value = dr[5].ToString();
            dataGridView1.Rows[i].Cells[6].Value = dr[6].ToString();
            dataGridView1.Rows[i].Cells[7].Value = dr[7].ToString();
            dataGridView1.Rows[i].Cells[8].Value = dr[8].ToString();
            dataGridView1.Rows[i].Cells[9].Value = dr[9].ToString();
            dataGridView1.Rows[i].Cells[10].Value = dr[13].ToString();
            dataGridView1.Rows[i].Cells[11].Value = dr[12].ToString();
        }

    }
    dr.Close();
    con.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    con.Close();
}

How To Fix This

possible duplicate of [expects parameter '@ID', which was not supplied?](http://stackoverflow.com/questions/9799524/expects-parameter-id-which-was-not-supplied) — Martin Smith, May 27 '13 at 12:44
Try this cmd.Parameters.Add("order_no", SqlDbType.NVarChar).Value = txt_orderno.Text; — K D, May 27 '13 at 12:45
u r ri8 Martin Smith m not setting commandtype correct thx guyx but data is not set on gridview columns how to solve this — naeemshah1, May 27 '13 at 12:55
you should use _cmd.Parameters.AddWithValue("@order_no", txt_orderno.Text);_ kavita verma answer seem good to me — Rémi, May 27 '13 at 12:57

score 4 · Accepted Answer · answered May 27 '13 at 12:43

4

Use cmd.CommandType = CommandType.StoredProcedure; to execute stored procedure.

answered May 27 '13 at 12:43

PiLHA

2,326
1
24
32

but how to set the data on gridview which data is coming – naeemshah1 May 27 '13 at 12:56
Well, would be interesting to run a "fill" and bring the results into a datatable, something like: DbDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; adapter.Fill (dataTable); – PiLHA May 27 '13 at 13:02
@PiLHA: yes good practise to do using dataadaptor for filling dataGrid or Gridview , i answered some like that – Satinder singh May 27 '13 at 13:09
it doest not work because i have gridview column and @order_id have 10 rows and 15 columns 10 columns for gridview and 5 for textboxes – naeemshah1 May 27 '13 at 13:13

score 3 · Answer 2 · answered May 27 '13 at 12:50

Try This:

    Try
       {
         con.Open();
            string order= txt_orderno.Text;

            SqlCommand cmd = new SqlCommand("gridalldata", con);
               cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@order_no", SqlDbType.NVarChar).Value=order;

            SqlDataReader dr = cmd.ExecuteReader();
             for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
            if (dr.HasRows)
            {
                dr.Read();

                dataGridView1.Rows[i].Cells[0].Value = dr[0].ToString();

                dataGridView1.Rows[i].Cells[2].Value = dr[2].ToString();
                dataGridView1.Rows[i].Cells[3].Value = dr[3].ToString();
                dataGridView1.Rows[i].Cells[4].Value = dr[4].ToString();
                dataGridView1.Rows[i].Cells[5].Value = dr[5].ToString();
                dataGridView1.Rows[i].Cells[6].Value = dr[6].ToString();
                dataGridView1.Rows[i].Cells[7].Value = dr[7].ToString();
                dataGridView1.Rows[i].Cells[8].Value = dr[8].ToString();
                dataGridView1.Rows[i].Cells[9].Value = dr[9].ToString();
                dataGridView1.Rows[i].Cells[10].Value = dr[13].ToString();
                dataGridView1.Rows[i].Cells[11].Value = dr[12].ToString();
                }

                }
            dr.Close();
            con.Close();


        }

Satinder singh · Answer 3 · 2013-05-27T13:03:58.350

using (SqlConnection connection = new SqlConnection(connectionString))
 {
    connection.Open();
    String orderNo=txt_orderno.Text;
    // Am assuming gridalldata is your SP
    SqlCommand cmd= new SqlCommand(gridalldata, connection);
    cmd.CommandType=CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@order_no", orderNo);
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
      // Code        
    }
 }

The using statement makes sure the connection is closed after use

Also You should bind Your Datagridview like this

Public DataTable FillDataGrid(string orderID)
{

SqlCommand cmd = new SqlCommand("gridalldata", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@order_no", orderNo);
        SqlDataAdapter dap = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        dap.Fill(ds);
        return ds.Tables[0];  

}

Datatable dt=FillDataGrid(txt_orderno.Text);
DataGridVIew1.DataSource=dt;

it doest not work because i have gridview column and @order_id have 10 rows and 15 columns 10 columns for gridview and 5 for textboxes — naeemshah1, May 27 '13 at 13:12
If its `GridView` then pls post your Gridveiw Markup its help the reader to answer easily, — Satinder singh, May 27 '13 at 13:17

how to pass Sql parameter

3 Answers3