SQL server connection Problem

[raaif]

I cannot connect to SQL server heres the code

Dim conn As New SqlClient.SqlConnection
        conn.ConnectionString = "Data Source=|DataDirectory|\Books.sdf"
        conn.Open() '\\ Error Comes here
        Dim sqlcomm As SqlCommand = New SqlCommand(("SELECT MAX(ID) FROM Books"), conn)
        maxid = sqlcomm.ExecuteNonQuery()

Error Prompts me as

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I know and have seen many posts on this probem but the solution there does not seem to help here what i have done so far

1. Firewall Alows TCP Ports 1433 and 1434
2. SQL Server Browser Enabled
3. Remote Connections are alowed
4. Connection string is correct

SO what seems to be the problem??? please help as it is urgent

[Reverend Jim]

The following code connects to the mydb database on my local copy of SQLEXPRESS.

Dim conn As New SqlClient.SqlConnection
'conn.ConnectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
conn.ConnectionString = "Data Source=" & SERVER & " ;Initial Catalog=" & DATABASE & ";Integrated Security=SSPI;"

conn.Open()
MsgBox(conn.State)

It uses integrated security (based on your windows logon). If you have to provide a SQL username and password then use the commented out form. A Complete reference for various connection strings can be found here

[raaif]

Dim conn As New SqlClient.SqlConnection
'conn.ConnectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
conn.ConnectionString = "Data Source=" & SERVER & " ;Initial Catalog=" & DATABASE & ";Integrated Security=SSPI;"
conn.Open()
MsgBox(conn.State)

@Reverend Jim
Whith what should I replace SERVER and DATABASE with?
is it like

Dim conn As New SqlClient.SqlConnection
conn.ConnectionString = "Data Source=" & "D:\myapp\" & " ;Initial Catalog=" & "Books" & ";Integrated Security=SSPI;"
 conn.Open()
MsgBox(conn.State)

[G_Waddell]

Hi,
What edition of SQL are you using?
For most versions of SQL Server, Datasource should generally be the Server Name, SQL Express is the exception where it will be Server Name\SQLExpress or if you have a named instance Server Name\Instance Name

Try this link: Click Here

For example my database is stored on the Server AppServer and is the default instance I would use Datasource =AppServer

[Reverend Jim]

If you are using SQLEXPRESS on your PC you can use ".\SQLEXPRESS" for SERVERNAME. For DATABASE you'll plug in the name of the database as used by SQL Server (not the file name).

[poojavb]

or in place of server can also add localhost....

to check ur server name u can use a query

select @@SERVERNAME

'connection string

Connection.ConnectionString = "Data Source = LocalHost\;Initial Catalog = DatabaseName;Integrated Security  = True"