Opening Existing RDL in Visual Studio

This HAS to be the most common question - yet I have not found it.
I have TWO developers.
One is working on reports, now the other wants to help.
How can Visual Studio open the RDL files already on the server?
It's a "team" question really. How do I do it?
Thank in advance, JerryThis behavior is not support by SQL Server 2000 Reporting Services. You
would either have to share the copy of the report already on disk or
download a copy from the server and edit that one. This feature request is
currently on our wishlist.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410120746.c9f3216@.posting.google.com...
> This HAS to be the most common question - yet I have not found it.
> I have TWO developers.
> One is working on reports, now the other wants to help.
> How can Visual Studio open the RDL files already on the server?
> It's a "team" question really. How do I do it?
> Thank in advance, Jerry|||Thank you.
Hearing "not supported" saves me lots of time in research.
Best regards, Jerry

Open Visual Studio file in Embarcadero erstudio

hello Friends,
I am trying to import a database model that I created in Microsoft visio into Embarcadero erstudio 7.1.1? Any idea how that could be done? There seems to be no option to import vsd files?I think the Professional sku of Visio can export a sql script from a .vsd

Open SSIS project error: Unable to cast COM object of type

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?

TITLE: Microsoft Visual Studio

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Did you get a solution to this problem? If so could you please post it? I'm having the exact same problem on my development server.

Sincerely

Svein Terje Gaup

|||

I have the same problem as well, anyone know how to solve it?

Many Thanks.

Warren

|||

i got it fixed without knowing how to fix it. it is kind of rediculous.

Can anyone from SSIS team or any SSIS guru answer this ?

Steve

|||

I've no idea but it seems some realated with the apartment for a COM object, maybe STA which is being used

in another thread.

My wonder is why BIDS needs to make a RCW between managed code and unmanaged code when you're going to open it.

|||

Have seen this error several times opening BIDS.

Just close BIDS and open it up again, error gone. Don't know why. I think it happens when I'm a little bit impatient and try to open a recent project while BIDS is not fully up and running yet.

It's a bit annoying, but I don't think it's a serious problem.

Pipo1

|||Have this problem too - any fixes?|||

It seems SQL 2005 does not do a good job to tell us what the error is...

Not sure if you guys did the same task I did, I got the same error when I tried to add a Maintenance Plan. I tried to find the reason/solution but no luck.. and I fixed it not because I found the answer and it is all about security...

My story is...

I set up two clustered SQL 2005 servers and tried to add a maintanience plan by using sa account authentication. However, sa account is not a network account but a SQL account. So I kept getting the error no matter I close/open how many times. And unfortunately SQL 2005 does not report this error "precisely" to me to identify where the problem is... So instead of using my local Management Tools and sa accoutn authentication, I logged in to another server on the same network domain as those two clustered servers. Then I used Management Tool installed on it and Window Authentication account which is the admin account of two clustered servers, and hahaha, it just works like I want it to.

Hope this helps,
Jet

Open SSIS project error: Unable to cast COM object of type

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?

TITLE: Microsoft Visual Studio

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Did you get a solution to this problem? If so could you please post it? I'm having the exact same problem on my development server.

Sincerely

Svein Terje Gaup

|||

I have the same problem as well, anyone know how to solve it?

Many Thanks.

Warren

|||

i got it fixed without knowing how to fix it. it is kind of rediculous.

Can anyone from SSIS team or any SSIS guru answer this ?

Steve

|||

I've no idea but it seems some realated with the apartment for a COM object, maybe STA which is being used

in another thread.

My wonder is why BIDS needs to make a RCW between managed code and unmanaged code when you're going to open it.

|||

Have seen this error several times opening BIDS.

Just close BIDS and open it up again, error gone. Don't know why. I think it happens when I'm a little bit impatient and try to open a recent project while BIDS is not fully up and running yet.

It's a bit annoying, but I don't think it's a serious problem.

Pipo1

|||Have this problem too - any fixes?|||

It seems SQL 2005 does not do a good job to tell us what the error is...

Not sure if you guys did the same task I did, I got the same error when I tried to add a Maintenance Plan. I tried to find the reason/solution but no luck.. and I fixed it not because I found the answer and it is all about security...

My story is...

I set up two clustered SQL 2005 servers and tried to add a maintanience plan by using sa account authentication. However, sa account is not a network account but a SQL account. So I kept getting the error no matter I close/open how many times. And unfortunately SQL 2005 does not report this error "precisely" to me to identify where the problem is... So instead of using my local Management Tools and sa accoutn authentication, I logged in to another server on the same network domain as those two clustered servers. Then I used Management Tool installed on it and Window Authentication account which is the admin account of two clustered servers, and hahaha, it just works like I want it to.

Hope this helps,
Jet

|||Hello

Does anyone have the solution to this issue?
Everytime I open a new SSIS package, i get the error
"Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type
'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface...failed due to the following error: Interface not registered."

Which interface do I need to register?

Doesnt happen for other packages, only SSIS.

Thanks!

Open SSIS project error: Unable to cast COM object of type

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?

TITLE: Microsoft Visual Studio

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Did you get a solution to this problem? If so could you please post it? I'm having the exact same problem on my development server.

Sincerely

Svein Terje Gaup

|||

I have the same problem as well, anyone know how to solve it?

Many Thanks.

Warren

|||

i got it fixed without knowing how to fix it. it is kind of rediculous.

Can anyone from SSIS team or any SSIS guru answer this ?

Steve

|||

I've no idea but it seems some realated with the apartment for a COM object, maybe STA which is being used

in another thread.

My wonder is why BIDS needs to make a RCW between managed code and unmanaged code when you're going to open it.

|||

Have seen this error several times opening BIDS.

Just close BIDS and open it up again, error gone. Don't know why. I think it happens when I'm a little bit impatient and try to open a recent project while BIDS is not fully up and running yet.

It's a bit annoying, but I don't think it's a serious problem.

Pipo1

|||Have this problem too - any fixes?|||

It seems SQL 2005 does not do a good job to tell us what the error is...

Not sure if you guys did the same task I did, I got the same error when I tried to add a Maintenance Plan. I tried to find the reason/solution but no luck.. and I fixed it not because I found the answer and it is all about security...

My story is...

I set up two clustered SQL 2005 servers and tried to add a maintanience plan by using sa account authentication. However, sa account is not a network account but a SQL account. So I kept getting the error no matter I close/open how many times. And unfortunately SQL 2005 does not report this error "precisely" to me to identify where the problem is... So instead of using my local Management Tools and sa accoutn authentication, I logged in to another server on the same network domain as those two clustered servers. Then I used Management Tool installed on it and Window Authentication account which is the admin account of two clustered servers, and hahaha, it just works like I want it to.

Hope this helps,
Jet

sql

Open SSIS project error: Unable to cast COM object of type

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?

TITLE: Microsoft Visual Studio

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Did you get a solution to this problem? If so could you please post it? I'm having the exact same problem on my development server.

Sincerely

Svein Terje Gaup

|||

I have the same problem as well, anyone know how to solve it?

Many Thanks.

Warren

|||

i got it fixed without knowing how to fix it. it is kind of rediculous.

Can anyone from SSIS team or any SSIS guru answer this ?

Steve

|||

I've no idea but it seems some realated with the apartment for a COM object, maybe STA which is being used

in another thread.

My wonder is why BIDS needs to make a RCW between managed code and unmanaged code when you're going to open it.

|||

Have seen this error several times opening BIDS.

Just close BIDS and open it up again, error gone. Don't know why. I think it happens when I'm a little bit impatient and try to open a recent project while BIDS is not fully up and running yet.

It's a bit annoying, but I don't think it's a serious problem.

Pipo1

|||Have this problem too - any fixes?|||

It seems SQL 2005 does not do a good job to tell us what the error is...

Not sure if you guys did the same task I did, I got the same error when I tried to add a Maintenance Plan. I tried to find the reason/solution but no luck.. and I fixed it not because I found the answer and it is all about security...

My story is...

I set up two clustered SQL 2005 servers and tried to add a maintanience plan by using sa account authentication. However, sa account is not a network account but a SQL account. So I kept getting the error no matter I close/open how many times. And unfortunately SQL 2005 does not report this error "precisely" to me to identify where the problem is... So instead of using my local Management Tools and sa accoutn authentication, I logged in to another server on the same network domain as those two clustered servers. Then I used Management Tool installed on it and Window Authentication account which is the admin account of two clustered servers, and hahaha, it just works like I want it to.

Hope this helps,
Jet

Open SSIS project error: Unable to cast COM object of type

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?

TITLE: Microsoft Visual Studio

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Did you get a solution to this problem? If so could you please post it? I'm having the exact same problem on my development server.

