Error C#/MySQL multiple select statements in insert statement

Question

I have a little problem to insert from 2 different table's in C# to MySql database

Here is my code where the error occurs

command.CommandText = 
    @"INSERT INTO personen (Name, Surname, GenderID, BirthplaceID) 
      VALUE @Name, @Surname, (SELECT GenderID FROM gender WHERE Gender = @GenderID),  
       (SELECT BirthplaceID FROM place WHERE placename = @BirthplaceID);";

When I replace the "@" statements and just fill in the name's, it works properly. So I want to know where the problem is.

Also the problem ain't the parameters like some answers said, cause i've added them(see below).

insertname = data.Name;
                    insertsurname = data.Surname;
                    insertgenderid= data.Gender;
                    insertbirthplaceid= data.Birthplace;
                    command.Parameters.AddWithValue("@Name", insertname);
                    command.Parameters.AddWithValue("@Surname", insertsurname);
                    command.Parameters.AddWithValue("@GenderID", insertgenderid);
                    command.Parameters.AddWithValue("@BirthplaceID", insertbirthplaceid);
                    command.ExecuteNonQuery();

Using dotConnect or some other mySQL library implementation? — Joachim Isaksson
– Joachim Isaksson, Commented Mar 24, 2012 at 18:51

Justin Pihony · Accepted Answer · 2012-03-24 19:58:32Z

3

If the problem is what you list as the title:

Here is the sqlfiddle of this validating now (tables are all int just because syntax is what we are going for not, types)

First, it should be VALUES not VALUES. And second, the VALUES must be wrapped in parentheses.

command.CommandText = 
@"INSERT INTO personen (Name, Surname, GenderID, BirthplaceID) 
  VALUES (@Name, @Surname, 
             (SELECT GenderID FROM gender WHERE Gender = @GenderID),  
             (SELECT BirthplaceID FROM place WHERE placename = @BirthplaceID)
         );";

edited Mar 24, 2012 at 19:58

answered Mar 24, 2012 at 18:46

Justin Pihony

67.2k20 gold badges154 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Leviso Over a year ago

I think I wasnt clear with my question, cause I added all the paramates, tough it wont work.

Collectives™ on Stack Overflow

Error C#/MySQL multiple select statements 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