Why does this loop not loop?

Question

string GetSerials = "SELECT SerialNumber from Warranty";
            string TestUpdateDates = "UPDATE Warranty SET StartDate = '@StartDate', EndDate = '@EndDate' WHERE SerialNumber = '@result'";
            //string TestUpdateDates2 = "UPDATE Warranty SET StartDate = cDate(Format('@StartDate', 'MM/dd/yyyy')), EndDate = cDate(Format('@EndDate', 'MM/dd/yyyy')) WHERE(SerialNumber = '@result')";

DataTable dataTable = new DataTable();
  using (var conn1 = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Blah\Blah\Blah\Warranty.accdb"))
  using (OleDbCommand serialCommand = new OleDbCommand(GetSerials, conn1))
  {
    conn1.Open();
    dataTable.Load(serialCommand.ExecuteReader());

    foreach (DataRow row in dataTable.Rows)
    {
      var result = row["SerialNumber"].ToString();
      WebRequest request = WebRequest.Create("urlpart1" + result + "urlpart2");
      string json;
      var response = request.GetResponse();
      request.ContentType = "application/json; charset=utf-8";

      using (var streamr = new StreamReader(response.GetResponseStream()))
      using (OleDbCommand testupdateCommand = new OleDbCommand(TestUpdateDates, conn1))
      using (OleDbCommand updateCommand = new OleDbCommand(UpdateDates, conn1))
      using (OleDbCommand deleteCommand = new OleDbCommand(DeleteIncomplete, conn1))
      {
        json = streamr.ReadToEnd();
        List<MyObject> list = JsonConvert.DeserializeObject<List<MyObject>>(json);
        MyObject obj = list[0]; //Base Warranty

        // obj -- Base Warranty
        var StartDate = obj.Start;
        var EndDate = obj.End;

        //testupdateCommand.Parameters.Add("@StartDate", OleDbType.Date).Value = StartDate;
        //testupdateCommand.Parameters.Add("@EndDate", OleDbType.Date).Value = EndDate;
        testupdateCommand.Parameters.AddWithValue("@StartDate", StartDate);
        testupdateCommand.Parameters.AddWithValue("@EndDate", EndDate);
        testupdateCommand.Parameters.AddWithValue("@result", result);

I am trying to update my database with values from a JSON response. I want to loop through all values in the column ["SerialNumber"] and with that Serial Number, I am able to get a Start Date and End Date from the url. I want to store these values into the same Serial Number from which I used to get the url into the respective fields (StartDate, EndDate).

I have commented out some of the other ways I have tried to do this but the output I get is that only the very first SerialNumber I get is changed, it doesnt continue looping through my column with 100 values. If I add

MessageBox.Show(result); //SerialNumber
MessageBox.Show(StartDate.ToString());
MessageBox.Show(EndDate.ToString());

It does loop through via MessageBox.Show but it will not update my database this way. Why doesn't it iterate though by itself?

I am using ACCESS2013 If that is important

The Update query is wrong. Remove the single quotes around the parameter placeholders. In that way they are not marker for the paramenter but literal strings. Do you have any empty try/catch around this code? — Steve, Mar 23 '16 at 19:16
Those single quotes were just an experiment haha I did remove them and try it but still no success. I do not have a try/catch around the code — Havoux, Mar 23 '16 at 19:19
Your method is too long as well - break your function into smaller components, and call them from the main method (or from each other). That will enable you to test pieces independently, and enable reuse. — D Stanley, Mar 23 '16 at 19:19
Anyway, I think that you should also add the missing part of the foreach and how do you execute the command — Steve, Mar 23 '16 at 19:20
@Steve Missing part of the Foreach? And - Oh.. Execute Command... must have missed that one — Havoux, Mar 23 '16 at 19:23
@DStanley I disagree. This method is perfectly fine length-wise. It's stupid how some people wrap every 2 lines of code in a function nowadays... and this function is doing a clear single task. — Alex McMillan, Mar 23 '16 at 19:23
@Steve The json response should change depending on the `@result `parameter that is entered in the url — Havoux, Mar 23 '16 at 19:24
And where is the call executed? Please post the whole foreach loop code? — Nikki9696, Mar 23 '16 at 19:29
@Nikki9696 after declaring the result param my code ends - I left out an execute command - Should it be something like `testupdateCommand.ExecuteNonQuery();` ? — Havoux, Mar 23 '16 at 19:34
@Nikki9696 i feel really dumb, It worked. So glad I asked instead of wasting hours trying to figure this out. Thank you — Havoux, Mar 23 '16 at 19:38

lesley belgium cuber houben · Accepted Answer · 2016-04-12T15:20:54.997

Don't quote the parameters :)

string GetSerials = "SELECT SerialNumber from Warranty";
                string TestUpdateDates = "UPDATE Warranty SET StartDate = @StartDate, EndDate = @EndDate WHERE SerialNumber = @result";
                //string TestUpdateDates2 = "UPDATE Warranty SET StartDate = cDate(Format(@StartDate, 'MM/dd/yyyy')), EndDate = cDate(Format(@EndDate, 'MM/dd/yyyy')) WHERE(SerialNumber = @result)";

    DataTable dataTable = new DataTable();
      using (var conn1 = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Blah\Blah\Blah\Warranty.accdb"))
      using (OleDbCommand serialCommand = new OleDbCommand(GetSerials, conn1))
      {
        conn1.Open();
        dataTable.Load(serialCommand.ExecuteReader());

        foreach (DataRow row in dataTable.Rows)
        {
          var result = row["SerialNumber"].ToString();
          WebRequest request = WebRequest.Create("urlpart1" + result + "urlpart2");
          string json;
          var response = request.GetResponse();
          request.ContentType = "application/json; charset=utf-8";

          using (var streamr = new StreamReader(response.GetResponseStream()))
          using (OleDbCommand testupdateCommand = new OleDbCommand(TestUpdateDates, conn1))
          using (OleDbCommand updateCommand = new OleDbCommand(UpdateDates, conn1))
          using (OleDbCommand deleteCommand = new OleDbCommand(DeleteIncomplete, conn1))
          {
            json = streamr.ReadToEnd();
            List<MyObject> list = JsonConvert.DeserializeObject<List<MyObject>>(json);
            MyObject obj = list[0]; //Base Warranty

            // obj -- Base Warranty
            var StartDate = obj.Start;
            var EndDate = obj.End;

            //testupdateCommand.Parameters.Add("@StartDate", OleDbType.Date).Value = StartDate;
            //testupdateCommand.Parameters.Add("@EndDate", OleDbType.Date).Value = EndDate;
            testupdateCommand.Parameters.AddWithValue("@StartDate", StartDate);
            testupdateCommand.Parameters.AddWithValue("@EndDate", EndDate);
            testupdateCommand.Parameters.AddWithValue("@result", result);

Why does this loop not loop?

1 Answers1