Hi! I have a DataBase and a query (SQL on C#). My table is about airways. So program should find one or some flights with searching them by FLIGHT NUMBER. I have DataGridView with this table. So what should I do with query and DB to get a table (DataGridView) that has only information about flights with this number?
So this is my query:

ALTER PROCEDURE dbo.StoredProcedure2
	
	(
	@number int ,
	@PrID text OUTPUT
	)
	
AS
	 SET NOCOUNT ON 
	 SELECT @PrID = Pr
	 FROM TimeTable
	 WHERE ([Flight num] = @number)
	RETURN

And this thing doesn`t work:

SqlCommand cmd = new SqlCommand();
 
            SqlConnection con = new SqlConnection();
            con.ConnectionString = connectionString;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "[dbo.StoredProcedure1]";
            cmd.Parameters.Clear();
            SqlParameter p1 = cmd.Parameters.Add("@number", SqlDbType.Int);
 
            SqlParameter p2 = cmd.Parameters.Add("@PrID", SqlDbType.Text);
            p1.Direction = ParameterDirection.Input;
            p1.Value = go.Text;
      
            p2.Direction = ParameterDirection.Output;
            //textBox1.Text = p2.Value.ToString(); here should be a table with chosen flights
            con.Open();
            try
            {
                cmd.ExecuteNonQuery();
                MessageBox.Show("success!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Source + ex.Message + ex.HelpLink);
            }
            con.Close();

What should i change? :)

You named the query "dbo.StoredProcedure2" but in your code you execute "dbo.StoredProcedure1".

You are executing it as a non-query when you want results.

You don't need an output value and it won't work anyway as you might get multiple rows returned. Your query should be:

SELECT Pr FROM TimeTable WHERE [Flight num] = @number

and you should be calling ExecuteQuery.

First of all you need to change output parameter type in C# code and mention the length (size).

SqlParameter p2 = cmd.Parameters.Add("@PrID", SqlDbType.Varchar,100);

and execute command to read output parameter value.

con.Open();
cmd.ExecuteNonQuery();
con.Close();
textBox1.Text = p2.Value.ToString();

EDIT:
Change CommandText property (see @Momerath post)

cmd.CommandText = "[dbo.StoredProcedure2]";

Edited 5 Years Ago by __avd: n/a

Thank you. I transformed something but it doesnt work too. c# tells my that its somthing wrong with ExecuteReader().

SqlCommand cmd = new SqlCommand();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = connectionString;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "[dbo.StoredProcedure2]";
            cmd.Parameters.Clear();
            SqlParameter p1 = cmd.Parameters.Add("@number", SqlDbType.Int);

            SqlParameter p2 = cmd.Parameters.Add("@PrID",SqlDbType.NVarChar,50);
            p1.Direction = ParameterDirection.Input;
            p1.Value = go.Text;
            con.Open();
            cmd.ExecuteReader();
            con.Close();
           // p2.Direction = ParameterDirection.Output;
            textBox1.Text = p2.Value.ToString();

And may be you know how to put my result (chosen flights with information about them) in dataGridView?

This one works but it has no output. I have a mistake in the last three lines.

int q;
            q = int.Parse(textBoxНомер.Text);
            SqlCommand cmd = new SqlCommand();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = connectionString;
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "[process3]";
            cmd.Parameters.Clear();
            SqlParameter p1 = cmd.Parameters.Add("@nameFlight", SqlDbType.Int);

          
            p1.Direction = ParameterDirection.Input;

            p1.Value = q;
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();//!!!!!!!
            timeTableDataGridView.DataSource = reader;//!!!!!

Edited 5 Years Ago by Mystery_Planet: oops

This article has been dead for over six months. Start a new discussion instead.