Trying to run 2 queries in one connection

Question

I have a small piece of code I am trying to run in which I first check if the value exists and then update my database if it does not. My problem is I can run one query or the other but not both, because then it states the database is open and cannot be accessed. I know for sure I can write to the database. It is not read protected, located where I can't access it etc. The routine runs if I just try to update and not check. The check definitely results in no record. I have tried separate routines using completely different variables for the connection. I am stumped big time. I have tried closing, using, disposing, but I need someone a lot smarter than me to advise me of what I am doing wrong...

For Each strfile As String In flist
    Dim pth As String = Path.GetDirectoryName(strfile)
    Dim objReader As New System.IO.StreamReader(strfile)
    Dim dVal As String = String.Empty
    TextLine = objReader.ReadLine()
    SplitLine = Split(TextLine, ",")
    dVal = SplitLine(1)

    Using Dcon As New OleDbConnection
        Dcon.ConnectionString = dbProvider
        Dcon.Open()

        Dim q1 As String = "SELECT Shares.[_Date] FROM Shares WHERE (((Shares.[_Date])=" & dVal & "))"
        Dim comd As OleDbCommand = New OleDbCommand(q1, Dcon)
        comd.ExecuteReader()

        Dim q2 As String = "INSERT INTO Shares (Code, _Date, _Open, _High, _Low, _Close, _Volume) " &
                                                                  "SELECT F1, F2, F3, F4, F5, F6, F7 FROM [Text;HDR=NO;DATABASE=" & pth & "].[" & Path.GetFileName(strfile) & "];"

        Dim cmd As OleDbCommand = New OleDbCommand(q2, Dcon)

        cmd.ExecuteNonQuery()
        Dcon.Close()
    End Using
Next

I have tried separate routines, I have tried opening, closing and then re-opening. I have tried one connection, I have tried two. Does anybody know what I am doing wrong and why it keeps telling me the database is exclusively opened and why it won't let me write to it?

Thank you in advance to those who help.

You really ought to use better names for things. Using `comd` and `cmd` to distinguish two command objects is a great way to cause confusion. How about descritive names, e.g. `selectCommand` and `insertCommand`? — jmcilhinney, May 29 '20 at 03:47

evry1falls · Answer 1 · 2020-05-29T17:05:57.653

1

I use this code when I try to execute multiple sql-statements:

Dim ConnectionString As String = ("Connection_String")
        'LoginsVal is a Table,
        Dim SqlStr1 As String = ("Select Count(Accounts.DtCrtd) FROM Accounts WHERE Accounts.DtCrtd > " & Now.Date & ";")
        Dim SqlStr2 As String = ("INSERT INTO LoginsVal(LoginNm,DtMdfd) VALUES ('Test1'," & Now.Date & ");")
        'DtCtrtd is Date DataType, Accounts is the Table Name
        Dim ThisCmd1, ThisCmd2 As New OleDbCommand
        Using ThisConn As New OleDbConnection With {.ConnectionString = ConnectionString}
            ThisConn.Open() 'Open connection
            With ThisCmd1
                .Connection = ThisConn
                .CommandType = CommandType.Text
                .CommandText = SqlStr1
            End With
            'Debug.WriteLine(ThisCmd1.ExecuteScalar) 'use to test only without if statement below.
            If Convert.ToInt32(ThisCmd1.ExecuteScalar) <= 0 Then
                Debug.WriteLine("No records found")
            Else
                'Your Next SqlStatement
                With ThisCmd2
                    .Connection = ThisConn
                    .CommandType = CommandType.Text
                    .CommandText = SqlStr2
                End With
                Debug.WriteLine("Num of Rows affects is : " & ThisCmd2.ExecuteNonQuery)
            End If
            ThisCmd1.Dispose()
            ThisCmd2.Dispose()
            ThisConn.Close() 'Close connection
        End Using

edited May 29 '20 at 17:05

answered May 29 '20 at 04:45

evry1falls

194
4
15

