title: SQL Server : "require open and available connection"
body: <p>I'm just learning databases to store a (large amount) of user entry data.  </p>
  
  <p>I have the following code, which checks a record and chooses whether to update or create new</p>
  
  <pre><code>using (SqlConnection connection = new SqlConnection(connectionString))
  {
      connection.Open();
      string sc1 = @"select count(*) from job1 where report = @report";
  
      SqlCommand check = new SqlCommand(sc1, connection);
      check.Parameters.AddWithValue("@report", jname);
  
      // check if the report number already exists, if not make a new table otherwise insert
      int test = (int)check.ExecuteScalar();
  
      if (test &gt; 0)
      {
          jobCardExistingTable(connection);
          digCardExistingTable(connection);
          //insert into existing table code                        
      }
  </code></pre>
  
  <p>If I use either <code>jobCardExistingTable</code> or <code>digCardExisting</code> table, they work fine. If I use both, I get the error</p>
  
  <blockquote>
    <p>require open and available connection </p>
  </blockquote>
  
  <p>I assume that the first <code>ExecuteNonQuery</code> (which are contained in the <code>jobCard</code> and <code>digCard</code> methods) is doing something with the connection - can I keep this one open, or do I have to open a new one each time I call a method? Maybe I'm doing this all wrong anyways...each method is calling a new table in the database, should I be calling them all at once?</p>
  
  <p>Edit: part of the issue is jobCardTable (digCardTable is identical, just a different query)</p>
  
  <pre><code>public void jobCardNewTable(SqlConnection connection)
  {
      using (connection)
      {
          string sc3 = "";
          sc3 = @"INSERT INTO job1 (" + pv.jobstring + ") VALUES (" + pv.jobparam + ")";
          SqlCommand cmd = new SqlCommand(sc3, connection);
          queryParams(cmd, 0);
          cmd.ExecuteNonQuery();
      }
  }
  </code></pre>
  
  <p>Edit: solved - realised that using{} disposes the connection. Took all the <code>using{}</code> out of the methods, and used a single <code>using{}</code> to encompass all the method calls and it works</p>