locked
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. RRS feed

  • Question

  • User-695975540 posted

     Hi All,

    Tyring to send in 2 strings in My access database one that keeps the HTML and one that strips out the HTML from my comment. However when I try to send the 'tm1commentstring' into the database I encounter this error: "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."

    How do I get around this error???

    Cheers

    Shock[;)]

    Code below:

    Protected Sub BtnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
            Dim commentstring As String
            Dim tm1comment As String
           
      
     '------ send htmlcomment into access database ----------------------
     
            LblPostedData.Text = HttpUtility.HtmlDecode(FCKeditor1.Value).ToString
            commentstring = LblPostedData.Text
       
           
            Dim connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Mywebsite\Websitetesting\App_Data\webdatabase.mdb")
            'open connection
            connection.Open()
            Dim Command As OleDbCommand
            Command = New OleDbCommand("INSERT INTO Comments (userName,Comment)VALUES('David','" & LblPostedData.Text & "')", connection)
            Command.ExecuteNonQuery()
            connection.Close()
           
           
       
       
            '------ send tm1commentstring into access database ----------------------
           
            tm1comment = StripHTML(commentstring)
            Dim connection2 As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Mywebsite\Websitetesting\App_Data\webdatabase.mdb")
            'open connection
            connection2.Open()
            Dim Command2 As OleDbCommand
            Command2 = New OleDbCommand("INSERT INTO Comments (userName,tm1Comment)VALUES('David','" & tm1comment & "')", connection)
            Command2.ExecuteNonQuery()
            connection2.Close()
           
           
           
        End Sub 

     

    Friday, January 16, 2009 1:35 AM

Answers

  • User-1509636757 posted

      Command2 = New OleDbCommand("INSERT INTO Comments (userName,tm1Comment)VALUES('David','" & tm1comment & "')", connection)
     

    according to your code, you should use connection2 here instead of connection in above line of code as..

     

      Command2 = New OleDbCommand("INSERT INTO Comments (userName,tm1Comment)VALUES('David','" & tm1comment & "')", connection2)

     

    but, i would recommend instead of using 2 connection object, create only one connection object and open and close it for database operations.. again open the same connection for another database operation and close it again.. finally dispose it.

    hope it helps./.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 16, 2009 2:47 AM
  • User-821857111 posted

    I guess what I want to know is that if I want to update another table called say "OtherComments" can I do this within the same block of code like i have above??

    No you can't batch statements with Access. You need to run each statement separately.  As far as why it didn't work before, rather than create a new command object each time, just change the CommandText property of the command object for the second insert, and use Parameters:

    Dim Command As OleDbCommand(connection)
    Command.CommandText = "INSERT INTO Comments (userName,tm1Comment)VALUES('David',?)"
    Command.Parameters.AddWithValue("", tm1comment)
    Command.ExecuteNonQuery()
    Command.Parameters.Clear()
    Command.CommandText = "INSERT INTO Comments (userName,Comment)VALUES('David',?)"
    Command.Parameters.AddWithValue("", LblPostedData.Text)
    Command.ExecuteNonQuery()
    connection.Close()

    But if you want all the data to appear on the same row, the working code you posted is the way to do it.  The code above will add data to two rows.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 19, 2009 9:29 AM

