Solved

SQLConnection with C# in ASP.net

Posted on 2006-06-08
4
17,392 Views
Last Modified: 2008-01-16
I'm having some problems connecting to my datasource within a C# script in ASP.net.

I have the following commands:

SqlConnection conn = new SqlConnection("Data Source=localhost; database=mydatabase; uid=sa; pwd=thepassword; connection timeout = 10");
conn.Open();

It gives an error saying the connection could not be made. Warning that the server might not allow remote connections.

My questions are these:
1) Can I use ODBC to connect?
2) I'm running SQL Server 2000 (that's where the database is), but the machine had the SQL2005Express installed before I installed SQL 2000.
3) On a C# Windows Form, I've been able to add a sqlconnection in from the toolbox, configure it and get it to work. But this is NOT availble in my toolbox when writing web pages. Is this true?
4) How do I get my syntax to work?

THanks
0
Comment
Question by:josephbarron
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
GavinMannion earned 250 total points
Comment Utility
You can use a drag and drop SqlDataSource that will help you setup this connection.

SQL 2000 and SQLExpress can work together fine, just make sure you are pointing to the correct databse obviously.

You syntax seems fine, and since you say you can connect to the db through a windows form then I can only presume either the SQL server is not allowing mixed authentication. (The windows form used windows authentication while the we form is using sql auth), or you used a different method of connection.

Try the DataSource first and see how you get on.
0
 
LVL 27

Expert Comment

by:Sammy
Comment Utility
in vs.net go to server explorer, right click on data connection, click on add connection, in the add connection dialog click on change, choose microsoft sql server, click  Ok, type in your server name and select your database.
now drag the sqldatasource on your webform and configure it to use the connection you just created

hth
0
 
LVL 1

Author Comment

by:josephbarron
Comment Utility
I'm trying to use SqlCommand feature to do some simple SQL Selects, Inserts, Updates and Deletes. E.g.

For instance, I want to get the identityvalue (which is an identity field PK in my user table) where the loginname is equal to something. Login name is a unique field, so the SELECT will return only one row. I've been able to run SqlCommand with ExecuteScalar to return a single value. I then use this to get some other information or to make a decision, etc.

I've been able to do this on a Windows Form, because I could define the sqlConnection using the Toolbox. But that is apparently not available on the Web Page and my conn.open() is failing, which is what was happening on the Windows form when I tried to define my SqlConnection within the script. When I define my SqlConnection using the toolbox it generates the following ConnectionString:

"Data Source=myservername;Initial Catalog=mydatabase;Integrated Security=True;TrustServerCertificate=True"


Here is my code that works on Windows Form, using the tool box defined sqlconnection above.

string sqlString ;
long identitykey ;

SqlConnection conn = new SqlConnection("Data Source=localhost; database=mydatabase; uid=sa; pwd=thepassword; connection timeout = 10");
conn.Open();
sqlString = "select identityvalue from users where loginname = joseph.barron";
SqlCommand sqlcmd = new SqlCommand(sqlString, conn);
identitykey = Convert.ToUInt32(sqlcmd.ExecuteScalar());

Now my questions are:
   1) Can I define a SqlConnection using the toolbox when working on Web pages?
   2) Can I use sqldatasource with the SqlCommand syntax above?

Sorry for the long convoluted questions, but I'm so use to PowerBuilder and the ability to use a built in global ODBC connections or ColdFusion, where I simply define the datasource on my cold fusion server and then include in a cfquery statement. This syntax and connection issues are really holding my development up. Seems simple enough, but is not working. I have the feeling that Microsoft is pushing me towards something like ADO.net, but I don't know how to use this wth the syntax above either. Thanks for your help.

Regards,
0
 
LVL 1

Author Comment

by:josephbarron
Comment Utility
The sqlConnection is under View Component Designer. I was not seeing it in the ToolBox when I was NOT in Component Designer. I was able to setup a SQLconnection properly and get things working.

I tried the ODBCConnection, but that had problems with working with stored procedures.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now