Sincerely

Svein Terje Gaup

|||

I have the same problem as well, anyone know how to solve it?

Many Thanks.

Warren

|||

i got it fixed without knowing how to fix it. it is kind of rediculous.

Can anyone from SSIS team or any SSIS guru answer this ?

Steve

|||

I've no idea but it seems some realated with the apartment for a COM object, maybe STA which is being used

in another thread.

My wonder is why BIDS needs to make a RCW between managed code and unmanaged code when you're going to open it.

|||

Have seen this error several times opening BIDS.

Just close BIDS and open it up again, error gone. Don't know why. I think it happens when I'm a little bit impatient and try to open a recent project while BIDS is not fully up and running yet.

It's a bit annoying, but I don't think it's a serious problem.

Pipo1

|||Have this problem too - any fixes?|||

It seems SQL 2005 does not do a good job to tell us what the error is...

Not sure if you guys did the same task I did, I got the same error when I tried to add a Maintenance Plan. I tried to find the reason/solution but no luck.. and I fixed it not because I found the answer and it is all about security...

My story is...

I set up two clustered SQL 2005 servers and tried to add a maintanience plan by using sa account authentication. However, sa account is not a network account but a SQL account. So I kept getting the error no matter I close/open how many times. And unfortunately SQL 2005 does not report this error "precisely" to me to identify where the problem is... So instead of using my local Management Tools and sa accoutn authentication, I logged in to another server on the same network domain as those two clustered servers. Then I used Management Tool installed on it and Window Authentication account which is the admin account of two clustered servers, and hahaha, it just works like I want it to.

Hope this helps,
Jet

Open SSIS project error: Unable to cast COM object of type

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?

TITLE: Microsoft Visual Studio

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Did you get a solution to this problem? If so could you please post it? I'm having the exact same problem on my development server.

Sincerely

Svein Terje Gaup

|||

I have the same problem as well, anyone know how to solve it?

Many Thanks.

Warren

|||

i got it fixed without knowing how to fix it. it is kind of rediculous.

Can anyone from SSIS team or any SSIS guru answer this ?

Steve

|||

I've no idea but it seems some realated with the apartment for a COM object, maybe STA which is being used

in another thread.

My wonder is why BIDS needs to make a RCW between managed code and unmanaged code when you're going to open it.

|||

Have seen this error several times opening BIDS.

Just close BIDS and open it up again, error gone. Don't know why. I think it happens when I'm a little bit impatient and try to open a recent project while BIDS is not fully up and running yet.

It's a bit annoying, but I don't think it's a serious problem.

Pipo1

|||Have this problem too - any fixes?|||

It seems SQL 2005 does not do a good job to tell us what the error is...

Not sure if you guys did the same task I did, I got the same error when I tried to add a Maintenance Plan. I tried to find the reason/solution but no luck.. and I fixed it not because I found the answer and it is all about security...

My story is...

I set up two clustered SQL 2005 servers and tried to add a maintanience plan by using sa account authentication. However, sa account is not a network account but a SQL account. So I kept getting the error no matter I close/open how many times. And unfortunately SQL 2005 does not report this error "precisely" to me to identify where the problem is... So instead of using my local Management Tools and sa accoutn authentication, I logged in to another server on the same network domain as those two clustered servers. Then I used Management Tool installed on it and Window Authentication account which is the admin account of two clustered servers, and hahaha, it just works like I want it to.

Hope this helps,
Jet

Open SSIS project error: Unable to cast COM object of type

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?

TITLE: Microsoft Visual Studio

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Did you get a solution to this problem? If so could you please post it? I'm having the exact same problem on my development server.

Sincerely

Svein Terje Gaup

|||

I have the same problem as well, anyone know how to solve it?

Many Thanks.

Warren

|||

i got it fixed without knowing how to fix it. it is kind of rediculous.

Can anyone from SSIS team or any SSIS guru answer this ?

Steve

|||

I've no idea but it seems some realated with the apartment for a COM object, maybe STA which is being used

in another thread.

My wonder is why BIDS needs to make a RCW between managed code and unmanaged code when you're going to open it.

|||

Have seen this error several times opening BIDS.

Just close BIDS and open it up again, error gone. Don't know why. I think it happens when I'm a little bit impatient and try to open a recent project while BIDS is not fully up and running yet.

It's a bit annoying, but I don't think it's a serious problem.

Pipo1

|||Have this problem too - any fixes?|||

It seems SQL 2005 does not do a good job to tell us what the error is...

Not sure if you guys did the same task I did, I got the same error when I tried to add a Maintenance Plan. I tried to find the reason/solution but no luck.. and I fixed it not because I found the answer and it is all about security...

My story is...

I set up two clustered SQL 2005 servers and tried to add a maintanience plan by using sa account authentication. However, sa account is not a network account but a SQL account. So I kept getting the error no matter I close/open how many times. And unfortunately SQL 2005 does not report this error "precisely" to me to identify where the problem is... So instead of using my local Management Tools and sa accoutn authentication, I logged in to another server on the same network domain as those two clustered servers. Then I used Management Tool installed on it and Window Authentication account which is the admin account of two clustered servers, and hahaha, it just works like I want it to.

Hope this helps,
Jet

Open SQL 2005 database question

Shoud a SQL 2005 database remain open in a Visual Basic program, or should
it be opened and closed in every subroutine?

Thanks for any info.Charlie wrote:

Quote:

Originally Posted by

Shoud a SQL 2005 database remain open in a Visual Basic program, or should
it be opened and closed in every subroutine?

How often will the VB program access the database?|||Charlie (jadkins4@.yahoo.com) writes:

Quote:

Originally Posted by

Shoud a SQL 2005 database remain open in a Visual Basic program, or should
it be opened and closed in every subroutine?

You don't really open or close the database, but you connect and disconnect
from the server.

It's common to do as you say open and disconnect. It's not really cheap to
open a connection to SQL Server. However, the client API maintains a
connection pool, so behind the scenes the connection is open, and is reused
when you connect anew. (If there is no connection for a certain amount of
time, typically 60 seconds, the connection is closed for real.)

Then again, in a two-tier application it's not really any major flaw to
have a global connection that you keep open.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Very frequently.

"Ed Murphy" <emurphy42@.socal.rr.comwrote in message
news:4726a8c5$0$11450$4c368faf@.roadrunner.com...

Quote:

Originally Posted by

Charlie wrote:
>

Quote:

Originally Posted by

>Shoud a SQL 2005 database remain open in a Visual Basic program, or
>should it be opened and closed in every subroutine?

>
How often will the VB program access the database?

Open printer dialog in runtime mode in VB6

Hay,
when i open report from visual basic 6 using viewer its give me only printer icon which only print direct to deafult printer without any option to choose another printer.
How i can choose which printer i want in runtime in VB6If found this:

Report.PrinterSetup 0
Report.PrintOut

But for me it was easier to create my own Printer Selection form. I added a combobox for the Printer (filling it by looping through the Printers Collection), option buttons for All Pages or a specific page range, and a spinner for number of copies. I also added properties to the SelectPrinter Form to hold the data. Once the user picks the options and clicks 'Print', VB send the data to Crystal's .Printout.

Private Sub CRViewer_PrintButtonClicked(useDefault As Boolean)

Dim cOrientation As CRPaperOrientation
Dim cSize As CRPaperSize

useDefault = False

'Capture the original Orientation and Paper Size,
' SelectPrinter resets Landscape to Portrait
cOrientation = Report.PaperOrientation
cSize = Report.PaperSize

'Reset Form and all data by calling the NewData function on the SelectPrinter Form
frmSelectPrinter.NewData

'Set Max Number of Pages
frmSelectPrinter.MaxPages = Report.PrintingStatus.NumberOfPages

'Show Form and wait for User
frmSelectPrinter.Show vbModal

If Len(frmSelectPrinter.DeviceName) > 0 Then
'If a device is selected, print report
Report.SelectPrinter frmSelectPrinter.DriverName, _
frmSelectPrinter.DeviceName, frmSelectPrinter.Port

'Set the Orientation and Paper Size to the original,
' SelectPrinter resets Landscape to Portrait
Report.PaperOrientation = cOrientation
Report.PaperSize = cSize

Report.PrintOut False, frmSelectPrinter.Copies, , _
frmSelectPrinter.FromPage, frmSelectPrinter.ToPage


End If

End Sub

Open Connections - Can't Drop

