SQL connection fails

[Bill Purkins]

Hi, using VB 2008 Express, I have a button on a form which is to use a datareader to loop through all the records in a file. When I try to run it I get a message indicating it could not connect. I assume something is wrong with my connection string but I don't know what. I know the path name is correct, and I know my database does not require a password. I have tried it with and without USER ID and PASSWORD. I don;t know where to look next. Any help is appreciated.
Thanks,
Bill P.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim sConnection As String = "server=(local);uid=test;pwd=PassWord;database=C:\Documents and Settings\Compaq_Owner\My Documents\Visual Studio 2008\Projects\Medications\Medications\medications.sdf"

        Dim objCommand As New SqlCommand
        objCommand.CommandText = "Select doctor From Doctors"
        objCommand.Connection = New SqlConnection(sConnection)
        objCommand.Connection.Open()

        Dim objDataReader As SqlDataReader = objCommand.ExecuteReader()

        If objDataReader.HasRows Then
            Do While objDataReader.Read()
                Console.WriteLine(" Your name is: " & objDataReader(0))
            Loop
        Else
            Console.WriteLine("No rows returned.")
        End If

        objDataReader.Close()
        objCommand.Dispose()



    End Sub
End Class

[tuse]

Try this - http://www.connectionstrings.com/

[sandeep_chalke]

Could you please post the exact error text?

[Bill Purkins]

I find my connection string saved in my application configuration file, but when I refer to it by name I get this error message:

Error 1 Reference to a non-shared member requires an object reference.

Here is the app.config entry:

<connectionStrings>
<add name="WindowsApplication1.My.MySettings.medicationsConnectionString"
connectionString="Data Source=|DataDirectory|\medications.sdf"
providerName="Microsoft.SqlServerCe.Client.3.5" />
</connectionStrings>

Thanks,
Bill P.

[ChaseVoid]

The problem is your connection string. To connect to a SQL server Compact edition database you use the following:

Data Source=MyData.sdf;Persist Security Info=False;

How to specify the location of the SDF file
Often times the .SDF database is not running in the current directory so it becomes necessary to programatically set the path to the SDF file. This is an example (.net C#) on how to do this when the SDF file is located in the same directory as the executing application.

Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\MyData.sdf;Persist Security Info=False;

Here you get more info about how to use the App.config information in your application

[Bill Purkins]

When I use

Data Source=MyData.sdf;Persist Security Info=False;Data Source=MyData.sdf;Persist Security Info=False;

I get error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

[ChaseVoid]

Maybe you don't have your Sql Server running? Try starting/restarting it using the surface area configuration manager or services.

[Bill Purkins]

SQL Server Browser was stopped. I started it. Now I get:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)