如何从一列检索数据引用同一表中的另外两列？ [英] How can I retrieve data from one column refer to another two columns from the same table ?

问题描述

我有一个表名为flight_info（flight_id，flight_no，depart_from，destination，depart_time，arrival_time）。

现在我想根据列depart_from和destination来检索flight_id。

我写的SQL如下：

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";

在运行时，它显示一个指向@depart和@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();

它无效。需要帮助。

it is not working. Help needed.

推荐答案

您好Sopner Feriwala，



查看您的代码，

Hi Sopner Feriwala,

Looking at your code,

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

它指定@离开和@destination是参数。你可以在sql命令中添加参数，或者只是将值传递给字符串，如下所示

it specifies that @depart and @destination are the parameters. You can either add paramters to the sql command or just pass the values into the string like below

string str = "SELECT fligth_id FROM flight_info WHERE  depart_from = " + strDepart + " AND destination = " + strDestination";

这里strDestination和strDepart是两个字符串变量并赋值在传入查询之前。



我建议你不要将值直接传递给查询，而是将值作为参数传递并使用存储过程为了避免安全问题。假设您使用的是SQL Server 2005/08/12版本之一，请按照以下链接获取更多详细信息。



http://stackoverflow.com/questions/3806229/how-to-pass-value -to-a-stored-procedure-using-c-sharp [ ^ ]



http://net-informations.com/csprj/data-providers/cs-procedure-parameter.htm [ ^ ]



谢谢，

Vamsi

Here strDestination and strDepart are two string variables and assign the values before passing into the query.

Rather than passing the values directly into the query, I suggest you to take a look at passing the values as parameters and using stored procedures in order to avoid security issues. Assuming you are using one of the SQL Server 2005/08/12 versions, follow the below links for more details.

http://stackoverflow.com/questions/3806229/how-to-pass-value-to-a-stored-procedure-using-c-sharp[^]

http://net-informations.com/csprj/data-providers/cs-procedure-parameter.htm[^]

Thank you,
Vamsi

查看以下代码，看看它是否有效：

Take a look at following code, see if it works:

// Find the Employee and display it in the List Box
        private void btnFindFlightId_Click(object sender, System.EventArgs e)
        {
            SqlDataReader rdr = null;
            SqlConnection con = null;
            SqlCommand cmd = null;
            int flightId = 0;
            try
            {
                // Open connection to the database
                string ConnectionString = "server=YourServerName;uid=UserName;"+
                    "pwd=Password; database=YourDatabaseName"; // Use your database connection here.
                con = new SqlConnection(ConnectionString);
                con.Open();
                // Set up a command with the given query and associate
                // this with the current connection.
                string CommandText = "SELECT Flight_Id" +
                                     "  FROM Flight_Info" +
                                     " WHERE (depart_from = @depart)" +
                                     " AND destination = @destination";
                cmd = new SqlCommand(CommandText);
                cmd.Connection = con;
                // Add LastName to the above defined paramter @Find
                cmd.Parameters.Add(
                    new SqlParameter(
                    "@depart", // The name of the parameter to map
                    System.Data.SqlDbType.VarChar, // SqlDbType values
                    20, // The width of the parameter
                    "Depart"));  // The name of the source column

                cmd.Parameters.Add(
                    new SqlParameter(
                    "@destination", // The name of the parameter to map
                    System.Data.SqlDbType.VarChar, 
                    20, // The width of the parameter
                    "Destination"));  
                // Fill the parameter with the value retrieved
                // from the text field
                cmd.Parameters["@depart"].Value = txtDepart.Text; // This is the input field. 
                cmd.Parameters["@destination"].Value = txtDestination.Text; // This is the input field. 
                // Execute the query
                rdr = cmd.ExecuteReader();
                while(rdr.Read())
                {
                    flightId = Convert.ToInt32(rdr[Flight_Id]);// you can write your own way of using flight id further 
                }
            }
            catch(Exception ex)
            {
                // error message
                throw(ex.Message);
            }
            finally
            {
                // Close data reader object and database connection
                if (rdr != null)
                    rdr.Close();
                if (con.State == ConnectionState.Open)
                    con.Close();
            }
        }
    }

这篇关于如何从一列检索数据引用同一表中的另外两列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！