Hello,
I am using SQL Express and Visual Studion 2003. I am trying to DROP
DATABASE Customers using a Stored Procedure and also using ADO.NET.
Query:
IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
DATABASE [Customers]
ConnectionString:
RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
Password=MyPassword;Initial Catalog=master"
Everytime I call the procedure or ExecuteNonQuery I get:
Cannot Drop Database 'Customers' because database is currently in use. by
sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuer y()
I have no problems Dropping the Tables.
Thanks,
Chuck
Where is this stored procedure. If it's in the database then you won't be
able to use it to drop the database while you are in the same database.
Are you trying to run the query directly after connecting useing the
connection string just given. Or is there any "use Customer" before execute
ExecuteNonQuery().
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuer y()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>
|||Hello Thank You for you help.
I have the Stored Procedure inside the Master Table.
I am immediately running the query given after connecting. I also cannot
remove it from SQL Express Management Studio. - That is if I copy the SQL
into a query window and run it. I get the exact same error.
Chuck
"Hardik Bati" <hardikb_removethis_@.microsoft.com> wrote in message
news:OLkzPow9GHA.4376@.TK2MSFTNGP03.phx.gbl...
Where is this stored procedure. If it's in the database then you won't be
able to use it to drop the database while you are in the same database.
Are you trying to run the query directly after connecting useing the
connection string just given. Or is there any "use Customer" before execute
ExecuteNonQuery().
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuer y()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>
|||Hello,
I have been doing some testing. I am filling a Dataset using a DataAdapter.
If I create a new connection to SQL Express without calling the
DataAdapter's Fill Method I can Drop the Database.
If I Fill the Dataset with the DataAdapter, dispose the connection and the
DataAdapter and create a new connection it still wont Drop the Database.
Even if the Dataset is empty it still does not work. It appears that
calling the DataAdapter before dropping the table keeps the database in use
weather it has finished it's job or not.
I can't even call a Stored Procedure to drop the Database if I call the
DataAdapter First. Even if I run the program and call the DataAdapter and
go into SQL Express Management Studio, I still get the same error. Only
when I shut down the program or drop the table without using the DataAdapter
does it work.
Is there a way around this?
Thanks
Chuck
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
Hello,
I am using SQL Express and Visual Studion 2003. I am trying to DROP
DATABASE Customers using a Stored Procedure and also using ADO.NET.
Query:
IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
DATABASE [Customers]
ConnectionString:
RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
Password=MyPassword;Initial Catalog=master"
Everytime I call the procedure or ExecuteNonQuery I get:
Cannot Drop Database 'Customers' because database is currently in use. by
sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuer y()
I have no problems Dropping the Tables.
Thanks,
Chuck
|||The most likely cause is that the closed/disposed connection is pooled and
the pooled connection is preventing you from dropping the database. You can
specify Pooling=False in the connection string (or similar, depending on the
provider you are using) to disable connection pooling.
Another method is to execute "ALTER DATABASE MyDatabase SET SINGLE_USER WITH
ROLLBACK IMMEDIATE" to kill all connections to the database befor the drop.
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OgZmXqz9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have been doing some testing. I am filling a Dataset using a
> DataAdapter.
> If I create a new connection to SQL Express without calling the
> DataAdapter's Fill Method I can Drop the Database.
> If I Fill the Dataset with the DataAdapter, dispose the connection and the
> DataAdapter and create a new connection it still wont Drop the Database.
> Even if the Dataset is empty it still does not work. It appears that
> calling the DataAdapter before dropping the table keeps the database in
> use
> weather it has finished it's job or not.
> I can't even call a Stored Procedure to drop the Database if I call the
> DataAdapter First. Even if I run the program and call the DataAdapter and
> go into SQL Express Management Studio, I still get the same error. Only
> when I shut down the program or drop the table without using the
> DataAdapter
> does it work.
> Is there a way around this?
> Thanks
> Chuck
>
> "Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
> news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuer y()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>

Open Connections - Can't Drop

Hello,
I am using SQL Express and Visual Studion 2003. I am trying to DROP
DATABASE Customers using a Stored Procedure and also using ADO.NET.
Query:
IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
DATABASE [Customers]
ConnectionString:
RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
Password=MyPassword;Initial Catalog=master"
Everytime I call the procedure or ExecuteNonQuery I get:
Cannot Drop Database 'Customers' because database is currently in use. by
sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I have no problems Dropping the Tables.
Thanks,
ChuckWhere is this stored procedure. If it's in the database then you won't be
able to use it to drop the database while you are in the same database.
Are you trying to run the query directly after connecting useing the
connection string just given. Or is there any "use Customer" before execute
ExecuteNonQuery().
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>|||Hello Thank You for you help.
I have the Stored Procedure inside the Master Table.
I am immediately running the query given after connecting. I also cannot
remove it from SQL Express Management Studio. - That is if I copy the SQL
into a query window and run it. I get the exact same error.
Chuck
"Hardik Bati" <hardikb_removethis_@.microsoft.com> wrote in message
news:OLkzPow9GHA.4376@.TK2MSFTNGP03.phx.gbl...
Where is this stored procedure. If it's in the database then you won't be
able to use it to drop the database while you are in the same database.
Are you trying to run the query directly after connecting useing the
connection string just given. Or is there any "use Customer" before execute
ExecuteNonQuery().
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>|||Hello,
I have been doing some testing. I am filling a Dataset using a DataAdapter.
If I create a new connection to SQL Express without calling the
DataAdapter's Fill Method I can Drop the Database.
If I Fill the Dataset with the DataAdapter, dispose the connection and the
DataAdapter and create a new connection it still wont Drop the Database.
Even if the Dataset is empty it still does not work. It appears that
calling the DataAdapter before dropping the table keeps the database in use
weather it has finished it's job or not.
I can't even call a Stored Procedure to drop the Database if I call the
DataAdapter First. Even if I run the program and call the DataAdapter and
go into SQL Express Management Studio, I still get the same error. Only
when I shut down the program or drop the table without using the DataAdapter
does it work.
Is there a way around this?
Thanks
Chuck
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
Hello,
I am using SQL Express and Visual Studion 2003. I am trying to DROP
DATABASE Customers using a Stored Procedure and also using ADO.NET.
Query:
IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
DATABASE [Customers]
ConnectionString:
RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
Password=MyPassword;Initial Catalog=master"
Everytime I call the procedure or ExecuteNonQuery I get:
Cannot Drop Database 'Customers' because database is currently in use. by
sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I have no problems Dropping the Tables.
Thanks,
Chuck|||The most likely cause is that the closed/disposed connection is pooled and
the pooled connection is preventing you from dropping the database. You can
specify Pooling=False in the connection string (or similar, depending on the
provider you are using) to disable connection pooling.
Another method is to execute "ALTER DATABASE MyDatabase SET SINGLE_USER WITH
ROLLBACK IMMEDIATE" to kill all connections to the database befor the drop.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OgZmXqz9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have been doing some testing. I am filling a Dataset using a
> DataAdapter.
> If I create a new connection to SQL Express without calling the
> DataAdapter's Fill Method I can Drop the Database.
> If I Fill the Dataset with the DataAdapter, dispose the connection and the
> DataAdapter and create a new connection it still wont Drop the Database.
> Even if the Dataset is empty it still does not work. It appears that
> calling the DataAdapter before dropping the table keeps the database in
> use
> weather it has finished it's job or not.
> I can't even call a Stored Procedure to drop the Database if I call the
> DataAdapter First. Even if I run the program and call the DataAdapter and
> go into SQL Express Management Studio, I still get the same error. Only
> when I shut down the program or drop the table without using the
> DataAdapter
> does it work.
> Is there a way around this?
> Thanks
> Chuck
>
> "Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
> news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>

Open Connections - Can't Drop

