Need help with connect string for SQL DB created within Visual Studio 2008 and VB.Net

I'm having issues with the following VB code which is within a VB project in VS2008.

I've created a local SQL database within this project named contacts (and so it shows up at contacts.sdf) with a table named contacts.  This is a test program/project.  The table has firstname, lastname, emailaddress as fields.  It's for a simple test.

When I run the program below VS complains about the connection string (after "thinking" for quite a long period of time).  The exact error message is "replace the value of the connString variable with a connection string that is valid for your system."

Can anyone say what a valid connection string would be for this situation?

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms


Public Class Form1

    Inherits System.Windows.Forms.Form

    Private dataAdapter As New SqlDataAdapter()
    Private bindingSource1 As New BindingSource()


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        ' Bind the DataGridView to the BindingSource
        ' and load the data from the database.
        Me.DataGridView1.DataSource = Me.bindingSource1
        GetData("select * from Contacts")

    End Sub



    Private Sub GetData(ByVal selectCommand As String)

        Try
            ' Specify a connection string. Replace the given value with a
            ' valid connection string for a Northwind SQL Server sample
            ' database accessible to your system.
            Dim connString As String = "Persist Security Info = False; Data Source = 'Contacts.sdf';"

            ' Create a new data adapter based on the specified query.
            Me.dataAdapter = New SqlDataAdapter(selectCommand, connString)

            Dim commandBuilder As New SqlCommandBuilder(Me.dataAdapter)

            ' Populate a new data table and bind it to the BindingSource.
            Dim table As New DataTable()
            '     table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapter.Fill(table)
            Me.bindingSource1.DataSource = table

            ' Resize the DataGridView columns to fit the newly loaded content.
            Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)

        Catch ex As SqlException
            MessageBox.Show("To run this example, replace the value of the " + _
                "connString variable with a connection string that is " + _
                "valid for your system.")
        End Try

    End Sub

End Class
LVL 1
Gene KlamerusTechnical ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SriVaddadiCommented:
As i see that is your message and the basic exception message. Please look at the ex.Message and see what is the original message.

Also try giving the full path of the contacts.sdf in the connection string
0
cyberkiwiCommented:
Like the other expert has said, try using full path for the sdf file.
Also, you don't need the quotes.
Finally, do not have spaces before and after the "=" signs

"Persist Security Info=False;Data Source=Contacts.sdf;"
0
halfbloodprinceCommented:
Try giving the connection string as follows:
Dim strConn As String = "server=YOURSERVERNAME;database=YOURDBNAME;uid=YOURSQLSERVERUSERNAME;pwd=YOURSQLSERVERPASSWORD"
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Gene KlamerusTechnical ArchitectAuthor Commented:
Thanks for the suggestion on printing the ex.message.

The message I'm receiving is:

"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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

I created this database from within Visual Studio 2008.  Would I need to list server=localhost or something?
0
Gene KlamerusTechnical ArchitectAuthor Commented:
I went to the properties on this database, which I created within VS2008 and it has a property called "Connection String" with the value "Data Source=C:\Users\Gene\Desktop\Contacts\Contacts\Contacts.sdf"

so I modified my code to have the statement:

            Dim connString As String = "Data Source=C:\Users\Gene\Desktop\Contacts\Contacts\Contacts.sdf;"

which was copy and past from the property.

I get the same error message with tis.
0
Gene KlamerusTechnical ArchitectAuthor Commented:
I also tried this without the leading C: (but with the \Users....).  Same issue
0
SriVaddadiCommented:
Did you check the exception message? ex.message? That would be helpful to locate the issue
0
srikanthreddyn143Commented:
Out of that all your database is compact edition. You need to use SqlCe connection and command objects

Dim sqlConn As New SqlCeConnection("Data Source = "fullpath\test.sdf";PASSWORD=test")

http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring%28VS.80%29.aspx
0
cyberkiwiCommented:
re #32272599
Never put spaces between paramname and arg in connection strings
0
Gene KlamerusTechnical ArchitectAuthor Commented:
the ex.message is posted a few posts ago.  I always get the same message at this point.

I think sri may be onto something here.  This is a SQL database created within VS 2008 and VS is telling me that the provider is .NET Framework Data Provider for Microsoft SQL Server Compact 3.5

