connecting to SQL db in an asp.net page

Ok, I'm an experienced asp developer switching to asp.net.

I've used the "Quickstart Tutorials" (http://www.asp.net/Tools/redir.aspx?path=quickstart) and have gotten started that way and built a few things based on that, but they only show XML connection info. Now I need to switch to SQL so I can connect to MS SQL Server for some stored procedures.

Anyone have suggestions?

(I've dug through some articles on msdn, etc, but I've only found so far theory and not code. And let me tell 'ya, I'm big on learning about code from code.)

If this helps, the specific tasks I'm trying to do are:
Have user select a static value from a drop down list, enter a word in a text box, and submit the form (so far so good), which connects to the db and returns the datagrid filled with results (the part I don't have). I already have my sp, just can't connect to it and get results back into the page.The Tutorials on this site have an example of doing just that. Check outPerforming a Parameterized Select

Sound like you have all of the pieces you need, you just need to stitch them together. Viewing the source code of that example should help you do just that. Post back if you are having trouble implementing it.

Terri|||I receive this error:

Server Error in '/Test1' Application.
------------------------

The resource cannot be found.
Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly.

Requested Url: /Test1/VB/datagrid2.aspx|||The problem is probably with the SqlConnection -- specifically, it is trying to read the connection string out of the web.config file. This is a good practice. But, let's try to get the sample running for you first.

Instead of this line:

MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("PubsString"))

Try this line (assuming you are using a local SQL Server with Windows authentication)

MyConnection = New SqlClient.SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")

Terri|||Ok, I'm looking at these various example ways to set up and connect to dbs.

I have a local sql server running on my machine.
I can use the sa account.
I have databases, sps, and tables already on it.

I've tried looking at the examples and changing to fit, but either I'm missing something or I've put info in the wrong place.

hum.|||YAY YAY YAY!!!

Thank you! I changed your line:
MyConnection = New SqlClient.SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")

To:
MyConnection = New SqlClient.SqlConnection("server=(local);database=mydbname;uid=sa;pwd=sa;")

and it works in the datagrid2.aspx sample file! 8)

Whew!

Ok, now time to get this working in my files...

Thanks again!

Connecting to remote SQL Server

I have SQL Server 2000 (Developer) on my computer. So I have SQL
Server Enterprise Manager + Tools (such as Query Analyzer).
I am doing some work on a remote server, mostly ASP pages (not
ASP.NET). There is a SQL Server database on that server. I would like
to use Enterprise Manager or Query Analyzer to access that database.
My main development environment is Dreamweaver MX2004. In Dreamweaver,
I was able to setup the database (on the databases tab) using a
"Custom Connection String", listed below (some info obfuscated for
security):
"Provider=SQLOLEDB.1;Persist Security Info=False;Initial
Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
I don't know if it is possible to use the other tools to manage this
database. I've tried the following:
1. In Enterprise Manager, I've tried just putting the ip address as
the server, and "Use SQL Server authentication" from the user ID and
Password above, but I get the following error:
"SQL Server registration failed because of the connection failure
below... Cannot open user default database. Login failed"
2. Created a udl file. In the Provider, selected the "Microsoft OLE DB
Provider for SQL Server" Entered teh IP address and username/password
on the Connection Tab. I get the same error as #1 above.
Any insight would be greatly appreciated.
"Bryce Fischer" <spamtrap@.berzerker-soft.com> wrote in message
news:3btpo01us397pf1gmfl4bt9c8k9dcrv05e@.4ax.com...
> I have SQL Server 2000 (Developer) on my computer. So I have SQL
> Server Enterprise Manager + Tools (such as Query Analyzer).
> I am doing some work on a remote server, mostly ASP pages (not
> ASP.NET). There is a SQL Server database on that server. I would like
> to use Enterprise Manager or Query Analyzer to access that database.
> My main development environment is Dreamweaver MX2004. In Dreamweaver,
> I was able to setup the database (on the databases tab) using a
> "Custom Connection String", listed below (some info obfuscated for
> security):
> "Provider=SQLOLEDB.1;Persist Security Info=False;Initial
> Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
> Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
> I don't know if it is possible to use the other tools to manage this
> database. I've tried the following:
> 1. In Enterprise Manager, I've tried just putting the ip address as
> the server, and "Use SQL Server authentication" from the user ID and
> Password above, but I get the following error:
> "SQL Server registration failed because of the connection failure
> below... Cannot open user default database. Login failed"
>
User XXX has a login on the remote server, but does not have a user in the
database he is configured to use. To administer the remote database, User
XXX must be a member of the sysadmin role on the remote server
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004
|||On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:

>User XXX has a login on the remote server, but does not have a user in the
>database he is configured to use. To administer the remote database, User
>XXX must be a member of the sysadmin role on the remote server
Thanks for the reply.
Is it possible to use Query Analyzer or Access to connect to the
server given the information in my original post? I don't need
administrator-like functionality. As I mentioned, in Dreamweaver, I'm
able to create a connection, and view the tables and data (not to be
confused with creating the connection on the ASP pages, but in
Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
it doesn't let you run SQL Statements, which is why I'd like to use
other tools I have.
TIA.
|||On Sat, 06 Nov 2004 20:21:58 -0500, Bryce Fischer
<spamtrap@.berzerker-soft.com> wrote:

>On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
><bob_simms@.somewhere.com> wrote:
>
>Thanks for the reply.
>Is it possible to use Query Analyzer or Access to connect to the
>server given the information in my original post? I don't need
>administrator-like functionality. As I mentioned, in Dreamweaver, I'm
>able to create a connection, and view the tables and data (not to be
>confused with creating the connection on the ASP pages, but in
>Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
>it doesn't let you run SQL Statements, which is why I'd like to use
>other tools I have.
Well... After some persistence, I figured it out. In Access, and
Visual Studio, I was able to bring up the Data Link Properties dialog,
and enter the information, and the one step I was not doing, was
entering in the catalog in the "Select the database on the server:"
field.
I was able to accomplish the same thing in Visual Studio, Server
Explorer, Data Connections.

Connecting to remote SQL Server

I have SQL Server 2000 (Developer) on my computer. So I have SQL
Server Enterprise Manager + Tools (such as Query Analyzer).
I am doing some work on a remote server, mostly ASP pages (not
ASP.NET). There is a SQL Server database on that server. I would like
to use Enterprise Manager or Query Analyzer to access that database.
My main development environment is Dreamweaver MX2004. In Dreamweaver,
I was able to setup the database (on the databases tab) using a
"Custom Connection String", listed below (some info obfuscated for
security):
"Provider=SQLOLEDB.1;Persist Security Info=False;Initial
Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
I don't know if it is possible to use the other tools to manage this
database. I've tried the following:
1. In Enterprise Manager, I've tried just putting the ip address as
the server, and "Use SQL Server authentication" from the user ID and
Password above, but I get the following error:
"SQL Server registration failed because of the connection failure
below... Cannot open user default database. Login failed"
2. Created a udl file. In the Provider, selected the "Microsoft OLE DB
Provider for SQL Server" Entered teh IP address and username/password
on the Connection Tab. I get the same error as #1 above.
Any insight would be greatly appreciated."Bryce Fischer" <spamtrap@.berzerker-soft.com> wrote in message
news:3btpo01us397pf1gmfl4bt9c8k9dcrv05e@.
4ax.com...
> I have SQL Server 2000 (Developer) on my computer. So I have SQL
> Server Enterprise Manager + Tools (such as Query Analyzer).
> I am doing some work on a remote server, mostly ASP pages (not
> ASP.NET). There is a SQL Server database on that server. I would like
> to use Enterprise Manager or Query Analyzer to access that database.
> My main development environment is Dreamweaver MX2004. In Dreamweaver,
> I was able to setup the database (on the databases tab) using a
> "Custom Connection String", listed below (some info obfuscated for
> security):
> "Provider=SQLOLEDB.1;Persist Security Info=False;Initial
> Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
> Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
> I don't know if it is possible to use the other tools to manage this
> database. I've tried the following:
> 1. In Enterprise Manager, I've tried just putting the ip address as
> the server, and "Use SQL Server authentication" from the user ID and
> Password above, but I get the following error:
> "SQL Server registration failed because of the connection failure
> below... Cannot open user default database. Login failed"
>
User XXX has a login on the remote server, but does not have a user in the
database he is configured to use. To administer the remote database, User
XXX must be a member of the sysadmin role on the remote server
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004|||On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:

>User XXX has a login on the remote server, but does not have a user in the
>database he is configured to use. To administer the remote database, User
>XXX must be a member of the sysadmin role on the remote server
Thanks for the reply.
Is it possible to use Query Analyzer or Access to connect to the
server given the information in my original post? I don't need
administrator-like functionality. As I mentioned, in Dreamweaver, I'm
able to create a connection, and view the tables and data (not to be
confused with creating the connection on the ASP pages, but in
Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
it doesn't let you run SQL Statements, which is why I'd like to use
other tools I have.
TIA.|||On Sat, 06 Nov 2004 20:21:58 -0500, Bryce Fischer
<spamtrap@.berzerker-soft.com> wrote:

>On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
><bob_simms@.somewhere.com> wrote:
>
>Thanks for the reply.
>Is it possible to use Query Analyzer or Access to connect to the
>server given the information in my original post? I don't need
>administrator-like functionality. As I mentioned, in Dreamweaver, I'm
>able to create a connection, and view the tables and data (not to be
>confused with creating the connection on the ASP pages, but in
>Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
>it doesn't let you run SQL Statements, which is why I'd like to use
>other tools I have.
Well... After some persistence, I figured it out. In Access, and
Visual Studio, I was able to bring up the Data Link Properties dialog,
and enter the information, and the one step I was not doing, was
entering in the catalog in the "Select the database on the server:"
field.
I was able to accomplish the same thing in Visual Studio, Server
Explorer, Data Connections.

Connecting to remote SQL Server

I have SQL Server 2000 (Developer) on my computer. So I have SQL
Server Enterprise Manager + Tools (such as Query Analyzer).
I am doing some work on a remote server, mostly ASP pages (not
ASP.NET). There is a SQL Server database on that server. I would like
to use Enterprise Manager or Query Analyzer to access that database.
My main development environment is Dreamweaver MX2004. In Dreamweaver,
I was able to setup the database (on the databases tab) using a
"Custom Connection String", listed below (some info obfuscated for
security):
"Provider=SQLOLEDB.1;Persist Security Info=False;Initial
Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
I don't know if it is possible to use the other tools to manage this
database. I've tried the following:
1. In Enterprise Manager, I've tried just putting the ip address as
the server, and "Use SQL Server authentication" from the user ID and
Password above, but I get the following error:
"SQL Server registration failed because of the connection failure
below... Cannot open user default database. Login failed"
2. Created a udl file. In the Provider, selected the "Microsoft OLE DB
Provider for SQL Server" Entered teh IP address and username/password
on the Connection Tab. I get the same error as #1 above.
Any insight would be greatly appreciated."Bryce Fischer" <spamtrap@.berzerker-soft.com> wrote in message
news:3btpo01us397pf1gmfl4bt9c8k9dcrv05e@.4ax.com...
> I have SQL Server 2000 (Developer) on my computer. So I have SQL
> Server Enterprise Manager + Tools (such as Query Analyzer).
> I am doing some work on a remote server, mostly ASP pages (not
> ASP.NET). There is a SQL Server database on that server. I would like
> to use Enterprise Manager or Query Analyzer to access that database.
> My main development environment is Dreamweaver MX2004. In Dreamweaver,
> I was able to setup the database (on the databases tab) using a
> "Custom Connection String", listed below (some info obfuscated for
> security):
> "Provider=SQLOLEDB.1;Persist Security Info=False;Initial
> Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
> Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
> I don't know if it is possible to use the other tools to manage this
> database. I've tried the following:
> 1. In Enterprise Manager, I've tried just putting the ip address as
> the server, and "Use SQL Server authentication" from the user ID and
> Password above, but I get the following error:
> "SQL Server registration failed because of the connection failure
> below... Cannot open user default database. Login failed"
>
User XXX has a login on the remote server, but does not have a user in the
database he is configured to use. To administer the remote database, User
XXX must be a member of the sysadmin role on the remote server
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004|||On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:
>> 1. In Enterprise Manager, I've tried just putting the ip address as
>> the server, and "Use SQL Server authentication" from the user ID and
>> Password above, but I get the following error:
>> "SQL Server registration failed because of the connection failure
>> below... Cannot open user default database. Login failed"
>User XXX has a login on the remote server, but does not have a user in the
>database he is configured to use. To administer the remote database, User
>XXX must be a member of the sysadmin role on the remote server
Thanks for the reply.
Is it possible to use Query Analyzer or Access to connect to the
server given the information in my original post? I don't need
administrator-like functionality. As I mentioned, in Dreamweaver, I'm
able to create a connection, and view the tables and data (not to be
confused with creating the connection on the ASP pages, but in
Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
it doesn't let you run SQL Statements, which is why I'd like to use
other tools I have.
TIA.|||On Sat, 06 Nov 2004 20:21:58 -0500, Bryce Fischer
<spamtrap@.berzerker-soft.com> wrote:
>On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
><bob_simms@.somewhere.com> wrote:
>> 1. In Enterprise Manager, I've tried just putting the ip address as
>> the server, and "Use SQL Server authentication" from the user ID and
>> Password above, but I get the following error:
>> "SQL Server registration failed because of the connection failure
>> below... Cannot open user default database. Login failed"
>>User XXX has a login on the remote server, but does not have a user in the
>>database he is configured to use. To administer the remote database, User
>>XXX must be a member of the sysadmin role on the remote server
>Thanks for the reply.
>Is it possible to use Query Analyzer or Access to connect to the
>server given the information in my original post? I don't need
>administrator-like functionality. As I mentioned, in Dreamweaver, I'm
>able to create a connection, and view the tables and data (not to be
>confused with creating the connection on the ASP pages, but in
>Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
>it doesn't let you run SQL Statements, which is why I'd like to use
>other tools I have.
Well... After some persistence, I figured it out. In Access, and
Visual Studio, I was able to bring up the Data Link Properties dialog,
and enter the information, and the one step I was not doing, was
entering in the catalog in the "Select the database on the server:"
field.
I was able to accomplish the same thing in Visual Studio, Server
Explorer, Data Connections.

Connecting to Network Server

Hi All,
I have 2 computers, one has SQL2000 and the other has MSDE2000. I have the
SQL tools installed on both computers.
When I use the enterprise manager and try to connect remote server I cannot
get the user to authenticate. It is trying to access the remote server
using the guest account.
There is no domain. Both computer have mixed mode authentication, and are
on the same 192.168.1.x network range.
What am I missing here.
Thanks
Lee
hi Lee,
"Lee Trotter" <latrotter@.@.@.sympatico.ca> ha scritto nel messaggio
news:%23lstCrAaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All,
> I have 2 computers, one has SQL2000 and the other has MSDE2000. I have
the
> SQL tools installed on both computers.
> When I use the enterprise manager and try to connect remote server I
cannot
> get the user to authenticate. It is trying to access the remote server
> using the guest account.
> There is no domain. Both computer have mixed mode authentication, and are
> on the same 192.168.1.x network range.
I did not undertand if you, from both machines, can not connect remotely or
you can not remotely connect just to the MSDE instance...
if this is the case, MSDE installs by default disabling network protocols,
in order to prevent external malicius attacks, so that only local connection
using Shared Memory are allowed..
you can modify this behaviour bot at install time, specifying the
DISABLENETWORKPROTOCOLS=0 parameter to the setup.exe boostrat MSDE
installer, and later, using Server Network Utility (svrnetcn.exe), enabling
the desired network protocol(s)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Connecting Server Management Studio to a database on remote server

To use the front end tools in SQL Manager on the database at my ISP I want to connect directly from Server Management Studio ( Or Server explorer i VS). I have enabled TCP/IP without succes.

My installation is based on SQL Server Developer Edition and VS 2005. With my previos installation based on Express versions of the product this worked fine.

In VS 2005 I select Tools -> Connect to database and fill inn the dialog window with the serveraddress (sql01.active24.com), username and password. I then get the following message:

An errror has occurd while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the fefault settings SQL Server does not allow remote connections. (provider: Named pipes, errror: 40 - Could not opeen a connection to SQL Server

In the network configuration I have now disabled Named Pipes and enabled TCP/IP.

Thanks in advance!

---

Andreas

Report Abuse

Open the Surface Area Configuration (SAC) tool, and enable remote connections. By default the developer edition has all that turned off.

Buck Woody

Connecting Server Management Studio to a database on remote server

To use the front end tools in SQL Manager on the database at my ISP I want to connect directly from Server Management Studio ( Or Server explorer i VS). I have enabled TCP/IP without succes.

My installation is based on SQL Server Developer Edition and VS 2005. With my previos installation based on Express versions of the product this worked fine.

In VS 2005 I select Tools -> Connect to database and fill inn the dialog window with the serveraddress (sql01.active24.com), username and password. I then get the following message:

An errror has occurd while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the fefault settings SQL Server does not allow remote connections. (provider: Named pipes, errror: 40 - Could not opeen a connection to SQL Server

In the network configuration I have now disabled Named Pipes and enabled TCP/IP.

Thanks in advance!

---

Andreas

Report Abuse

Open the Surface Area Configuration (SAC) tool, and enable remote connections. By default the developer edition has all that turned off.

Buck Woody