ConnectionString property fails occasionally

[ClevelandSteve]

I have ASP.net 2.0 application that needs to perform two tasks on an Oracle 10g database. Occasionally, the second task (cn2 in the code) will fail and return a “The ConnectionString property has not been initialized” error. This code has been through many revisions and I cannot figure out what is causing this. Does anyone have any ideas?

Protected Sub btnLoad_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLoad.Click
Dim streamData As Stream
Dim sr As StreamReader
Dim stringData As String
Dim cn As OracleConnection = New OracleConnection
Dim cn2 As OracleConnection = New OracleConnection
Dim cmd As New OracleCommand
Dim cmd2 As New OracleCommand
cn.ConnectionString = Session("ConnString")
cn2.ConnectionString = Session("ConnString")
lblMessage.Text = ""
streamData = fileLoad.PostedFile.InputStream
sr = New StreamReader(streamData)
Try
stringData = sr.ReadToEnd
Catch ex As Exception
lblMessage.Text = ex.Message
lblMessage.ForeColor = Drawing.Color.Red
Finally
sr.Close()
streamData.Close()
sr.Dispose()
streamData.Dispose()
End Try
With cmd
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "GET_FILE_ID"
.Connection = cn
End With
Try
cn.Open()
cmd.ExecuteNonQuery()
Catch ex As OracleException
lblMessage.Text = ex.Message
lblMessage.ForeColor = Drawing.Color.Red
Finally
cn.Close()
cmd.Dispose()
cn.Dispose()
End Try
With cmd2
.CommandType = Data.CommandType.Text
.CommandText = "Insert Into files(xx) values(xx)"
.Connection = cn2
End With
Try
cn2.Open()
cmd2.ExecuteNonQuery()
Catch ex As OracleException
lblMessage.Text = ex.Message
lblMessage.ForeColor = Drawing.Color.Red
Finally
cn2.Close()
cmd2.Dispose()
cn2.Dispose()
End Try
If lblMessage.Text = "" Then
lblMessage.Text = "File uploaded."
lblMessage.ForeColor = Drawing.Color.Black
End If
lblMessage.Visible = Try
End Sub

 

[jbenson001]

I do not have an answer to your question but I would not create 2 connections. Just create one,and change the commandtype and commandtext properties as needed, then just dispose of the one object.

 

[ClevelandSteve]

Yes, I agree. It originally had one connection, but in my loss for a solution I created a second one thinking maybe the second command was trying to use the connection before the first one was done with it.

 

[jmeckley]

something like this

using(IDbConnection cnn = New OracleConnection(...))
{
   IDbCommand cmd1 = cnn.CreateCommand();
   cmd1.CommandText = "sql string";
   IDbParameter p1 = cmd1.CreateParameter("name");
   p1.Value = value;
   cmd1.Parameters.Add(p1);

   IDbCommand cmd2 = cnn.CreateCommand();
   cmd2.CommandText = "sql string";
   IDbParameter p2 = cmd2.CreateParameter("name");
   p2.Value = value;
   cmd1.Parameters.Add(p2);

   cnn.Open();
   using(IDbTransaction tx = cnn.BeginTransaction())
   {
      cmd1.ExecuteNonQuery();
      cmd2.ExecuteNonQuery();
      tx.Commit();
   }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ClevelandSteve]

I gave it a try but got the same error at cmd2.

 

[ClevelandSteve]

I found the problem for anyone who may find this thread during a search in the future. End users would see the "The ConnectionString property has not been initialized" error on their screen, but checking the appliction log on the web server reveled the true soruce of the problem.

 

[jbenson001]

but checking the appliction log on the web server reveled the true soruce of the problem.

Ok, so what is it??