ferday ferday -4 years ago 74
SQL Question

sql "require open and available connection"

just learning databases to store a (large amount) of user entry data.

i have the following, which checks a record and chooses whether to update or create new

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 > 0)
{
jobCardExistingTable(connection);
digCardExistingTable(connection);
//insert into existing table code
}


if i use either jobCardExistingTable or digCardExisting table, they work fine. if i use both, i get the error

require open and available connection


i assume that the first ExecuteNonQuery (which are contained in the jobCard and digCard 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?

edit: part of the issue is jobCardTable (digCardTable is identical, just a different query)

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();
}
}


edit: solved - realised that using{} closes the thread. took all the using{} out of the methods, and used a single using{} to encompass all the method calls and it works

Answer Source

You should not use using (connection) if you are using same connection in other part of code. using dispose connection and make unavailable for further connection.

So, your jobCardNewTable method implementation should be without using statement :

public void jobCardNewTable(SqlConnection connection)
{
        string sc3 = "";
        sc3 = @"INSERT INTO job1 (" + pv.jobstring + ") VALUES (" + pv.jobparam + ")";
        SqlCommand cmd = new SqlCommand(sc3, connection);
        queryParams(cmd, 0);
        cmd.ExecuteNonQuery();

}

I would recommend to create new connection whenever you need it and dispose it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download