• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17471
  • Last Modified:

SQLConnection with C# in ASP.net

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
josephbarron
Asked:
josephbarron
  • 2
1 Solution
 
GavinMannionCommented:
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
 
Sammy AgeilCommented:
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
 
josephbarronAuthor Commented:
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
 
josephbarronAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now