Execute Non Query Error

I am having a problem with ExecuteNonQuery.

I have this code:
Try

strsql = "Insert into [Action] values (" &
EndItem.intItemIdentifier & ", " & intActionIdentifier
& ", '" & strOrg & "', '" & strStat & "', '" & strStatDate
& "', '" & strDesc & "', '" & strTake & "')"
Dim cmd As New OleDbCommand(strsql, conn)
cmd.ExecuteNonQuery()
Catch oledbExc As OleDbException
MessageBox.Show(oledbExc.ToString, _
"", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch ex As Exception
MessageBox.Show(ex.Message, "", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

I get this error message:
ExecuteNonQuery requires an open and available
Connection. The connection's current state is closed.

Yet, I have the connection open at the top of the form and
have used it in other instances and it runs perfectly.

Public Class ActionEdit

'Inherits System.Windows.Forms.Form
Inherits System.Windows.Forms.Form
'Public Shared CriticalParts As Form
Public Shared conStr As String
= "Provider=Microsoft.JET.OLEDB.4.0;data
source=c:\\CPRdatabase.mdb"
' Create connection object
Public Shared conn As OleDbConnection = New
OleDbConnection(conStr)

I am not sure what I am missing that gives me an error
here and not anywhere else in my form.

Thanks in advance!
Elena

Bernie
Fri Dec 05 11:39:15 CST 2003

Hi Elena,

I don't see anything wrong either - are you sure you didn't call conn.close
somewhere above?

HTH,

Bernie

"Elena" <elenareinoso@yahoo.com> wrote in message
news:05f501c3bb4a$2c655bc0$a001280a@phx.gbl...
> I am having a problem with ExecuteNonQuery.
>
> I have this code:
> Try
>
> strsql = "Insert into [Action] values (" &
> EndItem.intItemIdentifier & ", " & intActionIdentifier
> & ", '" & strOrg & "', '" & strStat & "', '" & strStatDate
> & "', '" & strDesc & "', '" & strTake & "')"
> Dim cmd As New OleDbCommand(strsql, conn)
> cmd.ExecuteNonQuery()
> Catch oledbExc As OleDbException
> MessageBox.Show(oledbExc.ToString, _
> "", MessageBoxButtons.OK, MessageBoxIcon.Error)
> Catch ex As Exception
> MessageBox.Show(ex.Message, "", _
> MessageBoxButtons.OK, MessageBoxIcon.Error)
> End Try
>
> I get this error message:
> ExecuteNonQuery requires an open and available
> Connection. The connection's current state is closed.
>
> Yet, I have the connection open at the top of the form and
> have used it in other instances and it runs perfectly.
>
> Public Class ActionEdit
>
> 'Inherits System.Windows.Forms.Form
> Inherits System.Windows.Forms.Form
> 'Public Shared CriticalParts As Form
> Public Shared conStr As String
> = "Provider=Microsoft.JET.OLEDB.4.0;data
> source=c:\\CPRdatabase.mdb"
> ' Create connection object
> Public Shared conn As OleDbConnection = New
> OleDbConnection(conStr)
>
> I am not sure what I am missing that gives me an error
> here and not anywhere else in my form.
>
> Thanks in advance!
> Elena
>

Miha
Fri Dec 05 11:44:41 CST 2003

Hi Elena,

From your code is evident that connection is created but not opened.
You are probably missing conn.Open()?

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

"Elena" <elenareinoso@yahoo.com> wrote in message
news:05f501c3bb4a$2c655bc0$a001280a@phx.gbl...
> I am having a problem with ExecuteNonQuery.
>
> I have this code:
> Try
>
> strsql = "Insert into [Action] values (" &
> EndItem.intItemIdentifier & ", " & intActionIdentifier
> & ", '" & strOrg & "', '" & strStat & "', '" & strStatDate
> & "', '" & strDesc & "', '" & strTake & "')"
> Dim cmd As New OleDbCommand(strsql, conn)
> cmd.ExecuteNonQuery()
> Catch oledbExc As OleDbException
> MessageBox.Show(oledbExc.ToString, _
> "", MessageBoxButtons.OK, MessageBoxIcon.Error)
> Catch ex As Exception
> MessageBox.Show(ex.Message, "", _
> MessageBoxButtons.OK, MessageBoxIcon.Error)
> End Try
>
> I get this error message:
> ExecuteNonQuery requires an open and available
> Connection. The connection's current state is closed.
>
> Yet, I have the connection open at the top of the form and
> have used it in other instances and it runs perfectly.
>
> Public Class ActionEdit
>
> 'Inherits System.Windows.Forms.Form
> Inherits System.Windows.Forms.Form
> 'Public Shared CriticalParts As Form
> Public Shared conStr As String
> = "Provider=Microsoft.JET.OLEDB.4.0;data
> source=c:\\CPRdatabase.mdb"
> ' Create connection object
> Public Shared conn As OleDbConnection = New
> OleDbConnection(conStr)
>
> I am not sure what I am missing that gives me an error
> here and not anywhere else in my form.
>
> Thanks in advance!
> Elena
>

Elena
Fri Dec 05 11:45:48 CST 2003

I'm sure I didn't close it... I even did a find for the
word "close" to check...

I am totally baffled on this one!

Elena

>-----Original Message-----
>Hi Elena,
>
>I don't see anything wrong either - are you sure you
didn't call conn.close
>somewhere above?
>
>HTH,
>
>Bernie
>
>"Elena" <elenareinoso@yahoo.com> wrote in message
>news:05f501c3bb4a$2c655bc0$a001280a@phx.gbl...
>> I am having a problem with ExecuteNonQuery.
>>
>> I have this code:
>> Try
>>
>> strsql = "Insert into [Action] values (" &
>> EndItem.intItemIdentifier & ", " & intActionIdentifier
>> & ", '" & strOrg & "', '" & strStat & "', '" &
strStatDate
>> & "', '" & strDesc & "', '" & strTake & "')"
>> Dim cmd As New OleDbCommand(strsql, conn)
>> cmd.ExecuteNonQuery()
>> Catch oledbExc As OleDbException
>> MessageBox.Show(oledbExc.ToString, _
>> "", MessageBoxButtons.OK,
MessageBoxIcon.Error)
>> Catch ex As Exception
>> MessageBox.Show(ex.Message, "", _
>> MessageBoxButtons.OK, MessageBoxIcon.Error)
>> End Try
>>
>> I get this error message:
>> ExecuteNonQuery requires an open and available
>> Connection. The connection's current state is closed.
>>
>> Yet, I have the connection open at the top of the form
and
>> have used it in other instances and it runs perfectly.
>>
>> Public Class ActionEdit
>>
>> 'Inherits System.Windows.Forms.Form
>> Inherits System.Windows.Forms.Form
>> 'Public Shared CriticalParts As Form
>> Public Shared conStr As String
>> = "Provider=Microsoft.JET.OLEDB.4.0;data
>> source=c:\\CPRdatabase.mdb"
>> ' Create connection object
>> Public Shared conn As OleDbConnection = New
>> OleDbConnection(conStr)
>>
>> I am not sure what I am missing that gives me an error
>> here and not anywhere else in my form.
>>
>> Thanks in advance!
>> Elena
>>
>
>
>.
>

Elena
Fri Dec 05 11:55:36 CST 2003

I've tried having conn.open, it gives the same error.


>-----Original Message-----
>Hi Elena,
>
>From your code is evident that connection is created but
not opened.
>You are probably missing conn.Open()?
>
>--
>Miha Markic - RightHand .NET consulting & development
>miha at rthand com
>
>"Elena" <elenareinoso@yahoo.com> wrote in message
>news:05f501c3bb4a$2c655bc0$a001280a@phx.gbl...
>> I am having a problem with ExecuteNonQuery.
>>
>> I have this code:
>> Try
>>
>> strsql = "Insert into [Action] values (" &
>> EndItem.intItemIdentifier & ", " & intActionIdentifier
>> & ", '" & strOrg & "', '" & strStat & "', '" &
strStatDate
>> & "', '" & strDesc & "', '" & strTake & "')"
>> Dim cmd As New OleDbCommand(strsql, conn)
>> cmd.ExecuteNonQuery()
>> Catch oledbExc As OleDbException
>> MessageBox.Show(oledbExc.ToString, _
>> "", MessageBoxButtons.OK,
MessageBoxIcon.Error)
>> Catch ex As Exception
>> MessageBox.Show(ex.Message, "", _
>> MessageBoxButtons.OK, MessageBoxIcon.Error)
>> End Try
>>
>> I get this error message:
>> ExecuteNonQuery requires an open and available
>> Connection. The connection's current state is closed.
>>
>> Yet, I have the connection open at the top of the form
and
>> have used it in other instances and it runs perfectly.
>>
>> Public Class ActionEdit
>>
>> 'Inherits System.Windows.Forms.Form
>> Inherits System.Windows.Forms.Form
>> 'Public Shared CriticalParts As Form
>> Public Shared conStr As String
>> = "Provider=Microsoft.JET.OLEDB.4.0;data
>> source=c:\\CPRdatabase.mdb"
>> ' Create connection object
>> Public Shared conn As OleDbConnection = New
>> OleDbConnection(conStr)
>>
>> I am not sure what I am missing that gives me an error
>> here and not anywhere else in my form.
>>
>> Thanks in advance!
>> Elena
>>
>
>
>.
>

Miha
Fri Dec 05 12:01:02 CST 2003

Are you sure that you are using the same conn instance?

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

"Elena" <elenareinoso@yahoo.com> wrote in message
news:006201c3bb58$fcd52890$a401280a@phx.gbl...
> I've tried having conn.open, it gives the same error.

Bernie
Fri Dec 05 13:29:42 CST 2003

Hi Elena,

Miha's post has given me an idea - is conn opened/created in a sub loop -
eg, inside an if statement, such that it might not be seen by a higher
element?

Bernie

"Elena" <elenareinoso@yahoo.com> wrote in message
news:05f501c3bb4a$2c655bc0$a001280a@phx.gbl...
> I am having a problem with ExecuteNonQuery.
>
> I have this code:
> Try
>
> strsql = "Insert into [Action] values (" &
> EndItem.intItemIdentifier & ", " & intActionIdentifier
> & ", '" & strOrg & "', '" & strStat & "', '" & strStatDate
> & "', '" & strDesc & "', '" & strTake & "')"
> Dim cmd As New OleDbCommand(strsql, conn)
> cmd.ExecuteNonQuery()
> Catch oledbExc As OleDbException
> MessageBox.Show(oledbExc.ToString, _
> "", MessageBoxButtons.OK, MessageBoxIcon.Error)
> Catch ex As Exception
> MessageBox.Show(ex.Message, "", _
> MessageBoxButtons.OK, MessageBoxIcon.Error)
> End Try
>
> I get this error message:
> ExecuteNonQuery requires an open and available
> Connection. The connection's current state is closed.
>
> Yet, I have the connection open at the top of the form and
> have used it in other instances and it runs perfectly.
>
> Public Class ActionEdit
>
> 'Inherits System.Windows.Forms.Form
> Inherits System.Windows.Forms.Form
> 'Public Shared CriticalParts As Form
> Public Shared conStr As String
> = "Provider=Microsoft.JET.OLEDB.4.0;data
> source=c:\\CPRdatabase.mdb"
> ' Create connection object
> Public Shared conn As OleDbConnection = New
> OleDbConnection(conStr)
>
> I am not sure what I am missing that gives me an error
> here and not anywhere else in my form.
>
> Thanks in advance!
> Elena
>

Bernie
Fri Dec 05 20:13:57 CST 2003

Hi Elena,

Can you show me the code where conn is opened?

Bernie

"Bernie Yaeger" <berniey@cherwellinc.com> wrote in message
news:uYNUiY2uDHA.3116@tk2msftngp13.phx.gbl...
> Hi Elena,
>
> Miha's post has given me an idea - is conn opened/created in a sub loop -
> eg, inside an if statement, such that it might not be seen by a higher
> element?
>
> Bernie
>
> "Elena" <elenareinoso@yahoo.com> wrote in message
> news:05f501c3bb4a$2c655bc0$a001280a@phx.gbl...
> > I am having a problem with ExecuteNonQuery.
> >
> > I have this code:
> > Try
> >
> > strsql = "Insert into [Action] values (" &
> > EndItem.intItemIdentifier & ", " & intActionIdentifier
> > & ", '" & strOrg & "', '" & strStat & "', '" & strStatDate
> > & "', '" & strDesc & "', '" & strTake & "')"
> > Dim cmd As New OleDbCommand(strsql, conn)
> > cmd.ExecuteNonQuery()
> > Catch oledbExc As OleDbException
> > MessageBox.Show(oledbExc.ToString, _
> > "", MessageBoxButtons.OK, MessageBoxIcon.Error)
> > Catch ex As Exception
> > MessageBox.Show(ex.Message, "", _
> > MessageBoxButtons.OK, MessageBoxIcon.Error)
> > End Try
> >
> > I get this error message:
> > ExecuteNonQuery requires an open and available
> > Connection. The connection's current state is closed.
> >
> > Yet, I have the connection open at the top of the form and
> > have used it in other instances and it runs perfectly.
> >
> > Public Class ActionEdit
> >
> > 'Inherits System.Windows.Forms.Form
> > Inherits System.Windows.Forms.Form
> > 'Public Shared CriticalParts As Form
> > Public Shared conStr As String
> > = "Provider=Microsoft.JET.OLEDB.4.0;data
> > source=c:\\CPRdatabase.mdb"
> > ' Create connection object
> > Public Shared conn As OleDbConnection = New
> > OleDbConnection(conStr)
> >
> > I am not sure what I am missing that gives me an error
> > here and not anywhere else in my form.
> >
> > Thanks in advance!
> > Elena
> >
>
>