If a database object exposes a .Dispose method it needs to be called. This applies to Connections and Commands. Use Using blocks. Why would you check the state of a connection you just created on the line before. You don't need the with syntax. You can pass the connection string directly to the constructor of the connection. You are executing each command twice! .ExecuteScalar returns an object. You cannot write it because it is not a string and you can't compare it to 0 because it is not a number. – Mary May 29 '20 at 08:05
I'm sorry I'm not quiet following you here, 1) you say "If a database object exposes a .Dispose method it needs to be called". Why you so? what is it has to do with my code? 2) Why would you check the state of a connection you just created on the line before....... of this connection. I've already passed the connection string directly to the constructor, again what's wrong? 3) You are executing each command twice.......number. How am I exactly executing each command twice ? Yes you are right the Debug.writeLine() line 'Iforgot to omit before committing the answer' thank you – evry1falls May 29 '20 at 09:35
About executeScalar (Yes, but the only mistake in my code is '<=' it should be '<' only or if isdbnull() function instead, as in here : https://stackoverflow.com/questions/1999020/handling-executescalar-when-no-results-are-returned) – evry1falls May 29 '20 at 09:39
1.)The .Dispose method needs to be called because these objects may be using unmanaged resources which are released in the .Dispose method. 2.) `Using ThisConn As New OleDbConnection(ConnectionString) is much shorter. Unnecessary to check state. – Mary May 29 '20 at 15:47
Count() does not return a DbNull. It always has a value. – Mary May 29 '20 at 15:49
Please turn on Option Strict. This is a 2 part process. First for the current project - In Solution Explorer double click My Project. Choose Compile on the left. In the Option Strict drop-down select ON. Second for future projects - Go to the Tools Menu -> Options -> Projects and Solutions -> VB Defaults. In the Option Strict drop-down select ON. This will save you from bugs at runtime. – Mary May 29 '20 at 15:52
OK @Mary, according to Microsoft [https://docs.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbconnection.open?view=netframework-4.5.2] : If the OleDbConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close or Dispose, or using the connection within a Using statement (I used .Close). About the State code [OK and thank you.] Still ThisConn.Open needs to be called anyway as Constructor accepts only 2 properties, as in here [https://docs.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbconnection?view=netframework-4.5.2].thanx – evry1falls May 29 '20 at 16:14
Yes, your connection is fine because of the Using but the command needs a Using also. – Mary May 29 '20 at 16:22
Count() : I use my code as it is according to this Microsoft page (https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executescalar?redirectedfrom=MSDN&view=netcore-3.1#System_Data_Common_DbCommand_ExecuteScalar) Also This answer (https://stackoverflow.com/a/1999031/13393566) – evry1falls May 29 '20 at 16:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/214922/discussion-between-evry1falls-and-mary). – evry1falls May 29 '20 at 16:25

score 0 · Answer 2 · answered May 29 '20 at 03:41

The actual error message would have told you that there was already an open reader on the connection. The obvious solution is to close the data reader you opened.

There are some serious issues with your code. You call ExecuteReader and that is what opens the data reader that is blocking your second command but you never actually do anything with that data reader. You don't assign it to a variable so you can't get data from it and you can't close it either. You could do this:

Dim reader = comd.ExecuteReader()

'Use reader here.

reader.Close()

The proper way is to use a Using block though:

Using reader = comd.ExecuteReader()
    'Use reader here.
End Using

That said, if all you're trying to do is determine whether data exists and not actually use that data then you don't need a data reader at all. Just use a query that returns a Boolean and call ExecuteScalar, e.g.

Using connection As New OleDbConnection("connection string here")
    connection.Open()

    Dim query As New OleDbCommand("SELECT COUNT(*) > 0 FROM MyTable", connection)

    If CBool(query.ExecuteScalar()) Then
        'The table does contain data.
    End If
End Using

You may be able to use a more efficient query, depending on the database.

score 0 · Answer 3 · answered May 30 '20 at 23:54

Thank you all for your help, but at 2am I had an epiphany and realised it was not the database that was the problem.

I had left the streamreader open and that was what was causing the problem.

I have taken on board your comments though and appreciate the assistance in developing better code.

Trying to run 2 queries in one connection

3 Answers3