exception on : ra = myCommand.ExecuteNonQuery()

30 Dec 2005 6:32 PM

barret bonden

(VB Exoress 20005)

I get an exception on this line : ra = myCommand.ExecuteNonQuery()

{"ExecuteNonQuery requires an open and available Connection. The
connection's current state is closed."}

in this code:

Dim myConnection As OleDbConnection

Dim myCommand As OleDbCommand

Dim ra As Integer

myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\t.mdb")

myCommand = New OleDbCommand("delete from main where last = 'freud'",
myConnection)

ra = myCommand.ExecuteNonQuery()

MessageBox.Show("Records Deleted" & ra)

myConnection.Close()

whereas this code works fine :

myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\t.mdb")

myCommand = New OleDbCommand("select * from main", myConnection)

Dim myReader As OleDb.OleDbDataReader

myConnection.Open()

myReader = myCommand.ExecuteReader

Do Until myReader.Read = False

MessageBox.Show(myReader("last"))

Loop

myReader.Close()

myConnection.Close()

30 Dec 2005 6:37 PM

Marina

The message reallhy says it all.

ExecuteNonQuery requires an open connection.
You never open your connection.

So, the solution is to open the connection before running the query.

Show quote

"barret bonden" <art***@networks-cc.com> wrote in message
news:J1ftf.62$SG6.52@fe08.lga...
> (VB Exoress 20005)
>
> I get an exception on this line : ra = myCommand.ExecuteNonQuery()
>
> {"ExecuteNonQuery requires an open and available Connection. The
> connection's current state is closed."}
>
> in this code:
>
> Dim myConnection As OleDbConnection
>
> Dim myCommand As OleDbCommand
>
> Dim ra As Integer
>
> myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\t.mdb")
>
> myCommand = New OleDbCommand("delete from main where last = 'freud'",
> myConnection)
>
> ra = myCommand.ExecuteNonQuery()
>
> MessageBox.Show("Records Deleted" & ra)
>
> myConnection.Close()
>
>
>
>
>
> whereas this code works fine :
>
> myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\t.mdb")
>
> myCommand = New OleDbCommand("select * from main", myConnection)
>
> Dim myReader As OleDb.OleDbDataReader
>
> myConnection.Open()
>
> myReader = myCommand.ExecuteReader
>
> Do Until myReader.Read = False
>
> MessageBox.Show(myReader("last"))
>
> Loop
>
> myReader.Close()
>
> myConnection.Close()
>
>

30 Dec 2005 7:26 PM

barret bonden

Many thanks - that was it -

I can read Kant and Sartre and PHP - somehow the syntax of ADO net blinds
me - too many abstractions from the data itself - I'll keep plugging away.

Show quote

"Marina" <someone@nospam.com> wrote in message
news:e%23I4UAXDGHA.2704@TK2MSFTNGP15.phx.gbl...
> The message reallhy says it all.
>
> ExecuteNonQuery requires an open connection.
> You never open your connection.
>
> So, the solution is to open the connection before running the query.
>
> "barret bonden" <art***@networks-cc.com> wrote in message
> news:J1ftf.62$SG6.52@fe08.lga...
> > (VB Exoress 20005)
> >
> > I get an exception on this line : ra = myCommand.ExecuteNonQuery()
> >
> > {"ExecuteNonQuery requires an open and available Connection. The
> > connection's current state is closed."}
> >
> > in this code:
> >
> > Dim myConnection As OleDbConnection
> >
> > Dim myCommand As OleDbCommand
> >
> > Dim ra As Integer
> >
> > myConnection = New

OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Show quote

> > Source=C:\t.mdb")
> >
> > myCommand = New OleDbCommand("delete from main where last = 'freud'",
> > myConnection)
> >
> > ra = myCommand.ExecuteNonQuery()
> >
> > MessageBox.Show("Records Deleted" & ra)
> >
> > myConnection.Close()
> >
> >
> >
> >
> >
> > whereas this code works fine :
> >
> > myConnection = New

OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Show quote

> > Source=C:\t.mdb")
> >
> > myCommand = New OleDbCommand("select * from main", myConnection)
> >
> > Dim myReader As OleDb.OleDbDataReader
> >
> > myConnection.Open()
> >
> > myReader = myCommand.ExecuteReader
> >
> > Do Until myReader.Read = False
> >
> > MessageBox.Show(myReader("last"))
> >
> > Loop
> >
> > myReader.Close()
> >
> > myConnection.Close()
> >
> >
>
>

31 Dec 2005 9:09 AM

Cor Ligthert [MVP]

Barret,

>
> I can read Kant and Sartre and PHP - somehow the syntax of ADO net blinds
> me - too many abstractions from the data itself - I'll keep plugging away.
>

Probably will that go over in a while, beside the strange keyword "Dim" is
VB very describting.

To review your code a little bit, to make it more readable (Because you
don't have the need from much conventions from older compilers or better
programmers who are full with all kind of old rules because they did it
forever that way).

I show you some little changes in your VB Net Code

Dim myConnection as New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\t.mdb")
Dim myCommand as New OleDbCommand("delete from main where last = 'freud'",
myConnection)
myConnection.Open
dim ra As Integer = myCommand.ExecuteNonQuery()
MessageBox.Show("Records Deleted: " & ra.ToString)
myConnection.Close()

Just to give an idea

(There are more reasons to do it this way, however I don't want to make it
complicated in a message)

Cor