Hello,
I am using SQL Express and Visual Studion 2003. I am trying to DROP
DATABASE Customers using a Stored Procedure and also using ADO.NET.
Query:
IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
DATABASE [Customers]
ConnectionString:
RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
Password=MyPassword;Initial Catalog=master"
Everytime I call the procedure or ExecuteNonQuery I get:
Cannot Drop Database 'Customers' because database is currently in use. by
sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuer y()
I have no problems Dropping the Tables.
Thanks,
Chuck
Where is this stored procedure. If it's in the database then you won't be
able to use it to drop the database while you are in the same database.
Are you trying to run the query directly after connecting useing the
connection string just given. Or is there any "use Customer" before execute
ExecuteNonQuery().
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuer y()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>
|||Hello Thank You for you help.
I have the Stored Procedure inside the Master Table.
I am immediately running the query given after connecting. I also cannot
remove it from SQL Express Management Studio. - That is if I copy the SQL
into a query window and run it. I get the exact same error.
Chuck
"Hardik Bati" <hardikb_removethis_@.microsoft.com> wrote in message
news:OLkzPow9GHA.4376@.TK2MSFTNGP03.phx.gbl...
Where is this stored procedure. If it's in the database then you won't be
able to use it to drop the database while you are in the same database.
Are you trying to run the query directly after connecting useing the
connection string just given. Or is there any "use Customer" before execute
ExecuteNonQuery().
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuer y()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>
|||Hello,
I have been doing some testing. I am filling a Dataset using a DataAdapter.
If I create a new connection to SQL Express without calling the
DataAdapter's Fill Method I can Drop the Database.
If I Fill the Dataset with the DataAdapter, dispose the connection and the
DataAdapter and create a new connection it still wont Drop the Database.
Even if the Dataset is empty it still does not work. It appears that
calling the DataAdapter before dropping the table keeps the database in use
weather it has finished it's job or not.
I can't even call a Stored Procedure to drop the Database if I call the
DataAdapter First. Even if I run the program and call the DataAdapter and
go into SQL Express Management Studio, I still get the same error. Only
when I shut down the program or drop the table without using the DataAdapter
does it work.
Is there a way around this?
Thanks
Chuck
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
Hello,
I am using SQL Express and Visual Studion 2003. I am trying to DROP
DATABASE Customers using a Stored Procedure and also using ADO.NET.
Query:
IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
DATABASE [Customers]
ConnectionString:
RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
Password=MyPassword;Initial Catalog=master"
Everytime I call the procedure or ExecuteNonQuery I get:
Cannot Drop Database 'Customers' because database is currently in use. by
sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuer y()
I have no problems Dropping the Tables.
Thanks,
Chuck
|||The most likely cause is that the closed/disposed connection is pooled and
the pooled connection is preventing you from dropping the database. You can
specify Pooling=False in the connection string (or similar, depending on the
provider you are using) to disable connection pooling.
Another method is to execute "ALTER DATABASE MyDatabase SET SINGLE_USER WITH
ROLLBACK IMMEDIATE" to kill all connections to the database befor the drop.
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OgZmXqz9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have been doing some testing. I am filling a Dataset using a
> DataAdapter.
> If I create a new connection to SQL Express without calling the
> DataAdapter's Fill Method I can Drop the Database.
> If I Fill the Dataset with the DataAdapter, dispose the connection and the
> DataAdapter and create a new connection it still wont Drop the Database.
> Even if the Dataset is empty it still does not work. It appears that
> calling the DataAdapter before dropping the table keeps the database in
> use
> weather it has finished it's job or not.
> I can't even call a Stored Procedure to drop the Database if I call the
> DataAdapter First. Even if I run the program and call the DataAdapter and
> go into SQL Express Management Studio, I still get the same error. Only
> when I shut down the program or drop the table without using the
> DataAdapter
> does it work.
> Is there a way around this?
> Thanks
> Chuck
>
> "Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
> news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuer y()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>

Open Connections - Can't Drop

Hello,
I am using SQL Express and Visual Studion 2003. I am trying to DROP
DATABASE Customers using a Stored Procedure and also using ADO.NET.
Query:
IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
DATABASE [Customers]
ConnectionString:
RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
Password=MyPassword;Initial Catalog=master"
Everytime I call the procedure or ExecuteNonQuery I get:
Cannot Drop Database 'Customers' because database is currently in use. by
sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I have no problems Dropping the Tables.
Thanks,
ChuckWhere is this stored procedure. If it's in the database then you won't be
able to use it to drop the database while you are in the same database.
Are you trying to run the query directly after connecting useing the
connection string just given. Or is there any "use Customer" before execute
ExecuteNonQuery().
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>|||Hello Thank You for you help.
I have the Stored Procedure inside the Master Table.
I am immediately running the query given after connecting. I also cannot
remove it from SQL Express Management Studio. - That is if I copy the SQL
into a query window and run it. I get the exact same error.
Chuck
"Hardik Bati" <hardikb_removethis_@.microsoft.com> wrote in message
news:OLkzPow9GHA.4376@.TK2MSFTNGP03.phx.gbl...
Where is this stored procedure. If it's in the database then you won't be
able to use it to drop the database while you are in the same database.
Are you trying to run the query directly after connecting useing the
connection string just given. Or is there any "use Customer" before execute
ExecuteNonQuery().
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>|||Hello,
I have been doing some testing. I am filling a Dataset using a DataAdapter.
If I create a new connection to SQL Express without calling the
DataAdapter's Fill Method I can Drop the Database.
If I Fill the Dataset with the DataAdapter, dispose the connection and the
DataAdapter and create a new connection it still wont Drop the Database.
Even if the Dataset is empty it still does not work. It appears that
calling the DataAdapter before dropping the table keeps the database in use
weather it has finished it's job or not.
I can't even call a Stored Procedure to drop the Database if I call the
DataAdapter First. Even if I run the program and call the DataAdapter and
go into SQL Express Management Studio, I still get the same error. Only
when I shut down the program or drop the table without using the DataAdapter
does it work.
Is there a way around this?
Thanks
Chuck
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
Hello,
I am using SQL Express and Visual Studion 2003. I am trying to DROP
DATABASE Customers using a Stored Procedure and also using ADO.NET.
Query:
IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
DATABASE [Customers]
ConnectionString:
RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
Password=MyPassword;Initial Catalog=master"
Everytime I call the procedure or ExecuteNonQuery I get:
Cannot Drop Database 'Customers' because database is currently in use. by
sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I have no problems Dropping the Tables.
Thanks,
Chuck|||The most likely cause is that the closed/disposed connection is pooled and
the pooled connection is preventing you from dropping the database. You can
specify Pooling=False in the connection string (or similar, depending on the
provider you are using) to disable connection pooling.
Another method is to execute "ALTER DATABASE MyDatabase SET SINGLE_USER WITH
ROLLBACK IMMEDIATE" to kill all connections to the database befor the drop.
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OgZmXqz9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have been doing some testing. I am filling a Dataset using a
> DataAdapter.
> If I create a new connection to SQL Express without calling the
> DataAdapter's Fill Method I can Drop the Database.
> If I Fill the Dataset with the DataAdapter, dispose the connection and the
> DataAdapter and create a new connection it still wont Drop the Database.
> Even if the Dataset is empty it still does not work. It appears that
> calling the DataAdapter before dropping the table keeps the database in
> use
> weather it has finished it's job or not.
> I can't even call a Stored Procedure to drop the Database if I call the
> DataAdapter First. Even if I run the program and call the DataAdapter and
> go into SQL Express Management Studio, I still get the same error. Only
> when I shut down the program or drop the table without using the
> DataAdapter
> does it work.
> Is there a way around this?
> Thanks
> Chuck
>
> "Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
> news:epQ433v9GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I am using SQL Express and Visual Studion 2003. I am trying to DROP
> DATABASE Customers using a Stored Procedure and also using ADO.NET.
> Query:
> IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP
> DATABASE [Customers]
> ConnectionString:
> RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa;
> Password=MyPassword;Initial Catalog=master"
>
> Everytime I call the procedure or ExecuteNonQuery I get:
> Cannot Drop Database 'Customers' because database is currently in use. by
> sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> I have no problems Dropping the Tables.
> Thanks,
> Chuck
>
>

only show fields that have data in reportviewer

Hi all,

I am using reportviewer control in Visual Web Developer. It uses a report (Report.rdlc with a table) to show all the fields. I am using a stored procedure to populate the dataset. How can I set the reportviewer or the table to increase or decrease the number of columns it shows depending on the names and number of fields pulled from the database. The user has an option of choosing the fields she/he wants on the report but right now all the coulmns show (even those which I am not selecting from the database, i.e. columns with no data).

Please help,

Thanks,

bullpit

have a look at thishttp://en.wikipedia.org/wiki/Double_posting|||

I got your point. Actually I was not sure which forum I should submit this post. I found two forums which looked related to my problem so I posted in both of them. But now I know this should not be done, would you please let me know how to delete one.

Thanks

Online query to my database

