How to use select sub Query in insert statement?

Question

I am trying to insert some values into a database, using insert statement. I have to use select statement as well to get from another table the key that corresponds to the option selected.

I tried several queries but none of them worked.

    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 + "');";

I expect the output "saved" but I get {"Incorrect syntax near ';'."}

When I use:

'" + ("SELECT specialization_id from specialization where SpecializationName =" + this.specialization.Text)+ "'

instead of (wrote above):

(select specialization_id from specialization where SpecializationName = '" + this.specialization.Text + "')

I get:

{"Conversion failed when converting the varchar value 'SELECT specialization_id from specialization where SpecializationName =Informatica Economica' to data type int."}

Don't use string concatenation to create SQL statements. That's how SQL injection attacks and conversion errors occur. Use a proper parameterized query. Quoting and sanitization can cover up some problems but not all. It's better to not have that problem to begin with — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 13, 2019 at 10:21
SqlCommand.Parameters Property - Write your code properly and parametrise, and the issue does not exist. — Thom A
– Thom A ♦, Commented Sep 13, 2019 at 10:22
You should check the contents of the query3 variable, try them in SSMS and post them here properly formatted. It's impossible to find out what that query does otherwise. Using parameters instead of concatenation would result in a far cleaner query too — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 13, 2019 at 10:27
AiR do not use the answer you have accepted as the solution. — Thom A
– Thom A ♦, Commented Sep 13, 2019 at 10:47

Thom A · Accepted Answer · 2019-09-13 13:40:35Z

My usual caveat, I'm not a C# programmer, I barely know it, but the documenation I linked before was more than enough for me to write this properly:

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
}

Collectives™ on Stack Overflow

How to use select sub Query in insert statement?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related