CODEWITHSUNDEEP
c#asp.netsql-server
Sazzad Hossain
Sazzad Hossain

Reputation: 35

How to retrieve data from one column refer to another two columns from the same table?

I have a table name

flight_info (flight_id, flight_no, depart_from, destination, depart_time, arrive_time)

Now I want to retrieve only

flight_id on the basis of column depart_from and destination

I wrote the SQL as following:

string str = "SELECT fligth_id FROM flight_info WHERE  depart_from = @depart AND destination = @destination"

While running, it's showing an error pointing @depart and @destination. Can you help me, How can I specify those scalar variable.?

I tried it..

SqlDataReader myReader;
string depart = myReader["depart_from"].ToString();
string destination = myReader["destination"].ToString();

But, it's not working. Help needed.

Upvotes: 1

Views: 911

Answers (1)

Soner Gönül
Soner Gönül

Reputation: 98830

You are using parameters in your query, but you didn't specify them.

Try like this;

using (SqlConnection connection = new SqlConnection(connectionString))
{
   connection.Open();
   using (SqlCommand command = new SqlCommand("SELECT fligth_id FROM flight_info WHERE  depart_from = @depart AND destination = @destination", connection))
   {
        command.Parameters.Add("@depart", depart);
        command.Parameters.Add("@destination", destination);
        SqlDataReader myReader = command.ExecuteReader();
        while (myReader.Read())
        {
           int fligth_id = reader.GetInt32(0);
        }
   }
}

Since your query returns only fligth_id column, you can't access depart_from and destination columns with that way. SqlDataReader reads database rows one-by-one.

If you return other columns in your query like;

SELECT fligth_id, depart_from, destination

You can read them the same way like;

while (myReader.Read())
{
    int fligth_id = reader.GetInt32(0);
    string depart_from = reader.GetString(1);
    string destination = reader.GetString(2);
}

Upvotes: 1

Related Questions