Hi. I have a database established by MS SQL, and I want to make online
queries to my database over the net by a GUI developed in Visual Studio .NET.
Could someone explain the procedure I must follow to accomplish such an aim.
I mean that database is in the desktop computer in my house and I want to
make query from my friend's desktop computer which is somewhere else. Hence,
I need something that should be performed over the net.
Thanls in advance
Hi ,
Please go through the following piece of information which will be useful
in your case.
ADO can use any OLE DB provider to establish a connection. The provider is
specified through the Provider property of the Connection object.
Microsoft SQL Server 2000 applications use SQLOLEDB to connect to an
instance of SQL Server, although existing applications can also use MSDASQL
to maintain backward compatibility.
Using the Execute method of the Connection object is one way to execute an
SQL statement against a SQL Server data source.
The Connection object allows you to:
Configure a connection.
Establish and terminate sessions with data sources.
Identify an OLE DB provider.
Execute a query.
Manage transactions on the open connection.
Choose a cursor library available to the data provider.
There are some differences in connection properties between SQLOLEDB and
MSDASQL. For information about connection properties for MSDASQL, see the
MSDN Library at Microsoft Web site.
If you are writing a connection string for use with SQLOLEDB:
Use the Initial Catalog property to specify the database.
Use the Data Source property to specify the server name.
Use the Integrated Security keyword, set to a value of SSPI, to specify
Windows Authentication (recommended),
or
use the User ID and Password connection properties to specify SQL Server
Authentication.
Security Note When possible, use Windows Authentication. If Windows
Authentication is not available, prompt users to enter their credentials at
run time. Avoid storing credentials in a file. If you must persist
credentials, you should encrypt them with the Win32 crypto API. For more
information, see "The Crypto API Function" in the MSDN Library at this
Microsoft Web site.
If you are writing a connection string for use with MSDASQL:
Use the Database keyword or Initial Catalog property to specify the
database.
Use the Server keyword or Data Source property to specify the server name.
Use the Trusted_Connection keyword, set to a value of yes, to specify
Windows Authentication (recommended),
or
Use the UID keyword or User ID property, and the Pwd keyword or Password
property to specify SQL Server Authentication.
Security Note When possible, use Windows Authentication. If Windows
Authentication is not available, prompt users to enter their credentials at
run time. Avoid storing credentials in a file. If you must persist
credentials, you should encrypt them with the Win32 crypto API. For more
information, see "The Crypto API Function" in the MSDN Library at this
Microsoft Web site.
For more information about a complete list of keywords available for use
with a SQLOLEDB connection string, see Connection Object.
Restrictions on Multiple Connections
SQLOLEDB does not allow multiple connections. Unlike MSDASQL, SQLOLEDB does
not attempt to reconnect when the connection is blocked.
Examples
A. Using SQLOLEDB to connect to an instance of SQL Server: setting
individual properties
The following Microsoft Visual Basic code fragments from the ADO
Introductory Visual Basic Sample show how to use SQLOLEDB to connect to an
instance of SQL Server.
' Initialize variables.
Dim cn As New ADODB.Connection
. . .
Dim ServerName As String, DatabaseName As String
' Put text box values into connection variables.
ServerName = txtServerName.Text
DatabaseName = txtDatabaseName.Text
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
' Set SQLOLEDB connection properties.
cn.Properties("Data Source").Value = ServerName
cn.Properties("Initial Catalog").Value = DatabaseName
' Windows NT authentication.
cn.Properties("Integrated Security").Value = "SSPI"
' Open the database.
cn.Open
B. Using SQLOLEDB to connect to an instance of SQL Server: connection
string method
The following Visual Basic code fragment shows how to use SQLOLEDB to
connect to an instance or SQL Server:
' Initialize variables.
Dim cn As New ADODB.Connection
Dim provStr As String
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
' Specify connection string on Open method.
ProvStr = "Server=MyServer;Database=northwind;Trusted_Connec tion=yes"
cn.Open provStr
C. Using MSDASQL to connect to an instance of SQL Server
To use MSDASQL to connect to an instance of SQL Server, use the following
types of connections.
The first type of connection is based on the ODBC API SQLConnect function.
This type of connection is useful in situations where you do not want to
code specific information about the data source. This may be the case if
the data source could change or if you do not know its particulars.
In the code fragment shown, the ConnectionTimeout method sets the
connection time-out value to 100 seconds. Next, the data source name, and
authentication type are passed as parameters to the Open method of the
Connection object, using an ODBC data source named MyDataSource that points
to the northwind database on an instance of SQL Server.
Dim cn As New ADODB.Connection
cn.ConnectionTimeout = 100
' DSN connection
' cn.Open "DSN=MyDataSource;Trusted_Connection=yes;"
cn.Close
The second type of connection is based on the ODBC API SQLDriverConnect
function. This type of connection is useful in situations where you want a
driver-specific connection string. To make a connection, use the Open
method of the Connection object and specify the driver, server name,
authentication type, and database. You can also specify any other valid
keywords to include in the connection string. For more information about
the keyword list, see SQLDriverConnect.
Dim cn As New ADODB.Connection
' Connection to SQL Server without using ODBC data source.
cn.Open "Driver={SQL
Server};Server=Server1;Database=northwind;Trusted_ Connection=yes"
cn.Close
Using the Connection Object
In addition to the Command object, an application can use the Connection
object to issue commands, stored procedures, and user-defined functions to
a database as if they were native methods on the Connection object. To
execute a query without using a Command object, an application can pass a
query string to the Execute method of a Connection object.
However, a Command object is required if you want to save and re-execute
the command text, or use query parameters.
To execute a command on the Connection object
Assign a name to the command using the Name property of the Command object.
Set the ActiveConnection property of the Command object to the connection.
Issue a statement where the command name is used as if it were a method on
the Connection object, followed by any parameters.
Create a Recordset object if any rows are returned.
Set the Recordset properties to customize the resulting Recordset.
Using the Connection Object to Execute Commands
This example shows how to use the Execute method of the Connection object
to execute commands.
Dim cn As New ADODB.Connection
. . .
Dim rs As New ADODB.Recordset
cmd1 = txtQuery.Text
Set rs = cn.Execute(cmd1)
After the Connection and Recordset objects are created, the variable cmd1
is assigned the value of a user-supplied query string (txtQuery.Text) from
a Microsoft Visual Basic form. The recordset is assigned the results of a
query, by calling the Execute method of the Connection object, with the
variable cmd1 used as the query string parameter.
Using the Recordset Object
The Recordset object provides methods for manipulating result sets. It
allows you to add, update, delete, and scroll through rows in the recordset.
A Recordset object can be created using the Execute method of the
Connection or Command object.
Each row in a recordset can also be retrieved and updated using the Fields
collection and the Field object. Updates on the Recordset object can be in
an immediate or batch mode. When a Recordset object is created, a cursor is
opened automatically.
The Recordset object allows you to specify the cursor type and location for
fetching the result set. With the CursorType property, you can specify
whether the cursor is read-only, forward-only, static, keyset-driven, or
dynamic. Cursor type determines if a Recordset object can be scrolled or
updated and affects the visibility of changed rows. By default, the cursor
type is read-only and forward-only.
An application can specify the location of the cursor with the
CursorLocation property. This property allows you to specify whether to use
a client or server cursor. The CursorLocation property setting is important
when you use disconnected recordsets.
The first part of the cmdExecute_Click method in the ADO Introductory
Visual Basic Sample shows an example of creating, opening, passing a
command string variable to, and positioning the cursor in a recordset.
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
. . .
cmd1 = txtQuery.Text
Set rs = New ADODB.Recordset
rs.Open cmd1, cn
rs.MoveFirst
. . .
' Code to loop through result set(s)
Thanks and regards,
Girish Sundaram
This posting is provided "AS IS" with no warranties, and confers no rights.
|||thank you for now
I will try
Probably I will turn back to you with some questions
"Girish Sundaram" wrote:

