Databases
StackOverflow2013
Postsdbo
POMySQL Query Results end up affecting wrong table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL Query Results end up affecting wrong table
    primarykey
    data
    text
    <p>I am creating a system whereby a user is able to create new tasks to be completed, with each task having multiple steps. This is all done through one input form, with a section at the top where the task details (Name, Description etc) are entered and a section below where the name and description for each step are entered. Steps can be added using a button which creates a new set of controls via ajax when it is clicked, allowing the user to make as many steps as needed.</p> <p>The inputs are picked up correctly by the controller when the model is passed back (they are stored in a list), but somewhere in my code I have a problem, because instead of creating a new step in the steps table of my database, a duplicate task is created instead. So I end up having (1 + the number of steps) tasks created and no steps.</p> <p>I hope that makes sense to people. I am using MySQL and a custom MySQL clas I have written to handle stored procedures. I am very new to c#, the .NET framework and the MVC model so I am probably structuring my code all wrong and am probably doing things the hard way, but here goes.</p> <p>Here is my code:</p> <p>Controller:</p> <pre><code>[HttpPost] public ActionResult Index(TaskCreateModel i) { if (!ModelState.IsValid) { ModelState.AddModelError("", "Please fill the required fields. All tasks require at least 1 step."); return View(i); } //write task to the database var proc = new StoredProcService("create_task"); proc.AddParam(new MySqlParameter("Name", MySqlDbType.VarChar),i.Name); proc.AddParam(new MySqlParameter("Description", MySqlDbType.VarChar),i.Description); proc.AddParam(new MySqlParameter("Value", MySqlDbType.Float),i.Value); proc.DoQuery(); foreach (var item in i.Steps) { //add steps var proc_steps = new StoredProcService("create_task_step"); proc_steps.AddParam(new MySqlParameter("Name", MySqlDbType.VarChar), item.Name); proc_steps.AddParam(new MySqlParameter("Description", MySqlDbType.VarChar), item.Description); proc_steps.AddParam(new MySqlParameter("taskID", MySqlDbType.Int32), proc.Results.ElementAt(0)); proc.DoQuery(); } return View(); } </code></pre> <p>Stored Procedure Service:</p> <pre><code>public class StoredProcService { private MySqlConnection Connect; private MySqlCommand CMD; public List&lt;Object&gt; Results { get; private set; } public StoredProcService(string name, params MySqlParameter[] args) { QuerySetup(name); foreach (var item in args) { AddParam(item); } DoQuery(); } public StoredProcService(string name) { QuerySetup(name); } //used by all constructors private void QuerySetup(string name) { Connect = new MySqlConnection(ConfigurationManager.ConnectionStrings[&lt;my actual connetion string is here&gt;].ConnectionString); CMD = new MySqlCommand(name, Connect); CMD.CommandType = CommandType.StoredProcedure; } public void AddParam(MySqlParameter p,object pval) { CMD.Parameters.Add(p).Value = pval; } public void AddParam(MySqlParameter p) { CMD.Parameters.Add(p); } public void DoQuery() { Results = new List&lt;Object&gt;(); MySqlDataReader myReader; Connect.Open(); myReader = CMD.ExecuteReader(); int count = 0; while (myReader.Read()) { Results.Add(myReader.GetValue(count)); count++; } myReader.Close(); Connect.Close(); } } </code></pre> <p>Models used:</p> <pre><code>public class TaskCreateModel { [Required] [Display(Name = "Task Name")] public string Name { get; set; } [Display(Name = "Task Description")] public string Description { get; set; } [Required] [Display(Name = "Task Value")] [DisplayFormat(DataFormatString = "${0:F2}")] public float Value { get; set; } [Required] public List&lt;StepCreateModel&gt; Steps { get; set; } } public class StepCreateModel { [Required] [Display(Name = "Step Name")] public string Name { get; set; } [Required] [Display(Name = "Description")] public string Description { get; set; } } </code></pre> <p>Views:</p> <pre><code>@model ExampleProject.Models.TaskCreateModel @{ ViewBag.Title = "Create Task"; } &lt;h2&gt;@ViewBag.Title&lt;/h2&gt; @using (Html.BeginForm("index", "CreateTask")) { @Html.ValidationSummary(true) &lt;fieldset&gt; &lt;legend&gt; Please enter task details below: &lt;/legend&gt; @Html.Partial("_CreateTaskPartial") &lt;input type="submit" value="Create Task" /&gt; &lt;/fieldset&gt; &lt;fieldset&gt; &lt;legend&gt; Steps &lt;/legend&gt; &lt;div id="editorRows"&gt; &lt;/div&gt; @Ajax.ActionLink("Add Step","BlankEditorRow", new AjaxOptions{ InsertionMode= System.Web.Mvc.Ajax.InsertionMode.InsertAfter, HttpMethod="Get", UpdateTargetId="editorRows"}) @Html.EditorFor(x =&gt; x.Steps) &lt;/fieldset&gt; } </code></pre> <p>This is the partial view called in the previous view (I have not posted the one called by AJAX because my ajax works fine)</p> <pre><code>@model ExampleProject.Models.TaskCreateModel &lt;ul&gt; &lt;li&gt; @Html.LabelFor(m =&gt; m.Name) @Html.TextBoxFor(m =&gt; m.Name) @Html.ValidationMessageFor(m =&gt; m.Name) &lt;/li&gt; &lt;li&gt; @Html.LabelFor(m =&gt; m.Description) @Html.TextBoxFor(m =&gt; m.Description) @Html.ValidationMessageFor(m =&gt; m.Description) &lt;/li&gt; &lt;li&gt; @Html.LabelFor(m =&gt; m.Value) @Html.TextBoxFor(m =&gt; m.Value) @Html.ValidationMessageFor(m =&gt; m.Value) &lt;/li&gt; &lt;/ul&gt; </code></pre> <p>Stored Procedures:</p> <pre><code>DELIMITER $$ CREATE DEFINER=`&lt;me&gt;`@`localhost` PROCEDURE `create_task`(in name VARCHAR(128), in description VARCHAR(255), in value FLOAT) BEGIN INSERT INTO tasks (Name,Description,Value) VALUES (name,description,value); SELECT LAST_INSERT_ID() as TaskID; END CREATE DEFINER=`&lt;me&gt;`@`localhost` PROCEDURE `create_task_step`(in Name varchar(128), in Description VARCHAR(255), in TaskID INT) BEGIN INSERT INTO steps (name,description,taskID) VALUES (Name, Description, TaskID); SELECT LAST_INSERT_ID() as StepID; END </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPlausibleSarge
    1. USPlausibleSarge
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload