Connecting to an SQL Server

[simonm]

My attempt to conenct to an SQL database when I click on a 'submit' button on my ASPX page fails with the following error: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

Here is my ASP code:

<script runat="server">
	Sub submit(sender As Object, e As EventArgs)
		dim dbconn
		dbconn=New OleDbConnection("Provider=SQLOLEDB.1;Data Source=ex1;Initial Catalog=TOPS;User ID=sa;Password=XXXX")
		dbconn.Open()
	End Sub
</script>

The SQL server is on another machine in the network (not the machine running IIS) and I know for a fact that the connection string and login details are correct because they work fine if I open a connection from anywhere else. It is only from within my ASPX page that it fails...

[dj4uk]

You should be using SqlConnection, SqlCommand etc. (all part of System.Data.SqlClient) namespace rather than OleDb.

Your connection string can be something like:
"server=ServerName;uid=sa;pwd=XXXXX;database=DatabaseName"

Where ServerName is the host name or IP address of the server the SQL Server is hosted on.

HTH

DJ

[simonm]

Thanks, but I get the same error...

[dj4uk]

Have you tried pinging the SQL Server machine from the machine with IIS to ensure they can connect to each other?

DJ

[simonm]

Yes, the machine can connect to the SQL server database with the above connection string in any other place other than within IIS.

Incidentally, is there any real reason why I should use SqlClient rather OleDb? Is it more efficient for SQL Server databases or something?

[dj4uk]

OleDb will work but as the SqlClient namespace was specifically written for SQL Server it gives better performance etc. I would definately recommend using it.

If the machines can physically connect there must be something wrong in the connection string or the uid and password ain't right or the SQL Server is turned off

DJ

[simonm]

I have a test VB.NET application sitting on the IIS machine that does nothing but open a connection object in an identical way with an identical connection string and it works fine.

Therefore, it must be something to do with ASP.NET or IIS...