Solved

SQLConnection with C# in ASP.net

Posted on 2006-06-08
4
17,403 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
ID: 16867418
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
ID: 16867466
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
ID: 16871649
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
ID: 16898904
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 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

20 Experts available now in Live!

Get 1:1 Help Now