All replies

  • User-1509636757 posted

      Command2 = New OleDbCommand("INSERT INTO Comments (userName,tm1Comment)VALUES('David','" & tm1comment & "')", connection)
     

    according to your code, you should use connection2 here instead of connection in above line of code as..

     

      Command2 = New OleDbCommand("INSERT INTO Comments (userName,tm1Comment)VALUES('David','" & tm1comment & "')", connection2)

     

    but, i would recommend instead of using 2 connection object, create only one connection object and open and close it for database operations.. again open the same connection for another database operation and close it again.. finally dispose it.

    hope it helps./.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 16, 2009 2:47 AM
  • User-695975540 posted

    HI There,

     Thanks for your recommendation. I have just used the one connection object, however it does not update my database. I have tested the code seperately and it updates the table. However when I combine them within the same sub routine it sends in the first string "tm1comment" but not the other. When I then reverse the order in the code below and place "commentstring" first then that code is executed but the other update is not for my tm1coment string and vice versa...........very strange! Do I need to close each connection before I send another string into the same table?? Here is my code so far

    Code Below:

    Protected Sub BtnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
            Dim commentstring As String
            Dim tm1comment As String
           
        
            LblPostedData.Text = HttpUtility.HtmlDecode(FCKeditor1.Value).ToString
            commentstring = LblPostedData.Text
     tm1comment = StripHTML(commentstring)       

            '------ send comments into access database ----------------------
           
           
            Dim connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Mywebsite\Websitetesting\App_Data\webdatabase.mdb")
            'open connection
            connection.Open()
            Dim Command As OleDbCommand
            Command = New OleDbCommand("INSERT INTO Comments (userName,tm1Comment)VALUES('David','" & tm1comment & "')", connection)
            Command.ExecuteNonQuery()
           
            Command = New OleDbCommand("INSERT INTO Comments (userName,Comment)VALUES('David','" & LblPostedData.Text & "')", connection)
            Command.ExecuteNonQuery()
           
            connection.Close()
           

           
           
        ' Make the posted data block visible.
            PostedDataBlock.Visible = True
            PostedAlertBlock.Visible = True
          
           
        End Sub

    Sunday, January 18, 2009 6:55 PM
  • User-695975540 posted

    Hi All,

    I got it working however I would like to know why the above does not work?? I guess what I want to know is that if I want to update another table called say "OtherComments" can I do this within the same block of code like i have above??

    here is my working code:

    Command = New OleDbCommand("INSERT INTO Comments (userName,Comment,tm1Comment)VALUES('David','" & LblPostedData.Text & "','" & tm1comment & "')", connection)Command.ExecuteNonQuery()

    Thanks dudes

    Shock

     

     

    Sunday, January 18, 2009 7:33 PM
  • User-1509636757 posted

    first of the first, i would suggest not to use INLINE queries like you have done above. Instead of that, use Stored Procedure, that way you would get out of such many sql related errors..

    here is some helpful links >  Calling Stored procedures in C# Programming

     

    hope it helps./.

    Sunday, January 18, 2009 11:24 PM
  • User1024576976 posted

    Yes, You can do it like this

     

    INSERT INTO Comments...values(..) ;INSERT INTO Comments...values(..)

    Seperate your queries with the ";" and assign them to the command text property.

     

    Thanks

     

    Sunday, January 18, 2009 11:44 PM
  • User-695975540 posted

    Hi There,

     Thanks for the link, however I cannot understand why my original query will not wrk? My solution works, but  was wondering why having 2 seperate lines of code would not work? Still learning the basics of ASP.NET. Stored procedures was something that was mentioned to me by a consultant - I will definately have a look to see how I can impletment in my project.

     Cheers

     Shock

    Sunday, January 18, 2009 11:49 PM
  • User-821857111 posted

    I guess what I want to know is that if I want to update another table called say "OtherComments" can I do this within the same block of code like i have above??

    No you can't batch statements with Access. You need to run each statement separately.  As far as why it didn't work before, rather than create a new command object each time, just change the CommandText property of the command object for the second insert, and use Parameters:

    Dim Command As OleDbCommand(connection)
    Command.CommandText = "INSERT INTO Comments (userName,tm1Comment)VALUES('David',?)"
    Command.Parameters.AddWithValue("", tm1comment)
    Command.ExecuteNonQuery()
    Command.Parameters.Clear()
    Command.CommandText = "INSERT INTO Comments (userName,Comment)VALUES('David',?)"
    Command.Parameters.AddWithValue("", LblPostedData.Text)
    Command.ExecuteNonQuery()
    connection.Close()

    But if you want all the data to appear on the same row, the working code you posted is the way to do it.  The code above will add data to two rows.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 19, 2009 9:29 AM