> Hi ,
> Please go through the following piece of information which will be useful
> in your case.
> ADO can use any OLE DB provider to establish a connection. The provider is
> specified through the Provider property of the Connection object.
> Microsoft? SQL Server? 2000 applications use SQLOLEDB to connect to an
> instance of SQL Server, although existing applications can also use MSDASQL
> to maintain backward compatibility.
> Using the Execute method of the Connection object is one way to execute an
> SQL statement against a SQL Server data source.
> The Connection object allows you to:
> Configure a connection.
>
> Establish and terminate sessions with data sources.
>
> Identify an OLE DB provider.
>
> Execute a query.
>
> Manage transactions on the open connection.
>
> Choose a cursor library available to the data provider.
> There are some differences in connection properties between SQLOLEDB and
> MSDASQL. For information about connection properties for MSDASQL, see the
> MSDN Library at Microsoft Web site.
> If you are writing a connection string for use with SQLOLEDB:
> Use the Initial Catalog property to specify the database.
>
> Use the Data Source property to specify the server name.
>
> Use the Integrated Security keyword, set to a value of SSPI, to specify
> Windows Authentication (recommended),
> or
> use the User ID and Password connection properties to specify SQL Server
> Authentication.
>
> Security Note When possible, use Windows Authentication. If Windows
> Authentication is not available, prompt users to enter their credentials at
> run time. Avoid storing credentials in a file. If you must persist
> credentials, you should encrypt them with the Win32? crypto API. For more
> information, see "The Crypto API Function" in the MSDN? Library at this
> Microsoft Web site.
> If you are writing a connection string for use with MSDASQL:
> Use the Database keyword or Initial Catalog property to specify the
> database.
>
> Use the Server keyword or Data Source property to specify the server name.
>
> Use the Trusted_Connection keyword, set to a value of yes, to specify
> Windows Authentication (recommended),
> or
> Use the UID keyword or User ID property, and the Pwd keyword or Password
> property to specify SQL Server Authentication.
>
> Security Note When possible, use Windows Authentication. If Windows
> Authentication is not available, prompt users to enter their credentials at
> run time. Avoid storing credentials in a file. If you must persist
> credentials, you should encrypt them with the Win32 crypto API. For more
> information, see "The Crypto API Function" in the MSDN Library at this
> Microsoft Web site.
> For more information about a complete list of keywords available for use
> with a SQLOLEDB connection string, see Connection Object.
> Restrictions on Multiple Connections
> SQLOLEDB does not allow multiple connections. Unlike MSDASQL, SQLOLEDB does
> not attempt to reconnect when the connection is blocked.
> Examples
> A. Using SQLOLEDB to connect to an instance of SQL Server: setting
> individual properties
> The following Microsoft Visual Basic? code fragments from the ADO
> Introductory Visual Basic Sample show how to use SQLOLEDB to connect to an
> instance of SQL Server.
> ' Initialize variables.
> Dim cn As New ADODB.Connection
> . . .
> Dim ServerName As String, DatabaseName As String
> ' Put text box values into connection variables.
> ServerName = txtServerName.Text
> DatabaseName = txtDatabaseName.Text
> ' Specify the OLE DB provider.
> cn.Provider = "sqloledb"
> ' Set SQLOLEDB connection properties.
> cn.Properties("Data Source").Value = ServerName
> cn.Properties("Initial Catalog").Value = DatabaseName
> ' Windows NT authentication.
> cn.Properties("Integrated Security").Value = "SSPI"
> ' Open the database.
> cn.Open
> B. Using SQLOLEDB to connect to an instance of SQL Server: connection
> string method
> The following Visual Basic code fragment shows how to use SQLOLEDB to
> connect to an instance or SQL Server:
> ' Initialize variables.
> Dim cn As New ADODB.Connection
> Dim provStr As String
> ' Specify the OLE DB provider.
> cn.Provider = "sqloledb"
> ' Specify connection string on Open method.
> ProvStr = "Server=MyServer;Database=northwind;Trusted_Connec tion=yes"
> cn.Open provStr
> C. Using MSDASQL to connect to an instance of SQL Server
> To use MSDASQL to connect to an instance of SQL Server, use the following
> types of connections.
> The first type of connection is based on the ODBC API SQLConnect function.
> This type of connection is useful in situations where you do not want to
> code specific information about the data source. This may be the case if
> the data source could change or if you do not know its particulars.
> In the code fragment shown, the ConnectionTimeout method sets the
> connection time-out value to 100 seconds. Next, the data source name, and
> authentication type are passed as parameters to the Open method of the
> Connection object, using an ODBC data source named MyDataSource that points
> to the northwind database on an instance of SQL Server.
> Dim cn As New ADODB.Connection
> cn.ConnectionTimeout = 100
> ' DSN connection
> ' cn.Open "DSN=MyDataSource;Trusted_Connection=yes;"
> cn.Close
> The second type of connection is based on the ODBC API SQLDriverConnect
> function. This type of connection is useful in situations where you want a
> driver-specific connection string. To make a connection, use the Open
> method of the Connection object and specify the driver, server name,
> authentication type, and database. You can also specify any other valid
> keywords to include in the connection string. For more information about
> the keyword list, see SQLDriverConnect.
> Dim cn As New ADODB.Connection
> ' Connection to SQL Server without using ODBC data source.
> cn.Open "Driver={SQL
> Server};Server=Server1;Database=northwind;Trusted_ Connection=yes"
> cn.Close
> Using the Connection Object
> In addition to the Command object, an application can use the Connection
> object to issue commands, stored procedures, and user-defined functions to
> a database as if they were native methods on the Connection object. To
> execute a query without using a Command object, an application can pass a
> query string to the Execute method of a Connection object.
> However, a Command object is required if you want to save and re-execute
> the command text, or use query parameters.
> To execute a command on the Connection object
> Assign a name to the command using the Name property of the Command object.
>
> Set the ActiveConnection property of the Command object to the connection.
>
> Issue a statement where the command name is used as if it were a method on
> the Connection object, followed by any parameters.
>
> Create a Recordset object if any rows are returned.
>
> Set the Recordset properties to customize the resulting Recordset.
> Using the Connection Object to Execute Commands
> This example shows how to use the Execute method of the Connection object
> to execute commands.
> Dim cn As New ADODB.Connection
> . . .
> Dim rs As New ADODB.Recordset
> cmd1 = txtQuery.Text
> Set rs = cn.Execute(cmd1)
> After the Connection and Recordset objects are created, the variable cmd1
> is assigned the value of a user-supplied query string (txtQuery.Text) from
> a Microsoft Visual Basic? form. The recordset is assigned the results of a
> query, by calling the Execute method of the Connection object, with the
> variable cmd1 used as the query string parameter.
> Using the Recordset Object
> The Recordset object provides methods for manipulating result sets. It
> allows you to add, update, delete, and scroll through rows in the recordset.
> A Recordset object can be created using the Execute method of the
> Connection or Command object.
> Each row in a recordset can also be retrieved and updated using the Fields
> collection and the Field object. Updates on the Recordset object can be in
> an immediate or batch mode. When a Recordset object is created, a cursor is
> opened automatically.
> The Recordset object allows you to specify the cursor type and location for
> fetching the result set. With the CursorType property, you can specify
> whether the cursor is read-only, forward-only, static, keyset-driven, or
> dynamic. Cursor type determines if a Recordset object can be scrolled or
> updated and affects the visibility of changed rows. By default, the cursor
> type is read-only and forward-only.
> An application can specify the location of the cursor with the
> CursorLocation property. This property allows you to specify whether to use
> a client or server cursor. The CursorLocation property setting is important
> when you use disconnected recordsets.
> The first part of the cmdExecute_Click method in the ADO Introductory
> Visual Basic Sample shows an example of creating, opening, passing a
> command string variable to, and positioning the cursor in a recordset.
> Dim cn As New ADODB.Connection
> Dim rs As ADODB.Recordset
> . . .
> cmd1 = txtQuery.Text
> Set rs = New ADODB.Recordset
> rs.Open cmd1, cn
> rs.MoveFirst
> . . .
> ' Code to loop through result set(s)
> Thanks and regards,
> Girish Sundaram
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Ahmet, you could also look at www.asp.net. They have plenty of examples for
you on that site. You should be able to cut, paste, and modify.
"Ahmet Karaca" <AhmetKaraca@.discussions.microsoft.com> wrote in message
news:D99CE06D-E875-4084-919F-333C7840B6DC@.microsoft.com...[vbcol=seagreen]
> thank you for now
> I will try
> Probably I will turn back to you with some questions
> "Girish Sundaram" wrote:
useful[vbcol=seagreen]
is[vbcol=seagreen]
MSDASQL[vbcol=seagreen]
an[vbcol=seagreen]
the[vbcol=seagreen]
at[vbcol=seagreen]
more[vbcol=seagreen]
name.[vbcol=seagreen]
at[vbcol=seagreen]
does[vbcol=seagreen]
an[vbcol=seagreen]
following[vbcol=seagreen]
function.[vbcol=seagreen]
and[vbcol=seagreen]
points[vbcol=seagreen]
a[vbcol=seagreen]
to[vbcol=seagreen]
a[vbcol=seagreen]
object.[vbcol=seagreen]
connection.[vbcol=seagreen]
on[vbcol=seagreen]
object[vbcol=seagreen]
cmd1[vbcol=seagreen]
from[vbcol=seagreen]
a[vbcol=seagreen]
recordset.[vbcol=seagreen]
Fields[vbcol=seagreen]
in[vbcol=seagreen]
is[vbcol=seagreen]
for[vbcol=seagreen]
cursor[vbcol=seagreen]
use[vbcol=seagreen]
important[vbcol=seagreen]
rights.[vbcol=seagreen]

Online query to my database

