如何在insert语句中使用select子查询？
如何在insert语句中使用select子查询？
提问于 2019-09-13 10:20:03
我试图使用insert语句将一些值插入到数据库中。我还必须使用select语句从另一个表中获取对应于所选选项的键。
我尝试了几个问题，但都没有奏效。
string query3 = "insert into students (FirstName, LastName, FatherName,
Email, DateBirth, DateReg, Adress, Gender, Specialization, Country,
Province, City) values ('"
+ this.txt_fname.Text + "','" + this.txt_lname.Text + "','"
+ this.txt_fathername.Text + "','" + this.txt_email.Text + "','"
+ this.date_birth.Text + "', '" + this.date_reg.Text + "','"
+ this.txt_adress.Text + "','" + this.Gender
+ "', (select specialization_id from specialization where SpecializationName = '" + this.specialization.Text
+ "'),
(select country_id from country where CountryName ='" + this.comboBox2.Text
+ "'),(select province_id from province where ProvinceName ='"
+ this.comboBox4.Text
+ "'),(select city_id from city where CityName ='"+ this.comboBox3.Text + "');";我希望输出“保存”，但我得到{“不正确的语法附近‘；’”}
当我使用：
'" + ("SELECT specialization_id from specialization where SpecializationName =" + this.specialization.Text)+ "' 而不是(上面写的)：
(select specialization_id from specialization where SpecializationName = '" + this.specialization.Text + "')我得到：
{“当将varchar值'SELECT specialization_id从专门化转换为数据类型int时，转换失败，其中SpecializationName =Informatica‘”}
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-09-13 12:06:14
我通常要提醒的是，我不是一个C#程序员，我几乎不知道，但是我之前链接的文档已经足够让我正确地写这个了：
string commandText = "INSERT INTO dbo.student (FirstName, LastName, FatherName, Email, DateBirth,DateReg, Adress, Gender, Specialization, Country, Province,City) " +
"SELECT @FirstName,@LastName, @Fathername, @Email, @DateBirth, @DateReg, @Address, @Gender, s.specialization_id, c.country_id, p.province_id, cy.city_id " +
"FROM (SELECT specialization_id FROM dbo.specialization WHERE SpecializationName = @Specialization) s " +
"CROSS APPLY (select country_id from country where CountryName = @Country) c " +
"CROSS APPLY (select province_id from province where ProvinceName = @Province) p " +
"CROSS APPLY (select city_id from city where CityName = @City) cy;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add(@FirstName, SqlDbType.VarChar,50).Value = this.txt_fname.Text;
command.Parameters.Add(@LastName, SqlDbType.VarChar,50).Value = this.txt_lname.Text;
command.Parameters.Add(@Fathername, SqlDbType.VarChar,50).Value = this.txt_fathername.Text;
command.Parameters.Add(@Email, SqlDbType.VarChar,50).Value = this.txt_email.Text;
command.Parameters.Add(@DateBirth, SqlDbType.Date).Value = this.date_birth.Text; //Shouldn't this be a date picker object?
command.Parameters.Add(@DateReg, SqlDbType.Date).Value = this.date_reg.Text; //Shouldn't this be a date picker object?
command.Parameters.Add(@Address, SqlDbType.VarChar,200).Value = this.txt_adress.Text; //It's spelt Address (2 d's)
command.Parameters.Add(@Gender, SqlDbType.VarChar,10).Value = this.Gender; //Why did this not have the Text property?
command.Parameters.Add(@Specialization, SqlDbType.VarChar,50).Value = this.specialization.Text;
command.Parameters.Add(@CountryName, SqlDbType.VarChar,50).Value = this.comboBox2.Text; //You should name this combo box
command.Parameters.Add(@Province, SqlDbType.VarChar,50).Value = this.comboBox4.Text; //You should name this combo box
command.Parameters.Add(@City, SqlDbType.VarChar,50).Value = this.comboBox3.Text;//You should name this combo box
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57921760
复制相关文章
相似问题
腾讯云开发者