I'll give this a try and report back.
0
SriVaddadiCommented:
Sorry I missed that post. Try this

Dim connString As String = "Data Source=(local);Initial Catalog=C:\Users\Gene\Desktop\Contacts\Contacts\Contacts.sdf;Integrated Security=SSPI;"
0
SriVaddadiCommented:
Also check this site

http://www.connectionstrings.com
0
Gene KlamerusTechnical ArchitectAuthor Commented:
I think poster Sri...143 is close but he's providing a connection string and what I need to create is a data adapter as per the other code I've seen.  What would a data acapter for a SQL Server CE look like?
0
Gene KlamerusTechnical ArchitectAuthor Commented:
Okay,

So I'm going to ask something that is perhaps out of line, but...

Would anyone be able to put together the limited amount of code that would replace the code above, but actually work.

I would gladly pay a few bucks (paypal?) for something that works in the above scenario.  The scenario is VS 2008 vb.net talking to a database created in that same VS project to get data from a query and puts it on a datagridview.

I'm just apparently very thick, but I'm unable to use the right object types to do this.

I'm not aware if offering cash for solutions is outside the rules of experts-exchange or not (and I've been a member of EE for something like 5 yrs).  I've just never found myself in this sort of corner.
0
Gene KlamerusTechnical ArchitectAuthor Commented:
Okay,

So I'm going to ask something that is perhaps out of line, but...

Would anyone be able to put together the limited amount of code that would replace the code above, but actually work.

I would gladly pay a few bucks (paypal?) for something that works in the above scenario.  The scenario is VS 2008 vb.net talking to a database created in that same VS project to get data from a query and puts it on a datagridview.

I'm just apparently very thick, but I'm unable to use the right object types to do this.

I'm not aware if offering cash for solutions is outside the rules of experts-exchange or not (and I've been a member of EE for something like 5 yrs).  I've just never found myself in this sort of corner.
0
Gene KlamerusTechnical ArchitectAuthor Commented:
sorry, hit the button twice.
0
Gene KlamerusTechnical ArchitectAuthor Commented:
I could send donuts instead...
0
srikanthreddyn143Commented:
Dim pSQL As String = ur sql
Dim dsResult As New Dataset

Dim da As SqlCeDataAdapter
da = New SqlCeDataAdapter(pSQL, sqlConn)
        da.Fill(dsResult)

get the sqlconn from my above comment.This is a pseudo code.
0
Gene KlamerusTechnical ArchitectAuthor Commented:
Are there special imports we need to include with tihs as well?
0
Gene KlamerusTechnical ArchitectAuthor Commented:
wtb typing skills...
0
cyberkiwiCommented:
C#
            String connString = @"Data Source=Database1.sdf;";
            // Create a new data adapter based on the specified query.
            SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter("select name from person", connString);
            SqlCeCommandBuilder commandBuilder = new SqlCeCommandBuilder(dataAdapter);

            // Populate a new data table and bind it to the BindingSource.
            DataTable table = new DataTable();
            dataAdapter.Fill(table);

VB
            Dim connString as String
            connString = "Data Source=Database1.sdf;"
            ' Create a new data adapter based on the specified query.
            Dim dataAdapter as SqlCeDataAdapter
            dataAdapter = New SqlCeDataAdapter("select name from person", connString)
            Dim commandBuilder as SqlCeCommandBuilder
            commandBuilder = New SqlCeCommandBuilder(dataAdapter)

            ' Populate a new data table and bind it to the BindingSource.
            Dim table as DataTable
            table = new DataTable()
            dataAdapter.Fill(table)

I left out the full path name, but you can drop it in the same folder as the \Debug executable location.
Note the sqlce* bits
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gene KlamerusTechnical ArchitectAuthor Commented:
i saw all kinds of various additional parameters for connect strings.

Since this DB is in my VS project and I'm running the application is this all I need (the name of the database)?
0
cyberkiwiCommented:
In a word - yes
0
Gene KlamerusTechnical ArchitectAuthor Commented:
Thanks so much to the experts.  With this help my son was able to get his code working.  Everyone was very responsive (which is why I come here) and super smart.

Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.