Hi. I have a database established by MS SQL, and I want to make online
queries to my database over the net by a GUI developed in Visual Studio .NET
.
Could someone explain the procedure I must follow to accomplish such an aim.
I mean that database is in the desktop computer in my house and I want to
make query from my friend's desktop computer which is somewhere else. Hence,
I need something that should be performed over the net.
Thanls in advanceHi ,
Please go through the following piece of information which will be useful
in your case.
ADO can use any OLE DB provider to establish a connection. The provider is
specified through the Provider property of the Connection object.
Microsoft SQL Server 2000 applications use SQLOLEDB to connect to an
instance of SQL Server, although existing applications can also use MSDASQL
to maintain backward compatibility.
Using the Execute method of the Connection object is one way to execute an
SQL statement against a SQL Server data source.
The Connection object allows you to:
Configure a connection.
Establish and terminate sessions with data sources.
Identify an OLE DB provider.
Execute a query.
Manage transactions on the open connection.
Choose a cursor library available to the data provider.
There are some differences in connection properties between SQLOLEDB and
MSDASQL. For information about connection properties for MSDASQL, see the
MSDN Library at Microsoft Web site.
If you are writing a connection string for use with SQLOLEDB:
Use the Initial Catalog property to specify the database.
Use the Data Source property to specify the server name.
Use the Integrated Security keyword, set to a value of SSPI, to specify
Windows Authentication (recommended),
or
use the User ID and Password connection properties to specify SQL Server
Authentication.
Security Note When possible, use Windows Authentication. If Windows
Authentication is not available, prompt users to enter their credentials at
run time. Avoid storing credentials in a file. If you must persist
credentials, you should encrypt them with the Win32 crypto API. For more
information, see "The Crypto API Function" in the MSDN Library at this
Microsoft Web site.
If you are writing a connection string for use with MSDASQL:
Use the Database keyword or Initial Catalog property to specify the
database.
Use the Server keyword or Data Source property to specify the server name.
Use the Trusted_Connection keyword, set to a value of yes, to specify
Windows Authentication (recommended),
or
Use the UID keyword or User ID property, and the Pwd keyword or Password
property to specify SQL Server Authentication.
Security Note When possible, use Windows Authentication. If Windows
Authentication is not available, prompt users to enter their credentials at
run time. Avoid storing credentials in a file. If you must persist
credentials, you should encrypt them with the Win32 crypto API. For more
information, see "The Crypto API Function" in the MSDN Library at this
Microsoft Web site.
For more information about a complete list of keywords available for use
with a SQLOLEDB connection string, see Connection Object.
Restrictions on Multiple Connections
SQLOLEDB does not allow multiple connections. Unlike MSDASQL, SQLOLEDB does
not attempt to reconnect when the connection is blocked.
Examples
A. Using SQLOLEDB to connect to an instance of SQL Server: setting
individual properties
The following Microsoft Visual Basic code fragments from the ADO
Introductory Visual Basic Sample show how to use SQLOLEDB to connect to an
instance of SQL Server.
' Initialize variables.
Dim cn As New ADODB.Connection
. . .
Dim ServerName As String, DatabaseName As String
' Put text box values into connection variables.
ServerName = txtServerName.Text
DatabaseName = txtDatabaseName.Text
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
' Set SQLOLEDB connection properties.
cn.Properties("Data Source").Value = ServerName
cn.Properties("Initial Catalog").Value = DatabaseName
' Windows NT authentication.
cn.Properties("Integrated Security").Value = "SSPI"
' Open the database.
cn.Open
B. Using SQLOLEDB to connect to an instance of SQL Server: connection
string method
The following Visual Basic code fragment shows how to use SQLOLEDB to
connect to an instance or SQL Server:
' Initialize variables.
Dim cn As New ADODB.Connection
Dim provStr As String
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
' Specify connection string on Open method.
ProvStr = " Server=MyServer;Database=northwind;Trust
ed_Connection=yes"
cn.Open provStr
C. Using MSDASQL to connect to an instance of SQL Server
To use MSDASQL to connect to an instance of SQL Server, use the following
types of connections.
The first type of connection is based on the ODBC API SQLConnect function.
This type of connection is useful in situations where you do not want to
code specific information about the data source. This may be the case if
the data source could change or if you do not know its particulars.
In the code fragment shown, the ConnectionTimeout method sets the
connection time-out value to 100 seconds. Next, the data source name, and
authentication type are passed as parameters to the Open method of the
Connection object, using an ODBC data source named MyDataSource that points
to the northwind database on an instance of SQL Server.
Dim cn As New ADODB.Connection
cn.ConnectionTimeout = 100
' DSN connection
' cn.Open " DSN=MyDataSource;Trusted_Connection=yes;
"
cn.Close
The second type of connection is based on the ODBC API SQLDriverConnect
function. This type of connection is useful in situations where you want a
driver-specific connection string. To make a connection, use the Open
method of the Connection object and specify the driver, server name,
authentication type, and database. You can also specify any other valid
keywords to include in the connection string. For more information about
the keyword list, see SQLDriverConnect.
Dim cn As New ADODB.Connection
' Connection to SQL Server without using ODBC data source.
cn.Open "Driver={SQL
Server};Server=Server1;Database=northwin
d;Trusted_Connection=yes"
cn.Close
Using the Connection Object
In addition to the Command object, an application can use the Connection
object to issue commands, stored procedures, and user-defined functions to
a database as if they were native methods on the Connection object. To
execute a query without using a Command object, an application can pass a
query string to the Execute method of a Connection object.
However, a Command object is required if you want to save and re-execute
the command text, or use query parameters.
To execute a command on the Connection object
Assign a name to the command using the Name property of the Command object.
Set the ActiveConnection property of the Command object to the connection.
Issue a statement where the command name is used as if it were a method on
the Connection object, followed by any parameters.
Create a Recordset object if any rows are returned.
Set the Recordset properties to customize the resulting Recordset.
Using the Connection Object to Execute Commands
This example shows how to use the Execute method of the Connection object
to execute commands.
Dim cn As New ADODB.Connection
. . .
Dim rs As New ADODB.Recordset
cmd1 = txtQuery.Text
Set rs = cn.Execute(cmd1)
After the Connection and Recordset objects are created, the variable cmd1
is assigned the value of a user-supplied query string (txtQuery.Text) from
a Microsoft Visual Basic form. The recordset is assigned the results of a
query, by calling the Execute method of the Connection object, with the
variable cmd1 used as the query string parameter.
Using the Recordset Object
The Recordset object provides methods for manipulating result sets. It
allows you to add, update, delete, and scroll through rows in the recordset.
A Recordset object can be created using the Execute method of the
Connection or Command object.
Each row in a recordset can also be retrieved and updated using the Fields
collection and the Field object. Updates on the Recordset object can be in
an immediate or batch mode. When a Recordset object is created, a cursor is
opened automatically.
The Recordset object allows you to specify the cursor type and location for
fetching the result set. With the CursorType property, you can specify
whether the cursor is read-only, forward-only, static, keyset-driven, or
dynamic. Cursor type determines if a Recordset object can be scrolled or
updated and affects the visibility of changed rows. By default, the cursor
type is read-only and forward-only.
An application can specify the location of the cursor with the
CursorLocation property. This property allows you to specify whether to use
a client or server cursor. The CursorLocation property setting is important
when you use disconnected recordsets.
The first part of the cmdExecute_Click method in the ADO Introductory
Visual Basic Sample shows an example of creating, opening, passing a
command string variable to, and positioning the cursor in a recordset.
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
. . .
cmd1 = txtQuery.Text
Set rs = New ADODB.Recordset
rs.Open cmd1, cn
rs.MoveFirst
. . .
' Code to loop through result set(s)
Thanks and regards,
Girish Sundaram
This posting is provided "AS IS" with no warranties, and confers no rights.|||thank you for now
I will try
Probably I will turn back to you with some questions
"Girish Sundaram" wrote:

