Ad:

VB.NET Discussion Thread
Unsolved
Views: 6113

Jul 20th, 2008

SQL connection fails

Expand Post »

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.

VB.NET Syntax (Toggle Plain Text)
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
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

Similar Threads

Apache in Linux Servers and Apache
SQL AddWithValue problems in VB.NET
help with sql d/base & java gui in Java

Bill Purkins

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

42 posts
since Jul 2008

Permalink

Jul 21st, 2008

Re: SQL connection fails

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

tuse

Reputation Points: 32

Solved Threads: 14

Junior Poster

Offline

173 posts
since Jul 2007

Permalink

Jul 21st, 2008

Re: SQL connection fails

Could you please post the exact error text?

Last edited by sandeep_chalke; Jul 21st, 2008 at 1:09 pm.

sandeep_chalke

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Aug 2007

Permalink

Jul 21st, 2008

Re: SQL connection fails

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:

VB.NET Syntax (Toggle Plain Text)
<connectionStrings>
<add name="WindowsApplication1.My.MySettings.medicationsConnectionString"
connectionString="Data Source=|DataDirectory|\medications.sdf"
providerName="Microsoft.SqlServerCe.Client.3.5" />
</connectionStrings>
<connectionStrings>
<add name="WindowsApplication1.My.MySettings.medicationsConnectionString"
connectionString="Data Source=|DataDirectory|\medications.sdf"
providerName="Microsoft.SqlServerCe.Client.3.5" />
</connectionStrings>

Thanks,
Bill P.

Bill Purkins

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

42 posts
since Jul 2008

Permalink

Jul 21st, 2008

Re: SQL connection fails

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

VB.NET Syntax (Toggle Plain Text)

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

Quote ...

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.

VB.NET Syntax (Toggle Plain Text)
Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\MyData.sdf;Persist Security Info=False;
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

Last edited by ChaseVoid; Jul 21st, 2008 at 1:51 pm. Reason: Added website : http://www.codeguru.com/columns/DotNet/article.php/c7987/

ChaseVoid

Reputation Points: 40

Solved Threads: 12

Junior Poster

Offline

116 posts
since Aug 2007

Permalink

Jul 21st, 2008

Re: SQL connection fails

When I use

VB.NET Syntax (Toggle Plain Text)
Data Source=MyData.sdf;Persist Security Info=False;Data Source=MyData.sdf;Persist Security Info=False;
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)

Bill Purkins

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

42 posts
since Jul 2008

Permalink

Jul 21st, 2008

Re: SQL connection fails

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

ChaseVoid

Reputation Points: 40

Solved Threads: 12

Junior Poster

Offline

116 posts
since Aug 2007

Permalink

Jul 21st, 2008

Re: SQL connection fails

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)

Bill Purkins

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

42 posts
since Jul 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Errorhandler vs. Msgbox for errors - preference?

Next Thread in VB.NET Forum Timeline: Graph controls

DaniWeb Message

Cancel Changes

User Name
Password