CODEWITHSUNDEEP
c#mysqldebugging
r4F705
r4F705

Reputation: 65

MySQL wrong syntax only on other machines

I have created a program that monitors the progress of projects for my company but as i am testing i am encountering a very bizzare problem. When i test it under windows 10 in the pc it was created everything runs as expected. However when i test it in the computers of my co-workers that run windows 7 and 8 i get the following error in the sql query code "You have an error in your SQL syntax, check the manual that corresponds to your MySQL server version for the right syntax to use near'01 where teammember.Name="SomeName" and projects="SomeProject"' at line 1" . The code that results to the error is below. 

 public void UpdateHoursWorked(string teamMember, string projectName, float hoursWorked)
        {
            SetSafeUpdates(false);

            // Error HERE
            using (MySqlCommand cmd = new MySqlCommand("update memberprojects " +
                "join teammembers on Member = teammembers.TeamMembersID " +
                "join projects on Project = projects.ProjectsID " +
                "set HoursWorkedOnProject = HoursWorkedOnProject + " + hoursWorked + " " +
                "where teammembers.Name = \"" + teamMember + "\" and projects.ProjectName = \"" + projectName + "\"", conn))
                cmd.ExecuteNonQuery();

            // Update the total hours worked in the projects table, and re-read the projects
            UpdateTotalHoursWorked(projectName, hoursWorked);

            OnUpdate(EventArgs.Empty);
        }

I can't seem to pin point the problem as under windows 10 the program works perfectly and the syntax looks correct to me. Any idea about what might cause the problem?

Upvotes: 1

Views: 76

Answers (3)

r4F705
r4F705

Reputation: 65

I managed to solve the final issue. My computer was using English Windows but my co-workers all use Widndows in our native language. In my country decimal numbers are written using a comma (example 0,0) but in the us decimal numbers are written with a dot (example 0.0) so when other pcs sent data to the database it was wrong. To fix the issue i changed the Thread.CurrentThread.CurrentCulture to new CultureInfo("en-us")

Upvotes: 0

mybirthname
mybirthname

Reputation: 18127

Write your query using Command.Parameters. Also use @ to concatenate strings on multiple lines. Format your query !

Benefits:

1) Problems like this will not occur

2) You are protected from sql injection

3) The code is read/written easier

using (MySqlCommand cmd = new MySqlCommand())
{
    cmd.CommandText = @"
                 UPDATE
                     MemberProjects  
                 JOIN 
                     TeamMembers ON Member = TeamMembers.TeamMembersID
                 JOIN 
                     Projects ON Project = Projects.ProjectsID 
                 SET 
                     HoursWorkedOnProject = HoursWorkedOnProject +  @HoursWorked
                 WHERE
                     TeamMembers.Name = @Name AND
                     Projects.ProjectName = @ProjectName";

    cmd.Connection = conn;
    cmd.Parameters.AddWithValue("@HoursWorked", hoursWorked);
    cmd.Parameters.AddWithValue("@Name", teamMember);    
    cmd.Parameters.AddWithValue("@ProjectName", projectName);

    cmd.ExecuteNonQuery();
}

I think you can see easily the difference between good formatting and using parameters. I advise you to write the Table names infront of Member, Project it will be easier to understand the location of this fields.

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133380

Try avoid double quotes sequence (and escape) using a proper alternance with single quotes

  "update memberprojects " +
  "join teammembers on Member = teammembers.TeamMembersID " +
  "join projects on Project = projects.ProjectsID " +
  "set HoursWorkedOnProject = HoursWorkedOnProject + " + hoursWorked + " " +
  "where teammembers.Name = '" + teamMember + "'  and projects.ProjectName = '" + projectName + "'", conn))
  cmd.ExecuteNonQuery();

Upvotes: 0

Related Questions