Error in Connecting to SQl server2000 Sample Northwind database

[Warren Ang]

I'm just started out learning programming SQL server 2002 with visual basic.net or visa versa. I have a book entitled "Programming Microsoft SQL Server 2000 with Microsoft Visual Basic.net". There is a CD containing sample vb.net application codes to facilitate the learning of programming...

Now when i run and compiled the sample source code, there is this error:

Unhandled Exception: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
at System.Data.SqlClient.SqlConnection.Open()
at MyADODOTNETSamples.Module1.IntegratedCnnToNorthwind() in D:\SQLVBNET\Chapter_10\MyADODOTNETSamples\Module1.vb:line 29
at MyADODOTNETSamples.Module1.main() in D:\SQLVBNET\Chapter_10\MyADODOTNETSamples\Module1.vb:line 6The program '[2788] MyADODOTNETSamples.exe' has exited with code 0 (0x0).


I have sqlserver 2000 installed. And i have run sqlserver 2000 enterprise manager. I can see that Northwind database is in my system.

The vb.net codes are as follows:


Imports System.Data.SqlClient
Module Module1


Sub main()
IntegratedCnnToNorthwind()
'SQLServerCnnToNorthwind()
'CatchSQLClientException()
'EnumerateCategories()
'EnumerateCustomerIDNames(25)
'RunCustOrderHistWithString("TORTU", 10)
'RunCustOrderHistWithParameter("TORTU", 10)
'CreateAndInvokeUDF(10249, 3)
'ShowForm2Bindings()
End Sub


Sub IntegratedCnnToNorthwind()

'Specify connection string for connection via user's
'Windows login; make sure user's Window login has access
'to the Northwind database or the Northwind database has
'its guest user account.
Dim cnn1 As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Initial Catalog=northwind;Integrated Security=SSPI")

'Attempt to open Northwind database with user's Windows login.
cnn1.Open()

'Echo connection string to either Debug window
'or a message box.
Debug.WriteLine(cnn1.ConnectionString)
'MsgBox(cnn1.ConnectionString)

'Close connection object to dispose of it.
cnn1.Close()

End Sub


Sub SQLServerCnnToNorthwind()

'Specify connection string for connection via vbdotnet1
'SQL Server login; make sure vbdotnet1 login has access
'to the Northwind database via its own account or guest account.
Dim str1 As String = "Data Source=(local);" & _
"Initial Catalog=northwind;" & _
"user id = vbdotnet1; password=passvbdotnet1"
Dim cnn1 As SqlConnection = _
New SqlConnection(str1)

'Attempt to open Northwind database with vbdotnet1 login.
cnn1.Open()

'Echo connection string
Debug.WriteLine(cnn1.ConnectionString)

'Close connection object to dispose of it.
cnn1.Close()

End Sub

Anyone has any solutions or suggestions or teachings? Thanks alot.

[Lil Ms Squirrel]

Try changing the connection string so that the Data Source = localhost rather than (local).

If not, try this knowledge base article http://support.microsoft.com/?kbid=328306

[Tool]

I have not had the chance to work with SQLServer very much. I do however know that the time I did (once or twice) I had to include a "provider=" into my connection string. The connection string could be your biggest problem since I believe it errors out when you are trying to establish the connection. Here is a nice little website to help you out with a few different options you can use with your Connection Strings, has been a life saver for me.

http://www.connectionstrings.com/

At the very top is SQL Server. You can try those connections strings to see what works best for you and hopefully that will work.

[Mike Hildner]

I have that same book, seems pretty good, for what that's worth.

Anyway, looks like your code is failing when you try to use Windows authentication. Could be for two reasons (at least that I know of). One, when you installed Sql, you didn't allow for mixed mode (Sql and Windows authentication), or if you did, your Windows account does not have proper DB permissions.

If you can access the DB just fine through EM, I'm guessing you don't allow Windows authentication and you told EM to save your password so you don't have to supply when you connect. You could try a connection string specifying and user name and password, or have Sql Server use Windows authentication. For the latter, I'm not sure if that's something you can just change, or if you can only specify during installation. I always use mixed mode.

[Lil Ms Squirrel]

I do however know that the time I did (once or twice) I had to include a "provider=" into my connection string.

You only need to specify a provider when working with the ADO.NET OLEDB data objects. The SQLClient objects have been designed specifically for, and only work with, SQL Server 7.0 and above.

[Tool]

I guess that was why I needed to add the Provider then. Thanks for the calrification on that.

[Warren Ang]

Anyway, looks like your code is failing when you try to use Windows authentication. Could be for two reasons (at least that I know of). One, when you installed Sql, you didn't allow for mixed mode (Sql and Windows authentication), or if you did, your Windows account does not have proper DB permissions.

If you can access the DB just fine through EM, I'm guessing you don't allow Windows authentication and you told EM to save your password so you don't have to supply when you connect. You could try a connection string specifying and user name and password, or have Sql Server use Windows authentication. For the latter, I'm not sure if that's something you can just change, or if you can only specify during installation. I always use mixed mode.

This is how i install my sql server 2000 according to my memory. I let sql to install in my com as a local sql server, then i used window authenication since i have forgotten my window account password. I installed both client tools and connectivity. So I'm wondering did i install wrongly?

What I have planned to do is to self-teach myself in my standalone house com with sql database and vb applications running in it. Any ideas on how 2 configure my com or watever i have done wrong. I m lost. T_T

[kleinma]

forgotten your windows account password

I think you have bigger problems than SQL Server on your hands... some day for some reason you are going to need that password (i assume it auto logs you in now)

can you access the SQL Server using Enterprise Manager? (one of the client tools)

[Mike Hildner]

Yeah, you may want to go back to the beginning, it'll save you a lot of grief down the line to spend a little time on it now. If you can access the Northwind DB through EM and not with VB.NET using Windows authentication - that just doesn't make sense unless EM is remembering the password.

I'd reset my password, or have your admin do it, maybe reinstall Sql Server and remember the sa password. I'd recommend installing Sql for mixed mode authentication - can't remember if that's the default or not.

[Warren Ang]

forgotten your windows account password

I think you have bigger problems than SQL Server on your hands... some day for some reason you are going to need that password (i assume it auto logs you in now)

can you access the SQL Server using Enterprise Manager? (one of the client tools)

Yes. I can access Northwind database in EM. It does auto log me in. As i check the radiobox (set blank passwords) so i do not have to type any passwords. So if i uninstall n remove sql server 2000, so what settings do i set accordingly in the installation wizard?