如何从表中检索数据并插入到另一个表中?
问题描述:
我试图从客户表中检索“CUSTOMER_ID”,并将其插入如何从表中检索数据并插入到另一个表中?
fare_tariff(tariff_id, customer_id, total_price)
所以我检索Customer表,如下CUSTOMER_ID:
using (SqlCommand command = new SqlCommand("SELECT customer_id FROM Customer WHERE UserName = '" + username + "' Password = '"+password +"' ", connection))
{
string cust_id = customer_id.ToString();
SqlDataReader myReader = command.ExecuteReader();
if (myReader.Read())
{
cust_id = myReader["customer_id"].ToString();
}
int c_id = Convert.ToInt32(cust_id);
myReader.Close();
custID(c_id);
}
,并插入CUSTOMER_ID成如表fare_tariff如下:
using (SqlCommand command = new SqlCommand("INSERT INTO flight_reservation(tariff_id, customer_id, total_price) VALUES(@val1,@val2,@val3)", connection))
{
command.Parameters.Add("@val1", SqlDbType.Int).Value = tariff_id;
command.Parameters.Add("@val2", SqlDbType.Int).Value = customer_id;
command.Parameters.Add("@val3", SqlDbType.VarChar).Value = total_price.ToString();
command.ExecuteNonQuery();
}
我将customer_id声明为存储customer_id的变量。
问题是:tariff_id和total_price插入成功,但customer_id字段为空。
需要帮助。
答
将数据提取到客户端并逐行返回到服务器 很可能会产生大的开销。还有更好的方式做同样的, 所谓的“插入/选择”查询:
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText =
"insert into Flight_Reservation(\n" +
" Customer_Id,\n" +
" Tariff_Id,\n" +
" Total_Price)\n" +
" select Customer_Id,\n" +
" @prm_Tariff_Id,\n" +
" @prm_Total_Price\n" +
" from Customer\n" +
" where (UserName = @prm_UserName)\n" +
" (Password = @prm_Password)";
command.Parameters.Add("@prm_Tariff_Id", SqlDbType.VarChar, 80).Value = tariff_id;
command.Parameters.Add("@prm_Total_Price", SqlDbType.VarChar, 80).Value = total_price.ToString();
command.Parameters.Add("@prm_UserName", SqlDbType.VarChar, 80).Value = username;
command.Parameters.Add("@prm_Password", SqlDbType.VarChar, 80).Value = password;
command.ExecuteNonQuery();
}
显示完整的代码很难理解什么ü在做什么FROM HERE – 2013-04-27 04:00:35
如果客户ID为空,你怎么看你应该这样做? – 2013-04-27 04:03:03
如何将值从'c_id'转移到'customer_id'? – 2013-04-27 04:06:11