> Hi ,
> Please go through the following piece of information which will be useful
> in your case.
> ADO can use any OLE DB provider to establish a connection. The provider is
> specified through the Provider property of the Connection object.
> Microsoft? SQL Server? 2000 applications use SQLOLEDB to connect to an
> instance of SQL Server, although existing applications can also use MSDASQ
L
> to maintain backward compatibility.
> Using the Execute method of the Connection object is one way to execute an
> SQL statement against a SQL Server data source.
> The Connection object allows you to:
> Configure a connection.
>
> Establish and terminate sessions with data sources.
>
> Identify an OLE DB provider.
>
> Execute a query.
>
> Manage transactions on the open connection.
>
> Choose a cursor library available to the data provider.
> There are some differences in connection properties between SQLOLEDB and
> MSDASQL. For information about connection properties for MSDASQL, see the
> MSDN Library at Microsoft Web site.
> If you are writing a connection string for use with SQLOLEDB:
> Use the Initial Catalog property to specify the database.
>
> Use the Data Source property to specify the server name.
>
> Use the Integrated Security keyword, set to a value of SSPI, to specify
> Windows Authentication (recommended),
> or
> use the User ID and Password connection properties to specify SQL Server
> Authentication.
>
> Security Note When possible, use Windows Authentication. If Windows
> Authentication is not available, prompt users to enter their credentials a
t
> run time. Avoid storing credentials in a file. If you must persist
> credentials, you should encrypt them with the Win32? crypto API. For more
> information, see "The Crypto API Function" in the MSDN? Library at this
> Microsoft Web site.
> If you are writing a connection string for use with MSDASQL:
> Use the Database keyword or Initial Catalog property to specify the
> database.
>
> Use the Server keyword or Data Source property to specify the server name.
>
> Use the Trusted_Connection keyword, set to a value of yes, to specify
> Windows Authentication (recommended),
> or
> Use the UID keyword or User ID property, and the Pwd keyword or Password
> property to specify SQL Server Authentication.
>
> Security Note When possible, use Windows Authentication. If Windows
> Authentication is not available, prompt users to enter their credentials a
t
> run time. Avoid storing credentials in a file. If you must persist
> credentials, you should encrypt them with the Win32 crypto API. For more
> information, see "The Crypto API Function" in the MSDN Library at this
> Microsoft Web site.
> For more information about a complete list of keywords available for use
> with a SQLOLEDB connection string, see Connection Object.
> Restrictions on Multiple Connections
> SQLOLEDB does not allow multiple connections. Unlike MSDASQL, SQLOLEDB doe
s
> not attempt to reconnect when the connection is blocked.
> Examples
> A. Using SQLOLEDB to connect to an instance of SQL Server: setting
> individual properties
> The following Microsoft Visual Basic? code fragments from the ADO
> Introductory Visual Basic Sample show how to use SQLOLEDB to connect to an
> instance of SQL Server.
> ' Initialize variables.
> Dim cn As New ADODB.Connection
> . . .
> Dim ServerName As String, DatabaseName As String
> ' Put text box values into connection variables.
> ServerName = txtServerName.Text
> DatabaseName = txtDatabaseName.Text
> ' Specify the OLE DB provider.
> cn.Provider = "sqloledb"
> ' Set SQLOLEDB connection properties.
> cn.Properties("Data Source").Value = ServerName
> cn.Properties("Initial Catalog").Value = DatabaseName
> ' Windows NT authentication.
> cn.Properties("Integrated Security").Value = "SSPI"
> ' Open the database.
> cn.Open
> B. Using SQLOLEDB to connect to an instance of SQL Server: connection
> string method
> The following Visual Basic code fragment shows how to use SQLOLEDB to
> connect to an instance or SQL Server:
> ' Initialize variables.
> Dim cn As New ADODB.Connection
> Dim provStr As String
> ' Specify the OLE DB provider.
> cn.Provider = "sqloledb"
> ' Specify connection string on Open method.
> ProvStr = " Server=MyServer;Database=northwind;Trust
ed_Connection=yes"
> cn.Open provStr
> C. Using MSDASQL to connect to an instance of SQL Server
> To use MSDASQL to connect to an instance of SQL Server, use the following
> types of connections.
> The first type of connection is based on the ODBC API SQLConnect function.
> This type of connection is useful in situations where you do not want to
> code specific information about the data source. This may be the case if
> the data source could change or if you do not know its particulars.
> In the code fragment shown, the ConnectionTimeout method sets the
> connection time-out value to 100 seconds. Next, the data source name, and
> authentication type are passed as parameters to the Open method of the
> Connection object, using an ODBC data source named MyDataSource that point
s
> to the northwind database on an instance of SQL Server.
> Dim cn As New ADODB.Connection
> cn.ConnectionTimeout = 100
> ' DSN connection
> ' cn.Open " DSN=MyDataSource;Trusted_Connection=yes;
"
> cn.Close
> The second type of connection is based on the ODBC API SQLDriverConnect
> function. This type of connection is useful in situations where you want a
> driver-specific connection string. To make a connection, use the Open
> method of the Connection object and specify the driver, server name,
> authentication type, and database. You can also specify any other valid
> keywords to include in the connection string. For more information about
> the keyword list, see SQLDriverConnect.
> Dim cn As New ADODB.Connection
> ' Connection to SQL Server without using ODBC data source.
> cn.Open "Driver={SQL
> Server};Server=Server1;Database=northwin
d;Trusted_Connection=yes"
> cn.Close
> Using the Connection Object
> In addition to the Command object, an application can use the Connection
> object to issue commands, stored procedures, and user-defined functions to
> a database as if they were native methods on the Connection object. To
> execute a query without using a Command object, an application can pass a
> query string to the Execute method of a Connection object.
> However, a Command object is required if you want to save and re-execute
> the command text, or use query parameters.
> To execute a command on the Connection object
> Assign a name to the command using the Name property of the Command object
.
>
> Set the ActiveConnection property of the Command object to the connection.
>
> Issue a statement where the command name is used as if it were a method on
> the Connection object, followed by any parameters.
>
> Create a Recordset object if any rows are returned.
>
> Set the Recordset properties to customize the resulting Recordset.
> Using the Connection Object to Execute Commands
> This example shows how to use the Execute method of the Connection object
> to execute commands.
> Dim cn As New ADODB.Connection
> . . .
> Dim rs As New ADODB.Recordset
> cmd1 = txtQuery.Text
> Set rs = cn.Execute(cmd1)
> After the Connection and Recordset objects are created, the variable cmd1
> is assigned the value of a user-supplied query string (txtQuery.Text) from
> a Microsoft Visual Basic? form. The recordset is assigned the results of
a
> query, by calling the Execute method of the Connection object, with the
> variable cmd1 used as the query string parameter.
> Using the Recordset Object
> The Recordset object provides methods for manipulating result sets. It
> allows you to add, update, delete, and scroll through rows in the recordse
t.
> A Recordset object can be created using the Execute method of the
> Connection or Command object.
> Each row in a recordset can also be retrieved and updated using the Fields
> collection and the Field object. Updates on the Recordset object can be in
> an immediate or batch mode. When a Recordset object is created, a cursor i
s
> opened automatically.
> The Recordset object allows you to specify the cursor type and location fo
r
> fetching the result set. With the CursorType property, you can specify
> whether the cursor is read-only, forward-only, static, keyset-driven, or
> dynamic. Cursor type determines if a Recordset object can be scrolled or
> updated and affects the visibility of changed rows. By default, the cursor
> type is read-only and forward-only.
> An application can specify the location of the cursor with the
> CursorLocation property. This property allows you to specify whether to us
e
> a client or server cursor. The CursorLocation property setting is importan
t
> when you use disconnected recordsets.
> The first part of the cmdExecute_Click method in the ADO Introductory
> Visual Basic Sample shows an example of creating, opening, passing a
> command string variable to, and positioning the cursor in a recordset.
> Dim cn As New ADODB.Connection
> Dim rs As ADODB.Recordset
> . . .
> cmd1 = txtQuery.Text
> Set rs = New ADODB.Recordset
> rs.Open cmd1, cn
> rs.MoveFirst
> . . .
> ' Code to loop through result set(s)
> Thanks and regards,
> Girish Sundaram
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Ahmet, you could also look at www.asp.net. They have plenty of examples for
you on that site. You should be able to cut, paste, and modify.
"Ahmet Karaca" <AhmetKaraca@.discussions.microsoft.com> wrote in message
news:D99CE06D-E875-4084-919F-333C7840B6DC@.microsoft.com...[vbcol=seagreen]
> thank you for now
> I will try
> Probably I will turn back to you with some questions
> "Girish Sundaram" wrote:
>
useful[vbcol=seagreen]
is[vbcol=seagreen]
MSDASQL[vbcol=seagreen]
an[vbcol=seagreen]
the[vbcol=seagreen]
at[vbcol=seagreen]
more[vbcol=seagreen]
name.[vbcol=seagreen]
at[vbcol=seagreen]
does[vbcol=seagreen]
an[vbcol=seagreen]
following[vbcol=seagreen]
function.[vbcol=seagreen]
and[vbcol=seagreen]
points[vbcol=seagreen]
a[vbcol=seagreen]
to[vbcol=seagreen]
a[vbcol=seagreen]
object.[vbcol=seagreen]
connection.[vbcol=seagreen]
on[vbcol=seagreen]
object[vbcol=seagreen]
cmd1[vbcol=seagreen]
from[vbcol=seagreen]
a[vbcol=seagreen]
recordset.[vbcol=seagreen]
Fields[vbcol=seagreen]
in[vbcol=seagreen]
is[vbcol=seagreen]
for[vbcol=seagreen]
cursor[vbcol=seagreen]
use[vbcol=seagreen]
important[vbcol=seagreen]
rights.[vbcol=seagreen]