.Net/SQL Drop Table Problem

[The VanDerbeck Group]

The code below generates the indicated error. As a newbie to vb and SQL I
have no idea how to track this down. I did verify that the connection state
is open. My suspicion is that I have to connect to a seperate "sysobjects "
database???

THE CODE:

Dim oSQLConnection As New SqlConnection(" data source =" _
& "psi-sbs2k3 ;" _
& "initial catalog = 'JHH PRODUCT DATA' ;" _
& "Integrated Security=SSPI;" _
& "persist security info =True;")
oSQLConnection. Open()

Dim osqlCommand As New SqlCommand("if exists (select * from
dbo.sysobjects where id = object_id(N'[dbo].[dsdprod]') and
OBJECTPROPERTY( id, N'IsUserTable') = 1)" _
& " drop table [dbo].[dsdprod] ")
osqlCommand.Exe cuteNonQuery()

ERROR GENERATED IS...

An unhandled exception of type 'System.Invalid OperationExcept ion' occurred
in system.data.dll

Additional information: ExecuteNonQuery : Connection property has not been
initialized.

[The VanDerbeck Group]

RE: .Net/SQL Drop Table Problem

Also... the query runs just fine in Query Anaylzer...
"The VanDerbeck Group" wrote:
[color=blue]
> The code below generates the indicated error. As a newbie to vb and SQL I
> have no idea how to track this down. I did verify that the connection state
> is open. My suspicion is that I have to connect to a seperate "sysobjects "
> database???
>
> THE CODE:
>
> Dim oSQLConnection As New SqlConnection(" data source =" _
> & "psi-sbs2k3 ;" _
> & "initial catalog = 'JHH PRODUCT DATA' ;" _
> & "Integrated Security=SSPI;" _
> & "persist security info =True;")
> oSQLConnection. Open()
>
> Dim osqlCommand As New SqlCommand("if exists (select * from
> dbo.sysobjects where id = object_id(N'[dbo].[dsdprod]') and
> OBJECTPROPERTY( id, N'IsUserTable') = 1)" _
> & " drop table [dbo].[dsdprod] ")
> osqlCommand.Exe cuteNonQuery()
>
> ERROR GENERATED IS...
>
> An unhandled exception of type 'System.Invalid OperationExcept ion' occurred
> in system.data.dll
>
> Additional information: ExecuteNonQuery : Connection property has not been
> initialized.[/color]

[Marina]

Re: .Net/SQL Drop Table Problem

You never tell osqlCommand what connection it should use.

You open a connection, but nothing is ever referencing it or knows it
exists.

The error message is pretty clear on this...

"The VanDerbeck Group" <TheVanDerbeckG roup@discussion s.microsoft.com > wrote
in message news:350B75F1-9BDA-4E02-BAB6-901D98741769@mi crosoft.com...[color=blue]
> The code below generates the indicated error. As a newbie to vb and SQL I
> have no idea how to track this down. I did verify that the connection
> state
> is open. My suspicion is that I have to connect to a seperate "sysobjects "
> database???
>
> THE CODE:
>
> Dim oSQLConnection As New SqlConnection(" data source =" _
> & "psi-sbs2k3 ;" _
> & "initial catalog = 'JHH PRODUCT DATA' ;" _
> & "Integrated Security=SSPI;" _
> & "persist security info =True;")
> oSQLConnection. Open()
>
> Dim osqlCommand As New SqlCommand("if exists (select * from
> dbo.sysobjects where id = object_id(N'[dbo].[dsdprod]') and
> OBJECTPROPERTY( id, N'IsUserTable') = 1)" _
> & " drop table [dbo].[dsdprod] ")
> osqlCommand.Exe cuteNonQuery()
>
> ERROR GENERATED IS...
>
> An unhandled exception of type 'System.Invalid OperationExcept ion' occurred
> in system.data.dll
>
> Additional information: ExecuteNonQuery : Connection property has not been
> initialized.[/color]

[Siva M]

Re: .Net/SQL Drop Table Problem

Set the SqlCommand's Connection property before executing the query.

"The VanDerbeck Group" <TheVanDerbeckG roup@discussion s.microsoft.com > wrote
in message news:350B75F1-9BDA-4E02-BAB6-901D98741769@mi crosoft.com...
The code below generates the indicated error. As a newbie to vb and SQL I
have no idea how to track this down. I did verify that the connection state
is open. My suspicion is that I have to connect to a seperate "sysobjects "
database???

THE CODE:

Dim oSQLConnection As New SqlConnection(" data source =" _
& "psi-sbs2k3 ;" _
& "initial catalog = 'JHH PRODUCT DATA' ;" _
& "Integrated Security=SSPI;" _
& "persist security info =True;")
oSQLConnection. Open()

Dim osqlCommand As New SqlCommand("if exists (select * from
dbo.sysobjects where id = object_id(N'[dbo].[dsdprod]') and
OBJECTPROPERTY( id, N'IsUserTable') = 1)" _
& " drop table [dbo].[dsdprod] ")
osqlCommand.Exe cuteNonQuery()

ERROR GENERATED IS...

An unhandled exception of type 'System.Invalid OperationExcept ion' occurred
in system.data.dll

Additional information: ExecuteNonQuery : Connection property has not been
initialized.

[The VanDerbeck Group]

Re: .Net/SQL Drop Table Problem

Bingo. I feel stupid! Thanks for the prompt reply!

"Marina" wrote:
[color=blue]
> You never tell osqlCommand what connection it should use.
>
> You open a connection, but nothing is ever referencing it or knows it
> exists.
>
> The error message is pretty clear on this...
>
> "The VanDerbeck Group" <TheVanDerbeckG roup@discussion s.microsoft.com > wrote
> in message news:350B75F1-9BDA-4E02-BAB6-901D98741769@mi crosoft.com...[color=green]
> > The code below generates the indicated error. As a newbie to vb and SQL I
> > have no idea how to track this down. I did verify that the connection
> > state
> > is open. My suspicion is that I have to connect to a seperate "sysobjects "
> > database???
> >
> > THE CODE:
> >
> > Dim oSQLConnection As New SqlConnection(" data source =" _
> > & "psi-sbs2k3 ;" _
> > & "initial catalog = 'JHH PRODUCT DATA' ;" _
> > & "Integrated Security=SSPI;" _
> > & "persist security info =True;")
> > oSQLConnection. Open()
> >
> > Dim osqlCommand As New SqlCommand("if exists (select * from
> > dbo.sysobjects where id = object_id(N'[dbo].[dsdprod]') and
> > OBJECTPROPERTY( id, N'IsUserTable') = 1)" _
> > & " drop table [dbo].[dsdprod] ")
> > osqlCommand.Exe cuteNonQuery()
> >
> > ERROR GENERATED IS...
> >
> > An unhandled exception of type 'System.Invalid OperationExcept ion' occurred
> > in system.data.dll
> >
> > Additional information: ExecuteNonQuery : Connection property has not been
> > initialized.[/color]
>
>
>[/color]