1

I have something like that:

  • Course: CID - CName
  • Discipline: MID - MNamev - MProf

I have to create a list of disciplines, because one course will have more than one. So I followed this link and created a third table, just like that:

CREATE TABLE coursedisciplines (
courseID int references curso (CID),
disciplineID int references materias (MID),
constraint pkcoursedis primary key (courseID, disciplineID)
)

I'm able to add Data for the first table (course) and for the second (discipline). Sadly, when trying to connect them using the third, I get an error about the syntax. I looked for documentation but haven't found what I'm looking for. Here it's part if the code.

id = cmd.LastInsertedId;

try
{
    cmd.CommandText = "INSERT INTO materias(MName, MProf) VALUES (@MName, @MProf)";
    cmd.Parameters.AddWithValue("@MName", txtMName.Text);
    cmd.Parameters.AddWithValue("@MProf", txtMProf.Text);

    cmd.ExecuteNonQuery();

    //The error is below
    cmd.CommandText = "INSERT INTO coursedisciplines(courseID, disciplineID) VALUES(id, SELECT MID FROM discipline WHERE MName = '"+ txtMName.Text +"')"; 
    cmd.ExecuteNonQuery();
}

Do you guys know what is wrong? Is this method really bad? I read a bit about serialize but don't want to go this way.

EDIT It worked here. Had to use select insert instead of select values. Thanks!

Community
  • 1
  • 1
ArthurMFB
  • 65
  • 13
  • 2
    What exactly is the error message? – MortenSickel Jan 30 '16 at 21:26
  • Please add a language-specific tag. Is it `C#` or something? – PM 77-1 Jan 30 '16 at 21:31
  • The error was: "There is a sintax error in the line, check the documentation", something like that. And the last time I added a language, you guys downvoted me asking how it was related to c#. So, as it is only about mysql, I see no need to add c# in the post. – ArthurMFB Jan 30 '16 at 22:08

2 Answers2

0

I do not know is this is the entire problem, but in

INSERT INTO coursedisciplines(courseID,disciplineID) 
VALUES(id, SELECT MID FROM discipline WHERE MName = '"+ txtMName.Text +"')

mysql will try to insert the value from the field 'id' which does not exist in the courcedisciplines table. You need to get the id from the former insert and reuse that in this query. Also the subquery needs to be in paranthesis: (SELECT MID ... +"') (that is probably what gives the syntax error)

MortenSickel
  • 2,118
  • 4
  • 26
  • 44
0

Your second insert is not correct. You want insert . . . select rather than insert . . . values:

INSERT INTO coursedisciplines(courseID,disciplineID)
    SELECT @id, MID
    FROM discipline WHERE MName = '"+ txtMName.Text +"';

I suspect that @id should be a parameter in the query, so you can pass it in.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That's right. Id is a parameter and I get it when the person creates a course. Now its working fine. I don't have 15 reputation, so I can't upvote you. Sorry. – ArthurMFB Jan 30 '16 at 21:43